NULL and three-valued logic

NULL is “unknown”, not “empty” or “zero”

The single idea everything else hangs on: NULL is not a value, it’s the absence of a known value — “we don’t know”. It is not zero, not an empty string, not false. Two NULLs are not equal to each other, because “unknown” can’t be confirmed equal to “unknown”.

That means comparisons against NULL don’t return TRUE or FALSE — they return a third truth value, UNKNOWN. 5 = NULL is UNKNOWN. NULL = NULL is UNKNOWN. NULL <> 3 is UNKNOWN. SQL is therefore a three-valued logic: TRUE, FALSE, and UNKNOWN.

The hard rule that follows: WHERE keeps a row only when its predicate evaluates to TRUE. UNKNOWN is not TRUE, so a row whose predicate is UNKNOWN is dropped — just like FALSE. That symmetry is the source of nearly every NULL surprise.

-- users(id, email, deleted_at)  -- deleted_at is NULL for active users
SELECT count(*) FROM users WHERE deleted_at = NULL;   -- always 0 rows!
SELECT count(*) FROM users WHERE deleted_at IS NULL;  -- the correct test

= NULL is never the test you want. To ask “is this unknown?” you must use the dedicated IS NULL / IS NOT NULL operators, which return real booleans.

The three-valued truth tables

Each connective has to define what it does when an operand is UNKNOWN. The key entries:

The two short-circuit rows matter in practice: TRUE OR <anything> is TRUE and FALSE AND <anything> is FALSE, even when “anything” is UNKNOWN — because the result is already determined. Every other combination touching UNKNOWN yields UNKNOWN.

The NOT IN trap, decoded

Now the opening incident. x NOT IN (a, b, c) is defined as x <> a AND x <> b AND x <> c. Suppose the list is (1, 2, NULL) and x = 5:

5 <> 1  → TRUE
5 <> 2  → TRUE
5 <> NULL → UNKNOWN      -- because comparing to NULL is always UNKNOWN
TRUE AND TRUE AND UNKNOWN → UNKNOWN

The whole expression collapses to UNKNOWN, so the row is dropped. With even one NULL in the NOT IN list, no row can ever be TRUE — the query returns zero rows, silently, for every value. That’s exactly how a single NULL in blocked_users zeroed out the fraud filter.

The fixes, in order of preference:

• Use NOT EXISTS instead of NOT IN for subqueries. NOT EXISTS is NULL-safe (it checks row existence, not value equality) and usually plans better as an anti-join:

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_users b WHERE b.user_id = u.id
);

• Or exclude NULLs from the subquery: ... NOT IN (SELECT user_id FROM blocked_users WHERE user_id IS NOT NULL).
• Better still, make the column NOT NULL so the bug is impossible by construction.

All three fixes address the same root: once a NULL can reach the comparison, the logic collapses. The strongest fix (NOT NULL constraint) eliminates the possibility at the schema level; NOT EXISTS is the safest drop-in replacement for existing queries without a schema change.

IS DISTINCT FROM, and the NULL-aware toolkit

Sometimes you genuinely want “are these two values different, treating NULL as a normal comparable value?” Plain <> can’t, because it goes UNKNOWN on NULL. Postgres gives you IS DISTINCT FROM: it returns a real boolean, treating two NULLs as not distinct (equal) and a NULL-vs-value as distinct.

-- detect a changed column even when either side may be NULL:
SELECT * FROM staging s JOIN target t USING (id)
WHERE s.email IS DISTINCT FROM t.email;   -- TRUE/FALSE, never UNKNOWN

The everyday helpers:

• COALESCE(a, b, …) returns the first non-NULL argument — your default-value tool: COALESCE(nickname, email, 'anon').
• NULLIF(a, b) returns NULL when a = b, else a — handy to dodge divide-by-zero: total / NULLIF(qty, 0).

And two aggregate facts that trip people: aggregates skip NULLs. COUNT(col) counts only non-NULL values of col, while COUNT(*) counts rows regardless. AVG(col) averages over non-NULL values only — so an AVG can differ from SUM/COUNT(*) when NULLs are present.