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 ...
Get MySQL Cookbook, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.