MySQL Cookbook, 2nd Edition

Working with NULL Values

Problem

You’re trying to compare column values to NULL, but it isn’t working.

Solution

You have to use the proper comparison operators: IS NULL, IS NOT NULL, or <=>.

Discussion

Conditions that involve NULL are special. You cannot use comparisons of the form value = NULL or value != NULL to check whether value is NULL. Such comparisons always produce a result of NULL because it’s impossible to tell whether they are true or false. Even NULL = NULL produces NULL because you can’t determine whether one unknown value is the same as another unknown value.

To look for columns that are or are not NULL, use the IS NULL or IS NOT NULL operator. Suppose that a table taxpayer contains taxpayer names and ID numbers, where a NULL value in the id column indicates that the value is unknown:

mysql>SELECT * FROM taxpayer;
+---------+--------+
| name    | id     |
+---------+--------+
| bernina | 198-48 |
| bertha  | NULL   |
| ben     | NULL   |
| bill    | 475-83 |
+---------+--------+

You can see that = and != do not identify NULL values as follows:

mysql>SELECT * FROM taxpayer WHERE id = NULL;
Empty set (0.00 sec)
mysql> SELECT * FROM taxpayer WHERE id != NULL;
Empty set (0.01 sec)

To find rows where the id column is or is not NULL, write the statements like this instead:

mysql>SELECT * FROM taxpayer WHERE id IS NULL;
+--------+------+
| name   | id   |
+--------+------+
| bertha | NULL |
| ben    | NULL |
+--------+------+
mysql> SELECT * FROM taxpayer WHERE id IS NOT NULL; +---------+--------+ | name ...