Which one represents null? undef or empty string

Question

I want to insert null in a column in a table.

Which one represents null? undef or empty string ''.

Which one should be used and Why? I know about defined and that I can check it.

But I am looking more from perspective of database.

Which one represents null more properly?

Update: I am using DBI module.

Indisposed

Answer 1

DBI uses undef to represent a SQL NULL. The empty string represents an empty string.

However, some databases don't make a distinction between NULL and the empty string. Oracle is a particular offender here. DBI can't do anything about that.

Fucus

Answer 2

Assuming Perl DBI, undef represents a SQL NULL.

An empty string in Perl represents exactly the same (i.e. an empty string) in SQL.

Rozier

If both of them represents NULL,then which one to use? – Indisposed

@Drt, they don't both represent NULL. undef does. – Tola

@Tola and @Alnitak..When I do select * from tablename where columnname is null. I get same rows in each case. :( – Indisposed

@ Alnitak..In two cases 1. if update column with '' 2. If update with undef. Same rows for where columnname is null – Indisposed

@Drt, it's also possible that your database doesn't make a distinction between NULL and the empty string. DBI can't do anything about that. What database are you using? – Fucus

@Drt, there's your problem. Oracle treats the empty string as NULL. – Fucus

Answer 3

Assuming you are using the DBI module, and are using bound arguments (since if you were constructing the SQL by hand, you would use NULL in the string):

Undefined values, or undef, are used to indicate NULL values.

See the perldoc for DBI

If you aren't using DBI directly (e.g. you are using DBIx::Class or some other ORM) then you will probably find that it inherits the behaviour of DBI for this.

Dewar

So if I run select statement on that column, what will it return for undef. – Indisposed

Since it won't have undef in it, that question doesn't make sense. If it is NULL then you will get undef out. See the documentation I pointed you at in my answer: undef NULL values are represented by undefined values in Perl – Dewar

Be careful passing NULL to as a bound parameter. Some databases won't do what you expect and require "where x is null" and "where x = ?" and a NULL parameter is not always the same. I wrote up a summary of the discussion on dbi-users years ago at easysoft.com/developer/languages/perl/… – Tyratyrannical