NULL, and the third truth value

SQL has three truth values: true, false, and unknown. Any comparison involving NULL produces unknown, and a WHERE clause keeps a row only when its predicate is true. Unknown is not a reason to keep a row, and it is not a reason to drop it either — it is simply not true, and that is enough for the row to disappear.

SELECT NULL = NULL AS null_equals_null, NULL <> NULL AS null_differs, (NULL = NULL) IS NULL AS actually_unknown

A predicate and its opposite do not cover the table

This is the consequence people are surprised by. If a column is nullable, WHERE total > 100 and WHERE total <= 100 do not partition the table between them — the rows where total is null fall out of both.

SELECT count(*) FILTER (WHERE total > 1000) AS big, count(*) FILTER (WHERE total <= 1000) AS small, count(*) AS all_rows FROM orders

NOT IN is the trap worth memorising

x IN (1, 2, NULL) is true for 1, and unknown — not false — for 3, because SQL cannot rule out that the null was a 3. NOT IN is therefore never true the moment the list contains a null, and the query returns nothing at all however obviously the value is absent.

SELECT (3 IN (1, 2, NULL)) IS NULL AS in_is_unknown, 3 NOT IN (1, 2) AS not_in_is_fine

The fix is not to remember this at the moment you write it, because you will not. The fix is NOT EXISTS, which asks a question that has an answer whatever the nulls are doing.

The operator that will compare a null

IS DISTINCT FROM is the null-safe comparison: two nulls are not distinct, and a null and a value are. It is what = refuses to say, and it is why it exists.

SELECT NULL IS NOT DISTINCT FROM NULL AS same, 1 IS DISTINCT FROM NULL AS different

Where grouping disagrees with equality

GROUP BY, DISTINCT and the set operations all treat two nulls as the same value, which = flatly refuses to do. Grouping and equality are different questions, and this is the clearest place that shows.

SELECT DISTINCT total FROM orders WHERE total IS NULL