SQL practice › SQL reference › NULL
SQL NULL: why = NULL finds nothing, IS NULL, COALESCE and the NOT IN trap
NULL means there is no value: unknown, or not applicable. It is not 0 and it is not an empty string. Because the value is unknown, comparing anything with it (=, <>, <) gives NULL instead of true or false, and WHERE keeps only the rows where the condition is true. That is why WHERE bonus = NULL returns no rows, even when the column is full of NULLs.
To test for it, write bonus IS NULL or bonus IS NOT NULL. To replace it with something, COALESCE(bonus, 0).
The table
Six people. Bo and Eli have no bonus on record (NULL); Dee's bonus is a real 0. Cy and Eli have no phone (NULL), and Bo's phone is an empty string, which looks the same in many tools and is not the same thing. Ada has no manager.
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| No rows |
bonus = NULL is not true for any row, not even for those two. Change = NULL to IS NULL and run it again: Bo and Eli.What a comparison with NULL returns
A condition in SQL has three possible results: true, false and NULL (unknown). The next query puts the two tests side by side as columns, so you can see what WHERE was looking at.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| name | bonus | equals_null | is_null |
|---|---|---|---|
| Ada | 500 | NULL | 0 |
| Bo | NULL | NULL | 1 |
| Cy | 300 | NULL | 0 |
| Dee | 0 | NULL | 0 |
| Eli | NULL | NULL | 1 |
| Fay | 200 | NULL | 0 |
equals_null is NULL on every row, whatever the bonus. is_null is 1 for Bo and Eli and 0 for everyone else, and never NULL. SQLite shows true as 1 and false as 0.The same thing happens with every other comparison, and that one gives wrong rows without an error. Ask for everyone whose bonus is not 500:
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| name | bonus |
|---|---|
| Cy | 300 |
| Dee | 0 |
| Fay | 200 |
NULL <> 500 is NULL, not true, so they are left out. Five people don't have a 500 bonus; to get all five, add OR bonus IS NULL.COALESCE: a value instead of NULL
COALESCE(a, b, ...) returns the first of its arguments that isn't NULL. Use it to show a default, or to make a NULL count as 0 in arithmetic: bonus + 100 is NULL when the bonus is NULL, and COALESCE(bonus, 0) + 100 is 100.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| name | bonus | phone |
|---|---|---|
| Ada | 500 | 555-0101 |
| Bo | 0 | |
| Cy | 300 | none |
| Dee | 0 | 555-0104 |
| Eli | 0 | none |
| Fay | 200 | 555-0106 |
none as their phone, but Bo's phone is still blank: an empty string is a value, so COALESCE leaves it alone.NULL or empty string
An empty string, '', is text of length zero: a value. NULL is the absence of a value. In this table phone IS NULL finds Cy and Eli and misses Bo, and phone = '' finds Bo and misses the other two. To catch both, test both, phone IS NULL OR phone = '', or turn the empty string into NULL first with NULLIF(phone, '').
One database blurs the line: Oracle's documentation says it treats a character value with a length of zero as null.
NULL in COUNT, SUM and AVG
COUNT(*) counts rows. COUNT(bonus), SUM(bonus) and AVG(bonus) skip the rows where the bonus is NULL. The one that surprises people is AVG: it divides by the number of values it found, not by the number of rows.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| people | with_bonus | total | average | average_all |
|---|---|---|---|---|
| 6 | 4 | 1000 | 250 | 166.67 |
AVG(bonus) is 250: 1000 divided by the 4 known values. If a missing bonus means no bonus, the average over all 6 people is 166.67, and you have to say so with COALESCE(bonus, 0). Neither number is wrong. They answer different questions.NOT IN and NULL
x NOT IN (list) is true only when x is known to be different from every value in the list. If the list holds a NULL, "different from NULL" is unknown, so the test can never come out true. One NULL in a subquery empties the whole result, without an error.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
| No rows |
manager_id, including Ada's NULL, so no row passes. Add WHERE manager_id IS NOT NULL inside the parentheses and four people come back: Bo, Cy, Eli and Fay.Practice: 4 exercises
Write the query in the box and press Run. Your result is compared with the right one, on this data and again on changed data, so a query with the values typed in won't pass. Column names don't matter; the columns and rows do.
Return the name of everyone whose bonus is missing (NULL).
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
Show the answer
SELECT name
FROM staff
WHERE bonus IS NULL| name |
|---|
| Bo |
| Eli |
IS NULL is the test for a missing value. = NULL returns nothing, and = 0 finds Dee, whose bonus is known to be zero.
Return the name and bonus of everyone whose bonus is not 500. People with no bonus on record didn't get 500 either: they must be in the result.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
Show the answer
SELECT name, bonus
FROM staff
WHERE bonus <> 500 OR bonus IS NULL| name | bonus |
|---|---|
| Bo | NULL |
| Cy | 300 |
| Dee | 0 |
| Eli | NULL |
| Fay | 200 |
bonus <> 500 is NULL for Bo and Eli, so they need their own test: OR bonus IS NULL. In SQLite, bonus IS NOT 500 does both at once.
Return each person's name and phone. Where there is no phone, whether it is NULL or an empty string, show the text none instead.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
Show the answer
SELECT name, COALESCE(NULLIF(phone, ''), 'none')
FROM staff| name | COALESCE(NULLIF(phone, ''), 'none') |
|---|---|
| Ada | 555-0101 |
| Bo | none |
| Cy | none |
| Dee | 555-0104 |
| Eli | none |
| Fay | 555-0106 |
NULLIF(phone, '') turns the empty string into NULL, and COALESCE then replaces every NULL with none. A CASE WHEN phone IS NULL OR phone = '' THEN 'none' ELSE phone END does the same.
Return the name of everyone who doesn't manage anyone: no row has their id as manager_id.
Table: staff
| id | name | phone | bonus | manager_id |
|---|---|---|---|---|
| 1 | Ada | 555-0101 | 500 | NULL |
| 2 | Bo | NULL | 1 | |
| 3 | Cy | NULL | 300 | 1 |
| 4 | Dee | 555-0104 | 0 | 1 |
| 5 | Eli | NULL | NULL | 4 |
| 6 | Fay | 555-0106 | 200 | 4 |
Show the answer
SELECT name
FROM staff
WHERE id NOT IN (SELECT manager_id FROM staff WHERE manager_id IS NOT NULL)| name |
|---|
| Bo |
| Cy |
| Eli |
| Fay |
The managers are the ids that appear in manager_id: 1 and 4. Everyone else manages nobody. The subquery has to drop the NULL, or NOT IN returns no rows at all. NOT EXISTS with a subquery gives the same four names and has no such trap.
Questions people ask
Why does NULL = NULL return no rows?
Because the result of NULL = NULL is NULL, not true: two unknown values can't be said to be equal. PostgreSQL's documentation says it directly: don't write expression = NULL, use IS NULL. To compare two columns and treat two NULLs as equal, SQLite has a IS b, and PostgreSQL documents a IS NOT DISTINCT FROM b.
How do I check for NULL or an empty string?
Test both: WHERE phone IS NULL OR phone = ''. Or fold the empty string into NULL and test once: WHERE NULLIF(phone, '') IS NULL. Both return three people on this page's table.
What is the difference between COALESCE, IFNULL and NVL?
COALESCE is the standard one and takes any number of arguments: it returns the first that isn't NULL. In SQLite, IFNULL(a, b) is the same as COALESCE with two arguments. PostgreSQL's documentation describes NVL and IFNULL as similar functions used in some other database systems. If you write COALESCE, you rarely need the others.
Do COUNT, SUM and AVG ignore NULL?
COUNT(column), SUM and AVG skip NULLs; only COUNT(*) counts every row. One more case: when there is no non-NULL value at all, SUM returns NULL, not 0. Write COALESCE(SUM(bonus), 0) if you need a number.
Where do NULLs go in ORDER BY and GROUP BY?
In SQLite a NULL sorts as smaller than any other value: first with ORDER BY bonus, last with ORDER BY bonus DESC, and NULLS LAST or NULLS FIRST after the column changes that. That is SQLite's rule: check your own database's documentation before relying on it elsewhere. In GROUP BY, all the NULLs form one group.