Recipe 3.9. 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    | id     | +---------+--------+ | bernina | 198-48 | | bill    | 475-83 | +---------+--------+ 

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 | +-------------+---------------+ 

Sometimes it's useful to map NULL values onto some other distinctive value that has more meaning in the context of your application. If NULL id values in the taxpayer table mean "unknown," you can display that fact by using IF⁠(⁠ ⁠ ⁠) to map NULL onto the string Unknown:

mysql> SELECT name, IF(id IS NULL,'Unknown', id) AS 'id' FROM taxpayer; +---------+---------+ | name    | id      | +---------+---------+ | bernina | 198-48  | | bertha  | Unknown | | ben     | Unknown | | bill    | 475-83  | +---------+---------+ 

This technique actually works for any kind of value, but it's especially useful with NULL values because NULL tends to be given a variety of meanings: unknown, missing, not yet determined, out of range, and so forth. You can choose the label that makes most sense in a given context.

The preceding query can be written more concisely using IFNULL⁠(⁠ ⁠ ⁠), which tests its first argument and returns it if it's not NULL, or returns its second argument otherwise:

mysql> SELECT name, IFNULL(id,'Unknown') AS 'id' FROM taxpayer; +---------+---------+ | name    | id      | +---------+---------+ | bernina | 198-48  | | bertha  | Unknown | | ben     | Unknown | | bill    | 475-83  | +---------+---------+ 

In other words, these two tests are equivalent:

IF(expr1 IS NOT NULL,expr1,expr2) IFNULL(expr1,expr2) 

From a readability standpoint, IF⁠(⁠ ⁠ ⁠) often is easier to understand than IFNULL⁠(⁠ ⁠ ⁠). From a computational perspective, IFNULL⁠(⁠ ⁠ ⁠) is more efficient because expr1 need not be evaluated twice, as happens with IF⁠(⁠ ⁠ ⁠).

See Also

NULL values also behave specially with respect to sorting and summary operations. See Recipes Section 7.14 and Section 8.8.