Relational Foundations

Nulls and Three-Valued Logic

SQL NULL marks the absence of a regular value and makes many comparisons evaluate to UNKNOWN, producing three-valued logic rather than ordinary Boolean logic. Because filtering retains only TRUE, correct queries must account for UNKNOWN explicitly.
  • Ordinary comparison with NULL, including NULL = NULL, evaluates to UNKNOWN; use IS NULL and IS NOT NULL, not = NULL or <> NULL.
  • Most comparisons with NULL produce UNKNOWN.
  • NOT UNKNOWN remains UNKNOWN; negating a nullable comparison does not necessarily include the rows the comparison excluded.
  • FALSE AND UNKNOWN is FALSE, while TRUE OR UNKNOWN is TRUE; the known operand can determine the result.
  • NOT IN is vulnerable to a single NULL on its right side.
  • Date argues for avoiding nulls and retaining two-valued relational logic; mainstream SQL practice accepts nulls but requires deliberate constraints and null-aware predicates.

Complete SQL three-valued truth tables

AND and OR for every pair of truth values
pqp AND qp OR q
TRUETRUETRUETRUE
TRUEFALSEFALSETRUE
TRUEUNKNOWNUNKNOWNTRUE
FALSETRUEFALSETRUE
FALSEFALSEFALSEFALSE
FALSEUNKNOWNFALSEUNKNOWN
UNKNOWNTRUEUNKNOWNTRUE
UNKNOWNFALSEFALSEUNKNOWN
UNKNOWNUNKNOWNUNKNOWNUNKNOWN
NOT for every truth value
pNOT p
TRUEFALSE
FALSETRUE
UNKNOWNUNKNOWN

The NOT IN / NULL trap

Assume blocked_customer(customer_id) returns (7) and (NULL). For customer 12, 12 NOT IN (7, NULL) is equivalent to 12 <> 7 AND 12 <> NULL: TRUE AND UNKNOWN becomes UNKNOWN, and WHERE discards the row. Customer 12 is therefore not returned even though 12 does not equal 7.

Null-safe anti-membership
SELECT c.customer_id
FROM customer AS c
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_customer AS b
  WHERE b.customer_id = c.customer_id
);
`NOT EXISTS` tests whether a matching row exists and is not poisoned by unrelated nulls.