P02 · SQL & Querying
SQL NULL — why NULL never equals NULL
How NULL really works in SQL — and why a join on a nullable key silently drops rows, because NULL is never equal to anything, not even another NULL.
Start with the basics
A NULL is SQL's way of saying 'no value here' — unknown, missing, not applicable. It is not zero and not an empty string; it is the absence of a value.
Because a NULL is unknown, comparing to it is also unknown — not true, not false. So a NULL is never equal to anything, not even another NULL.
That one idea sits under a surprising number of bugs: filters that drop rows, joins that lose keys, sums that turn blank. Learn to see the unknown and most of them explain themselves.
One that works
| id | code |
|---|---|
| 1 | A |
| 2 | NULL |
SELECT id
FROM t
WHERE code IS NULL;| id |
|---|
| 2 |
IS NULL is how you find missing values — a plain equals never matches a NULL.
In SQL a comparison to NULL is never true or false — it is unknown, so NULL never equals anything, not even another NULL.
When to use it
- Watch for NULL whenever a column is optional — a status, a foreign key, an end date.
- Test for it with IS NULL / IS NOT NULL, never with equals.
- Default it with COALESCE when a missing value should read as something concrete.
That one rule — a missing value is never equal to anything — is where a join quietly drops rows. Two missing keys don't match, so the rows with a blank key silently vanish. Here is the day that bites.
See it happen
The join that drops the blank key — NULL never matches
Two tables both have a row with a missing key. You join them on that key. Watch the two missing keys refuse to match.
| k |
|---|
| 1 |
| NULL |
| k |
|---|
| 1 |
| NULL |
equals — NULL never matches
SELECT a.k
FROM a JOIN b ON a.k = b.k
ORDER BY a.k;| k |
|---|
| 1 |
null-safe comparison — treats NULLs as equal
SELECT a.k
FROM a JOIN b
ON a.k IS NOT DISTINCT FROM b.k
ORDER BY a.k;| k |
|---|
| 1 |
| NULL |
The row with a missing code never matches its twin — a comparison to a missing value is unknown, so the join drops it; a null-safe comparison treats two blanks as equal.
Walk the full example in the unit →The rest of the family
More ways NULL quietly breaks things
A filter drops both false and unknown rows alike, which is the root of many missing-row bugs.
COALESCE returns the first value that isn't missing, so order matters; a companion function blanks out a chosen sentinel value.
Adding to or joining text with a missing value yields a missing result — one blank spreads through the calculation.
Sum, average, and counting a column all ignore missing values, while counting rows counts them regardless.
The is-distinct-from comparison treats two missing values as equal, giving you a null-safe equals for keys, change detection, and dedup.
Self-check
How to spot it in your own SQL
- A join loses the rows whose key can be blank.
- A filter excludes more rows than you expected.
- A sum or a concatenation turned blank because one input was missing.
- You compared a column to a value and the blank rows disappeared.
What people miss
Missing values are the quiet root of half of SQL's surprises — dropped join rows, filters that exclude too much, sums that turn blank. Nothing errors, because a comparison to nothing is simply unknown.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why does my join drop rows with a null key?
A missing value is never equal to anything, not even another missing value, so an equals-join drops the rows whose key is blank.
Use a null-safe comparison (is-distinct-from) if you want two blanks to match.
from the unit →Why does my WHERE exclude the blank rows too?
A filter keeps only rows where the condition is true; on a blank value the condition is unknown, and unknown is dropped just like false.
Add an explicit is-null branch if you want the blank rows kept.
from the unit →Why did my SUM turn into NULL?
Arithmetic with a missing value yields a missing value, and a sum over nothing is blank rather than zero.
Default the inputs or the result with COALESCE so a missing value reads as 0.
from the unit →What's the null-safe way to compare two columns?
Use the is-distinct-from comparison — it treats two missing values as equal and a missing value as different from a real one.
It's the null-safe equals you want for keys, change detection, and dedup.
from the unit →That’s the free taste