MySQL Cookbook

Writing Comparisons Involving NULL in Programs

Problem

You’re writing a program that issues a query, but it fails for NULL values.

Solution

Try writing the comparison selectively for NULL and non-NULL values.

Discussion

The need to use different comparison operators for NULL values than for non-NULL values leads to a subtle danger when constructing query 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 records 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 query becomes:

SELECT * FROM taxpayer WHERE id = NULL

That statement returns no records—a comparison of = NULL always fails. To take into account the possibility that $id may be undef, construct the query 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 queries 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"); ...