NULL in SQL and three-valued logic

In short: In SQL NULL means “unknown”, not “empty” or “zero”. Comparisons with NULL yield neither true nor false but likewise “unknown”.

In more detail: SQL has three truth values: TRUE, FALSE, UNKNOWN. A row appears in the result of a WHERE only if the condition is TRUE.

In Depth

• Comparisons: NULL = NULL is UNKNOWN, not TRUE. So use IS NULL and IS NOT NULL. For null-safe equality: IS NOT DISTINCT FROM.
• Typical trap: WHERE status <> 'x' returns no rows with status IS NULL.
• NOT IN: x NOT IN (1, 2, NULL) never yields TRUE because the comparison with NULL is UNKNOWN. Better NOT EXISTS.
• Arithmetic: 5 + NULL is NULL. COALESCE(a, 0) replaces NULL by a default.
• Aggregates: COUNT(*) counts rows, COUNT(column) counts only non-NULL values. SUM and AVG ignore NULL.
• Sorting: NULL comes first or last depending on the system. NULLS FIRST/LAST fixes it.
• Design: Where possible declare NOT NULL with a default.

See also: NULL concept, SQL