MySQL Cookbook, 2nd Edition

Identifying NULL Values in Result Sets

Problem

A query result includes NULL values, but you’re not sure how to tell where they are.

Solution

Your API probably has some special value that represents NULL by convention. You just have to know what it is and how to test for it.

Discussion

Handling Special Characters and NULL Values in Statements described how to refer to NULL values when you send statements to the database server. In this section, we’ll deal instead with the question of how to recognize and process NULL values that are returned from the database server. In general, this is a matter of knowing what special value the API maps NULL values onto, or what method to call. These values are shown in the following table:

Language	NULL-detection value or method
Perl DBI	undef value
Ruby DBI	nil value
PHP PEAR DB	A NULL or unset value
Python DB-API	None value
Java JDBC	wasNull() method

The following sections show a very simple application of NULL value detection. The examples retrieve a result set and print all values in it, mapping NULL values onto the printable string “NULL”.

To make sure the profile table has a row that contains some NULL values, use mysql to issue the following INSERT statement, and then issue the SELECT statement to verify that the resulting row has the expected values:

mysql>INSERT INTO profile (name) VALUES('Juan');
mysql> SELECT * FROM profile WHERE name = 'Juan'; +----+------+-------+-------+-------+------+ | id | name | birth | color | foods | cats | +----+------+-------+-------+-------+------+ ...