NULL handling — three-valued logic and gotchas

**Logical operators with NULL** (3VL truth table): `NOT NULL = NULL`. `TRUE AND NULL = NULL`. `FALSE AND NULL = FALSE` (short-circuit on FALSE). `TRUE OR NULL = TRUE` (short-circuit on TRUE). `FALSE OR NULL = NULL`. WHERE filters rows where the predicate is strictly TRUE — rows evaluating to NULL are excluded.

NULL-safe comparison: standard x IS DISTINCT FROM y (Postgres, SQL Server) evaluates NULL as a comparable value — NULL IS DISTINCT FROM 1 = TRUE, NULL IS DISTINCT FROM NULL = FALSE. MySQL: x <=> y. Essential when you want 'the values differ, counting NULL≠NULL as same'.

**Predicate NOT IN + subquery with NULL**: `WHERE x NOT IN (SELECT y FROM t)` — if any y is NULL, the whole predicate becomes NULL → zero rows returned. The silent killer of 'why doesn't my NOT IN query return anything?'. Use `NOT EXISTS` instead.

Unique constraints and NULL: SQL standard treats NULLs as distinct for uniqueness — a UNIQUE column can have multiple NULL values. PostgreSQL + MySQL follow this. SQL Server historically did NOT (added NULLS NOT DISTINCT in 2022). Understand your engine.

Join on NULL: FROM a JOIN b ON a.x = b.x — rows where a.x is NULL do NOT match rows where b.x is NULL (NULL = NULL is NULL, not TRUE). Use a.x IS NOT DISTINCT FROM b.x or explicit OR (a.x IS NULL AND b.x IS NULL) if you want NULL-matches-NULL.

Aggregate semantics: COUNT(*) includes NULLs; COUNT(col) excludes NULLs; COUNT(DISTINCT col) excludes NULLs. SUM/AVG/MIN/MAX skip NULL entirely — caution with AVG, the denominator is non-NULL count, not row count.

**COALESCE vs IFNULL vs ISNULL**: COALESCE is SQL-standard + N-ary + short-circuit. IFNULL (MySQL) is binary. ISNULL (SQL Server) is binary. NVL (Oracle) is binary. Prefer COALESCE for portability.

NULL in ORDER BY: standard permits ASC/DESC combined with NULLS FIRST/NULLS LAST. Postgres default: ASC → NULLS LAST, DESC → NULLS FIRST. MySQL: NULLs sort first regardless of direction (treated as lowest value). SQL Server: NULLs sort first in ASC, last in DESC. Test before relying on a default.