Recipe 3.10. Writing Comparisons Involving NULL in Programs

Problem

You're writing a program that looks for rows containing a specific value, but it fails when the value is NULL.

Solution

Choose the proper comparison operator according to whether the comparison value is or is not NULL.

Discussion

The need to use different comparison operators for NULL values than for non-NULL values leads to a subtle danger when constructing statement strings within programs. If you have a value stored in a variable that might represent a NULL value, you must account for that if you use the value in comparisons. For example, in Perl, undef represents a NULL value, so to construct a statement that finds rows in the taxpayer table matching some arbitrary value in an $id variable, you cannot do this:

$sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id = ?"); $sth->execute ($id); 

The statement fails when $id is undef because the resulting statement becomes:

SELECT * FROM taxpayer WHERE id = NULL 

A comparison of id = NULL is never true, so that statement returns no rows. To take into account the possibility that $id may be undef, construct the statement using the appropriate comparison operator like this:

$operator = (defined ($id) ? "=" : "IS"); $sth = $dbh->prepare ("SELECT * FROM taxpayer WHERE id $operator ?"); $sth->execute ($id); 

This results in statements as follows for $id values of undef (NULL) or 43 (not NULL):

SELECT * FROM taxpayer WHERE id IS NULL SELECT * FROM taxpayer WHERE id = 43 

For inequality tests, set $operator like this instead:

$operator = (defined ($id) ? "!=" : "IS NOT"); 

Another way to avoid all this trouble, if it's not necessary to allow a column to contain NULL values, is to declare it NOT NULL when you create the table. For example, the taxpayer table could have been defined like this to disallow NULL values in either of its columns:

# taxpayer2.sql # taxpayer table, defined with NOT NULL columns DROP TABLE IF EXISTS taxpayer; CREATE TABLE taxpayer (   name  CHAR(20) NOT NULL,   id    CHAR(20) NOT NULL );