Working with NULL

Why NULL is not a value, and the comparisons that silently fail.

What you'll be able to do

Test for null correctly
Explain why NOT IN breaks on nulls
Know how aggregates treat nulls

NULL means unknown

NULL is not zero and not an empty string — it means no value recorded. Because it is unknown, comparing it produces unknown rather than true or false.

WHERE email = NULL     -- matches nothing, ever
WHERE email IS NULL    -- correct
WHERE email IS NOT NULL

This is the first null trap: = NULL is valid SQL that silently returns no rows.

NOT IN and nulls

SELECT * FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

If that subquery returns even one NULL, the whole query returns zero rows. The comparison can never be proven true, so nothing qualifies.

NOT EXISTS does not have this problem, which is why it is the safer default for anti-joins.

Aggregates skip nulls

COUNT(col), SUM, AVG, MIN and MAX all ignore nulls.

The consequence people miss: AVG divides by the count of non-null values, not the row count. Ten rows with three nulls averages over seven. If nulls should count as zero, say so: AVG(COALESCE(col, 0)).

COUNT(*) counts rows regardless, which is why it and COUNT(col) disagree on a nullable column.

Substituting a value

COALESCE(email, 'unknown')      -- first non-null argument
NULLIF(a, b)                    -- NULL when a = b, else a

NULLIF is most often used to avoid division by zero: total / NULLIF(count, 0) returns null instead of erroring.

Key points

Use IS NULL, never = NULL
NOT IN returns nothing when the list contains a NULL — prefer NOT EXISTS
AVG divides by non-null count, not row count

Check yourself