MySQL – NULL, IS NULL and ISNULL()

NULL is not a value. It is the absence of one — "we do not know", or "this does not apply". Every surprising thing about NULL follows from that one fact, and once it clicks the rest stops being a list of exceptions to memorise.

The pizza schema has a good example built into it. customer_order.user_id is nullable on purpose: it holds the account that placed the order, and it is NULL exactly when there was no account, because the order came from a guest checkout.

SELECT id, customer_name, user_id, guest_email
FROM   customer_order
WHERE  user_id IS NULL
ORDER  BY id
LIMIT  4;

+----+---------------+---------+-----------------------+
| id | customer_name | user_id | guest_email           |
+----+---------------+---------+-----------------------+
|  2 | Alex Rivera   |    NULL | guest1@example.com    |
|  4 | Sam Chen      |    NULL | guest2@example.com    |
|  5 | Jordan Blake  |    NULL | abandoned@example.com |
|  7 | Priya Nair    |    NULL | guest3@example.com    |
+----+---------------+---------+-----------------------+

Why = NULL never works

Comparing anything to NULL gives NULL — not true, not false. WHERE keeps only rows where the condition is true, so a comparison against NULL keeps nothing. WHERE user_id = NULL returns zero rows even though ten rows have a NULL user_id, and nothing warns you.

IS NULL and IS NOT NULL are the only operators that test for it:

WHERE user_id IS NULL        -- guest orders
WHERE user_id IS NOT NULL    -- orders from a registered account

The same logic explains a less obvious one: NOT IN against a list containing a NULL returns no rows at all, because "x is not equal to NULL" is unknown for every x. It is covered in subqueries, where it bites hardest.

ISNULL()

MySQL also has an ISNULL() function, which returns 1 or 0. It is the same test in expression form, so it can appear anywhere an expression can:

SELECT ISNULL(user_id) AS is_guest, COUNT(*) AS orders
FROM   customer_order
GROUP  BY ISNULL(user_id);

+----------+--------+
| is_guest | orders |
+----------+--------+
|        1 |     10 |
|        0 |      8 |
+----------+--------+

Prefer IS NULL in a WHERE clause. It is standard SQL, it reads better, and — as the WHERE lesson explains — wrapping a column in a function stops an index on it being used.

Aggregates skip NULL

This is the behaviour that catches people out most often in reports:

SELECT COUNT(*) AS all_rows, COUNT(user_id) AS with_user, COUNT(guest_email) AS with_guest_email
FROM   customer_order;

+----------+-----------+------------------+
| all_rows | with_user | with_guest_email |
+----------+-----------+------------------+
|       18 |         8 |               10 |
+----------+-----------+------------------+

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. That is why the three numbers differ, and why COUNT(*) is what you want unless you specifically mean "how many have one".

SUM, AVG, MIN and MAX ignore NULLs too, and for AVG that changes the answer: the denominator is the count of non-NULL values, not the row count. An AVG over a column that is half NULL is an average of the half that is present.

Two related quirks worth knowing: an aggregate over zero rows returns NULL, not 0 — so COALESCE(SUM(x), 0) is a common and sensible habit. And GROUP BY does the opposite of comparison: it puts all the NULLs together in a single group rather than treating them as all different.

Replacing NULL: COALESCE, IFNULL and NULLIF

COALESCE returns its first non-NULL argument, and takes as many as you like:

SELECT id,
       COALESCE(guest_email, 'registered account') AS contact
FROM   customer_order
ORDER  BY id
LIMIT  4;

+----+--------------------+
| id | contact            |
+----+--------------------+
|  1 | registered account |
|  2 | guest1@example.com |
|  3 | registered account |
|  4 | guest2@example.com |
+----+--------------------+

IFNULL(a, b) is the same thing limited to two arguments. It is MySQL-specific; COALESCE is standard SQL and handles both cases, so there is little reason to reach for IFNULL.

NULLIF(a, b) goes the other way — it returns NULL when the two are equal. Its one genuinely common use is avoiding a division by zero:

-- returns NULL instead of raising an error when the denominator is 0
SELECT total / NULLIF(delivery_fee, 0) FROM customer_order LIMIT 1;

Should the column be nullable at all?

A nullable column is a claim that "no value" is a meaningful state. Sometimes it plainly is — user_id above, or order_item.product_id, which goes NULL if the product is later deleted so the historical order survives.

Often it is not, and the NULL is standing in for something the schema should say properly: an empty string, a zero, a default, or a missing row in another table. Every nullable column is a branch every query has to handle. Add NOT NULL unless you can say what NULL means for that column.

What to remember

• NULL is unknown. Comparisons against it are unknown, and WHERE drops unknown.
• IS NULL / IS NOT NULL are the only tests. ISNULL() is the expression form.
• COUNT(*) counts rows; COUNT(col) counts non-NULL values.
• COALESCE for a fallback, NULLIF to create a NULL on purpose.
• GROUP BY groups NULLs together, unlike =.