NULL in SQL: three-valued logic and its pitfalls

What it is

SQL has 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; unknown acts like false there, but NOT (unbekannt) is still unknown. The PostgreSQL documentation on comparison operators describes IS NULL, IS NOT NULL and IS [NOT] DISTINCT FROM, which treat NULL as a comparable value: NULL IS DISTINCT FROM NULL yields false and 1 IS DISTINCT FROM NULL yields true, not NULL.

Why it matters

The classic mistakes: WHERE status <> 'archived' silently excludes rows whose status is NULL; id NOT IN (SELECT parent_id FROM t) returns no rows as soon as one parent_id is NULL; according to the aggregate function documentation, COUNT(spalte) counts only rows where the value is not NULL, whereas COUNT(*) counts all input rows; AVG ignores NULL; a unique constraint allows multiple NULLs because, according to the CREATE TABLE documentation, NULL values are not considered equal for this purpose, unless NULLS NOT DISTINCT is specified.

How to apply

• Decide per column whether NULL is allowed and what it means; set NOT NULL wherever "unknown" makes no sense.
• Use IS DISTINCT FROM for equality that treats NULL as a value; use COALESCE to give NULL an explicit replacement value in expressions.
• Use NOT EXISTS instead of NOT IN for subqueries that can contain NULL.
• In application code, check how the driver represents NULL (None, nil, null) and keep it separate from the empty string and the number 0.
• In data quality checks, also track the share of NULL per column: a sudden increase is more likely a sign of a broken data supplier than of genuine unknowns.

Pitfalls

Sorting: depending on the database and on NULLS FIRST/LAST, NULL comes first or last. String concatenation with NULL yields NULL in standard SQL. GROUP BY puts all NULLs into one group, even though NULL is not equal to NULL. Anyone who equates NULL with "empty", "0" or "not applicable" builds the ambiguity into the data model instead of resolving it; at that point only a dedicated status value helps.