Working with NULL Values

3.12.1 Problem

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

3.12.2 Solution

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

3.12.3 Discussion

Conditions involving NULL are special. You cannot use = NULL or != NULL to look for NULL values in columns. Such comparisons always fail because it's impossible to tell whether or not they are true. Even NULL = NULL fails. (Why? 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 IS NULL or IS NOT NULL. Suppose a table taxpayer contains taxpayer names and ID numbers, where a NULL ID 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 work with 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 records where the id column is or is not NULL, the queries should be written like this:

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

As of MySQL 3.23, you can also use <=> to compare values, which (unlike the = operator) is true even for two NULL values:

mysql> SELECT NULL = NULL, NULL <=> NULL;
+-------------+---------------+
| NULL = NULL | NULL <=> NULL |
+-------------+---------------+
| NULL | 1 |
+-------------+---------------+

3.12.4 See Also

NULL values also behave specially with respect to sorting and summary operations. See Recipe 6.6 and Recipe 7.9.