NULL in SQL: three-valued logic and its traps

What it is

SQL uses three truth values: true, false and unknown. Any comparison with NULL (x = NULL, x <> NULL, x > 5 when x is NULL) yields unknown. WHERE keeps only rows whose condition is true, so unknown behaves like false there, but NOT (unknown) is still unknown. The PostgreSQL documentation describes IS NULL, IS NOT NULL and IS [NOT] DISTINCT FROM, which treat NULL as a comparable value.

Why it matters

The classic bugs: WHERE status <> 'archived' silently excludes rows with NULL status; id NOT IN (SELECT parent_id FROM t) returns no rows if any parent_id is NULL; COUNT(col) counts non-NULL values while COUNT(*) counts rows; AVG ignores NULLs; a unique constraint allows several NULLs (unless NULLS NOT DISTINCT is specified).

How to apply

• Decide per column whether NULL is allowed and what it means; add NOT NULL wherever there is no meaning for "unknown".
• Use IS DISTINCT FROM for equality that treats NULL as a value; use COALESCE to give NULL an explicit default in expressions.
• Prefer NOT EXISTS over NOT IN with subqueries that may contain NULLs.
• In application code, check how the driver maps NULL (None, nil, null) and keep it distinct from empty strings and zero.

Pitfalls

Sorting: NULLs sort first or last depending on the database and NULLS FIRST/LAST. String concatenation with NULL yields NULL in standard SQL. GROUP BY puts all NULLs in one group even though NULL is not equal to NULL.