MySQL Cookbook, 2nd Edition

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 ...