11

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.

3 Answers3

15

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.

cjm
  • 61,471
  • 9
  • 126
  • 175
9

Assuming Perl DBI, undef represents a SQL NULL.

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

Alnitak
  • 334,560
  • 70
  • 407
  • 495
  • If both of them represents `NULL`,then which one to use? –  Oct 03 '12 at 13:29
  • 2
    @Drt, they *don't* both represent `NULL`. `undef` does. – Axeman Oct 03 '12 at 13:36
  • @Axeman and @Alnitak..When I do `select * from tablename where columnname is null`. I get same rows in each case. :( –  Oct 03 '12 at 13:39
  • @ Alnitak..In two cases 1. if update column with `''` 2. If update with `undef`. Same rows for `where columnname is null` –  Oct 03 '12 at 13:45
  • 1
    @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 03 '12 at 14:02
  • 7
    @Drt, there's your problem. [Oracle treats the empty string as `NULL`.](https://duckduckgo.com/?q=oracle%20null%20empty%20string) – cjm Oct 03 '12 at 14:05
4

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.

Quentin
  • 914,110
  • 126
  • 1,211
  • 1,335
  • So if I run `select` statement on that column, what will it return for `undef`. –  Oct 03 '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](http://search.cpan.org/dist/DBI/DBI.pm#Notation_and_Conventions) – Quentin Oct 03 '12 at 12:55
  • 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 http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_2.html#nulls – bohica Oct 03 '12 at 14:22