NULL is not a value
In SQL, NULL does not mean zero, and it does not mean an empty string. It means "there is no value here: we do not know it."
That one idea explains every surprise on this page. If you do not know what something is, you cannot say what it equals.
Example
Two sealed envelopes
Two envelopes each contain a number you cannot see. Are the numbers equal?
You cannot answer "yes". You cannot answer "no" either. The only honest answer is "I do not know".
That is what SQL says for NULL = NULL.
Three answers, not two
Most programming languages have two truth values. SQL has three:
- TRUE
- FALSE
- UNKNOWN
Any comparison that involves a NULL gives UNKNOWN. And here is the rule that causes the trouble:
A WHERE clause keeps a row only when the answer is TRUE. FALSE rows are dropped. UNKNOWN rows are dropped too.
Looking for NULLs the wrong way
The table has four orders. Two used a coupon and two did not, so their coupon is NULL. Try to find the ones with no coupon:
Start. The database checks WHERE coupon = NULL against each row. A row is returned only if the answer is TRUE.
- Rows returned
- 0
Nothing comes back. The comparison gave UNKNOWN for every row, including the rows that really are NULL.
Looking for NULLs the right way
SQL has a separate test that does not compare values. It simply asks whether a value is present.
Start. The database checks WHERE coupon IS NULL against each row. A row is returned only if the answer is TRUE.
- Rows returned
- 0
IS NULL and IS NOT NULL always answer TRUE or FALSE. They are the only correct way to test for NULL.
The rows that vanish
The first trap is easy to learn. This one is worse, because the query looks right and returns a plausible result. Find every order that did not use the coupon SAVE10:
Start. The database checks WHERE coupon <> 'SAVE10' against each row. A row is returned only if the answer is TRUE.
- Rows returned
- 0
Three orders did not use SAVE10. One row came back. The two orders with no coupon at all were dropped, because "is an unknown value different from SAVE10?" is UNKNOWN.
To include them, say so:
SELECT * FROM orders
WHERE coupon <> 'SAVE10' OR coupon IS NULL;NOT IN with a NULL in the list
NOT IN (a, b) is shorthand for "different from a AND different from b". Put a NULL in the list and one of those comparisons is always UNKNOWN, so the whole condition can never be TRUE.
Start. The database checks WHERE coupon NOT IN ('SAVE10', NULL) against each row. A row is returned only if the answer is TRUE.
- Rows returned
- 0
Nobody writes a NULL in the list on purpose. It arrives through a subquery:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);If a single order has a NULL customer_id, this returns no rows at all. Use NOT EXISTS instead, which does not have the problem.
Start. The database checks WHERE coupon = NULL against each row. A row is returned only if the answer is TRUE.
- Rows returned
- 0
How the three values combine
| Expression | Result | Why |
|---|---|---|
NULL = NULL | UNKNOWN | Two unknowns may or may not be equal |
NULL <> 5 | UNKNOWN | An unknown may or may not be 5 |
TRUE AND UNKNOWN | UNKNOWN | It depends on the unknown part |
FALSE AND UNKNOWN | FALSE | One FALSE is enough to make AND false |
TRUE OR UNKNOWN | TRUE | One TRUE is enough to make OR true |
NOT UNKNOWN | UNKNOWN | The opposite of "do not know" is still "do not know" |
NULL + 1 | NULL | Arithmetic with an unknown is unknown |
Other places NULL behaves differently
COUNT(*)counts rows.COUNT(column)skips rows where that column is NULL.SUMandAVGignore NULLs, so an average is over the rows that have a value.GROUP BYandDISTINCTtreat all NULLs as one group, even though they are not "equal".
Key takeaways
- NULL means "no value, unknown". It is not zero and not an empty string.
- Comparing anything with NULL gives UNKNOWN, a third truth value.
- WHERE keeps only TRUE rows, so UNKNOWN rows silently disappear.
- Test for NULL with IS NULL and IS NOT NULL, never with = or <>.
- A NULL in a NOT IN list makes the query return nothing. Use NOT EXISTS.