3.3.4.6 Working with NULL Values

The NULL value can be surprising until you get used to it. Conceptually, NULL means missing value or unknown value and it is treated somewhat differently than other values. To test for NULL, you cannot use the arithmetic comparison operators such as =, <, or <>. To demonstrate this for yourself, try the following query:

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

Clearly you get no meaningful results from these comparisons. Use the IS NULL and IS NOT NULL operators instead:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

Note that in MySQL, 0 or NULL means false and anything else means true. The default truth value from a boolean operation is 1.

This special treatment of NULL is why, in the previous section, it was necessary to determine which animals are no longer alive using death IS NOT NULL instead of death <> NULL.

Two NULL values are regarded as equal in a GROUP BY.

When doing an ORDER BY, NULL values are presented first if you do ORDER BY ... ASC and last if you do ORDER BY ... DESC.

Note that between MySQL 4.0.2 - 4.0.10, NULL values incorrectly were always sorted first regardless of the sort direction.

User Comments

The function 'COALESCE' can simplify working with null
values.
for example, to treat null as zero, you can use:
select COALESCE(colname,0) from table where
COALESCE(colname,0) > 1;

in a date field, i used:
ORDER BY
(coalesce(TO_DAYS(date),TO_DAYS(CURDATE()))-TO_DAYS(CURDATE()))
to treat NULL as the current date.

Use IFNULL() in your SELECT statement is make the NULL any value you wish.

IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used:
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

Null handling can be very counter intuitive, and could cause problems if you have an incorrect function in a delete statement that returns null

DELETE FROM my_table where field > NULL (or function returning NULL)

deletes all entries.

SELECT * FROM my_table WHERE field > NULL (or function returning NULL)

returns 0 entries!

Add your own comment.