MySQL Cookbook

Handling 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 value that represents NULL by convention. You just have to know what it is and how to test for it.

Discussion

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

Language	NULL-detection value or function
Perl	undef
PHP	an unset value
Python	None
Java	wasNull( )

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, then issue the SELECT query 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 |
+----+------+-------+-------+-------+------+
| 11 | Juan | NULL  | NULL  | NULL  | NULL |
+----+------+-------+-------+-------+------+

The id