Programming the Perl DBI

Null values

Undefined values, or undef, can be used to indicate null values. However, care must be taken in the particular case of trying to use null values to qualify a SELECT statement.

For example:

SELECT description FROM products WHERE product_code = ?

Binding an undef (NULL) to the placeholder will not select rows that have a NULL product_code. (Refer to the SQL manual for your database engine or any SQL book for the reasons for this.) To explicitly select NULLs, you have to say "WHERE product_code IS NULL" and to make that general, you have to say:

... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))

and bind the same value to both placeholders.