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");
Using the mysql Client Program
Writing MySQL-Based Programs
Record Selection Techniques
Working with Strings
Working with Dates and Times
Sorting Query Results
Generating Summaries
Modifying Tables with ALTER TABLE
Obtaining and Using Metadata
Importing and Exporting Data
Generating and Using Sequences
Using Multiple Tables
Statistical Techniques
Handling Duplicates
Performing Transactions
Introduction to MySQL on the Web
Incorporating Query Resultsinto Web Pages
Processing Web Input with MySQL
Using MySQL-Based Web Session Management
Appendix A. Obtaining MySQL Software
Appendix B. JSP and Tomcat Primer
Appendix C. References