Lessons · SQL · comparing with NULL

Why = NULL finds nothing

NULL means unknown, and unknown compared to anything is unknown, including to itself. A WHERE keeps only rows whose test is true, so an unknown result drops the row.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

This one rule explains most NULL surprises: the missing rows, the NOT IN that returns nothing, the total that is smaller than expected.

How to think about it

Read every comparison against a nullable column and ask what happens when it is NULL: the row disappears. If those rows should stay, add OR col IS NULL, or COALESCE the column first.

Worked example

SELECT COUNT(*) FROM orders WHERE status <> 'paid';

Orders whose status is not paid, and not NULL: a NULL status is neither equal nor unequal.

SELECT COUNT(*) FROM orders WHERE status <> 'paid' OR status IS NULL;

Everything that is not known to be paid, which is usually what was meant.

Your turn

Customers not in London, including those with no city.

SELECT name FROM customers WHERE city <> 'London'  city IS NULL;

The trap

col <> 'x' silently excludes NULLs, so 'everything except x' undercounts. Decide out loud whether unknown belongs in the answer.

Practise comparing with NULL on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.