NULL and three-valued logic

NULL means "unknown", not "empty" and not zero. Any comparison with an unknown value is itself unknown, so SQL logic has three values: true, false and NULL. A WHERE clause keeps a row only when the condition is true; a NULL condition drops the row exactly like false does.

The truth table you need

NULL AND false is false (whatever the unknown is, the result is false), but NULL AND true is unknown. Likewise NULL OR true is true, but NULL OR false is unknown. NOT NULL is still unknown.

Three tools handle NULL deliberately:

• IS NULL / IS NOT NULL — the only way to test for it.
• a IS DISTINCT FROM b — a comparison that treats two NULLs as equal and never returns NULL. Use it in triggers and change detection.
• COALESCE(a, b, ...) returns the first non-NULL argument; NULLIF(a, b) returns NULL when a = b, the classic guard against division by zero: x / NULLIF(y, 0).

The || operator propagates NULL, so 'a' || NULL is NULL; concat() skips NULL arguments instead.

The NOT IN trap

x NOT IN (1, NULL) means x <> 1 AND x <> NULL. The second comparison is unknown, so the whole condition can never be true. One NULL in the subquery and NOT IN returns no rows at all. NOT EXISTS compares row by row and is unaffected.

Aggregates, sorting and constraints

Aggregates skip NULL inputs. count(*) counts rows, count(col) counts non-NULL values, and avg(col) divides by the non-NULL count, not the row count. Over zero rows, count returns 0 but sum, avg, min and max return NULL, so a "total" for an empty period needs COALESCE(sum(x), 0).

When sorting, PostgreSQL treats NULL as larger than every value: last in ascending order, first in descending order. NULLS FIRST / NULLS LAST overrides it.

A plain UNIQUE constraint allows any number of NULLs, because no two unknowns are known to be equal. Since PostgreSQL 15 you can declare UNIQUE NULLS NOT DISTINCT to allow only one; the next lesson runs both.

Common mistakes

• WHERE status <> 'closed' to find "not closed" rows: rows with a NULL status are silently excluded. Write status IS DISTINCT FROM 'closed' if they should be included.
• NOT IN (SELECT ...) against a nullable column. One NULL and the query returns nothing, as the example shows; use NOT EXISTS.
• Reporting sum(amount) for a period with no rows and printing NULL instead of 0.
• Computing an average as sum(x) / count(*) when x can be NULL; it disagrees with avg(x), which divides by count(x).

What interviewers ask

• What does SELECT NULL = NULL return, and how do you compare two nullable columns? NULL, not true; use IS NOT DISTINCT FROM.
• Why might NOT IN return zero rows when you expect some? A NULL in the list makes every comparison unknown; NOT EXISTS or a left anti-join avoids it.