Writing Comparisons Involving NULL in Programs
3.14.1 Problem
You're writing a program that issues a query, but it fails for NULL values.
3.14.2 Solution
Try writing the comparison selectively for NULL and non-NULL values.
3.14.3 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 recordsa 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");
Категории