Selecting Data from Tables - MySQL - page 117

Solution
Choose the proper comparison operator according to whether the comparison value is
or is not NULL .
Discussion
Recipe 3.5 discusses the need to use different comparison operators for NULL values than
for non- NULL values in SQL statements. This issue leads to a subtle danger when con‐
structing statement strings within programs. If a value stored in a variable might rep‐
resent a NULL value, you must account for that when 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 expt table matching some arbitrary value in a $score variable, you
cannot do this:
$sth = $dbh -> prepare ( "SELECT * FROM expt WHERE score = ?" );
$sth -> execute ( $score );
The statement fails when $score is undef because the resulting statement becomes:
SELECT * FROM expt WHERE score = NULL
A comparison of score = NULL is never true, so that statement returns no rows. To take
into account the possibility that $score could be undef , construct the statement using
the appropriate comparison operator like this:
$operator = defined ( $score ) ? "=" : "IS" ;
$sth = $dbh -> prepare ( "SELECT * FROM expt WHERE score $operator ?" );
$sth -> execute ( $score );
This results in statements as follows for $score values of undef ( NULL ) or 43 (not NULL ):
SELECT * FROM expt WHERE score IS NULL
SELECT * FROM expt WHERE score = 43
For inequality tests, set $operator like this instead:
$operator = defined ( $score ) ? "<>" : "IS NOT" ;
3.7. Using Views to Simplify Table Access
Problem
You want to refer to values calculated from expressions without writing the expressions
each time you retrieve them.
Solution
Use a view defined such that its columns perform the desired calculations.