Take the 2-minute tour ×

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.

share|improve this question
add comment

3 Answers

up vote 8 down vote accepted

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.

share|improve this answer
add comment

Assuming Perl DBI, undef represents a SQL NULL.

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

share|improve this answer
If both of them represents NULL,then which one to use? –  DA14 Oct 3 '12 at 13:29
@Drt, they don't both represent NULL. undef does. –  Axeman Oct 3 '12 at 13:36
@Axeman and @Alnitak..When I do select * from tablename where columnname is null. I get same rows in each case. :( –  DA14 Oct 3 '12 at 13:39
@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? –  cjm Oct 3 '12 at 14:02
@Drt, there's your problem. Oracle treats the empty string as NULL. –  cjm Oct 3 '12 at 14:05
show 8 more comments

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.

share|improve this answer
So if I run select statement on that column, what will it return for undef. –  DA14 Oct 3 '12 at 12:53
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 –  Quentin Oct 3 '12 at 12:55
+1 Thanks.......... –  DA14 Oct 3 '12 at 14:17
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/… –  bohica Oct 3 '12 at 14:22
add comment

Your Answer


