NULL Isn't a Value, It's "Don't Know"

The previous post‘s LEFT JOIN pads NULL for rows that matched nothing. That NULL is this post’s subject — it’s the source of countless SQL bugs, and the root cause is one sentence: NULL isn’t a value, it’s “don’t know”. Once you read it as “don’t know” instead of “0” or “empty string”, a whole pile of strange behaviour becomes explicable.

Compare with NULL and you get a third result: UNKNOWN

Because NULL means “don’t know”, any comparison with it can only answer “don’t know”. age = NULL isn’t TRUE, and it isn’t FALSE either — it’s a third logical value: UNKNOWN. SQL logic has three values, not two:

So the first iron rule: to test for NULL, use only IS NULL / IS NOT NULL, never = NULL / <> NULL. The latter won’t error; it will simply, silently, never hold — which is exactly what makes it insidious.

Three-valued logic: UNKNOWN is contagious

With a third value, the truth tables for AND / OR gain a whole extra row. Only two cells matter:

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

Look at just these two: TRUE AND UNKNOWN = UNKNOWN (the moment one UNKNOWN gets into a chain of ANDs, with no FALSE anywhere, the result is stuck at UNKNOWN and can never reach TRUE); and FALSE AND UNKNOWN = FALSE (FALSE is absorbing and stops the contagion). This “UNKNOWN is contagious” rule is the root of the classic trap below.

The most insidious trap: NOT IN meets NULL and returns nothing

This is the one I’ve seen the most people fall into, and the hardest to spot on your own. You want “users not on the blacklist”:

SELECT * FROM users
WHERE id NOT IN (SELECT blocked_id FROM blacklist);

If the list that subquery returns contains even one NULL, this returns an empty set — not a single row. Why? Expand the NOT IN and you see straight through it:

Two fixes, pick one: switch to NOT EXISTS (unaffected by NULL, and clearer in meaning), or filter NULLs out inside the subquery with WHERE blocked_id IS NOT NULL. My default is the former:

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

Other NULL behaviour you’re bound to meet

The same “NULL = don’t know” principle extends into a string of behaviours you’ll run into sooner or later:

• COUNT(*) counts every row; COUNT(col) counts only non-NULLs — the difference is how many NULLs that column has. More precisely, COUNT(x) is defined as “the number of rows where x is not NULL”, so COUNT(<constant>) only depends on whether the constant is NULL: COUNT(1) counts every row = COUNT(*); while COUNT(NULL) is always 0 (a constant NULL is NULL on every row, so nothing gets counted). And to bust a myth: COUNT(1) isn’t faster than COUNT(*); the optimizer treats them as the same thing, and COUNT(*) says what it means most clearly.
• SUM / AVG / MAX ignore NULL. Watch AVG: its denominator is “the number of non-NULL rows”, not NULL-as-zero — the average you think you’re taking may not be the one you computed.
• GROUP BY puts all NULLs in the same group (here SQL treats them as “equal” — an exception).
• ORDER BY: PostgreSQL sorts NULL last under ASC by default; say NULLS FIRST / NULLS LAST explicitly.
• A UNIQUE constraint allows multiple NULLs: because NULL = NULL is also UNKNOWN, two NULLs don’t count as “duplicates”.

Tools for self-defence

A few very practical tools for handling NULL, worth remembering:

COALESCE(x, 0)              -- if x is NULL, use the default 0 (takes several candidates)
NULLIF(a, b)               -- returns NULL when a = b; classic guard against divide by zero: x / NULLIF(y, 0)
x IS DISTINCT FROM y       -- null-safe "not equal": treats NULL as an ordinary value, NULL equals NULL

IS DISTINCT FROM is especially handy: when you need to compare two columns that “might be NULL” and want “both NULL counts as the same”, it keeps you out of the UNKNOWN pit.

Reflections

Read NULL as “don’t know”, and a whole row of pits gets filled at once

When I started with SQL I treated these as separate odd rules to memorise: no = NULL, NOT IN goes wrong, AVG ignores NULL… Later I found they’re all extensions of one idea — NULL is “don’t know”, not “0” and not “empty”. Compare with “don’t know” and of course the result is “don’t know” (UNKNOWN); average a pile of “don’t knows” and of course the unknowns have to be excluded first. Once that meaning is swapped in, I no longer need to memorise rules; I can derive NULL’s behaviour in any situation. It’s the same gain as the processing order and JOIN posts: find the single consistent idea, and the rules degrade into deductions.

The most expensive bugs are the “runs fine, no error” kind

NULL traps almost never make your query error — NOT IN silently returns nothing, AVG silently computes wrong, = NULL silently matches nothing. It’s the same insidiousness as the previous post‘s “LEFT JOIN quietly becomes INNER”: runs, no error, numbers even look right — just wrong. These bugs cost the most, because there’s no red text to warn you, and they’re usually traced back only after someone downstream has reconciled numbers to the point of despair. So I’ve built a reflex: whenever a condition contains NOT IN, or compares a column that might be NULL, stop and ask “can this column be NULL?” That one question has caught more bugs than any tool.

Pin down NULL’s meaning at the source

When NULL bites you in a query, the problem is often further upstream: why was this column allowed to be NULL in the first place? Worse, NULL is often polysemous — in the same column, NULL might mean “hasn’t happened yet”, “not applicable”, or “data missing”. When I design a schema or pipeline now, I try to settle it at the source: use an explicit default instead of NULL where possible, and where NULL must stay, decide which kind of “nothing” it stands for. Settle the semantics at the source, and downstream doesn’t have to COALESCE its way through guessing what it meant — consistent with my attitude to data modelling generally: pushing the mess one step upstream and fixing it once beats patching it in every downstream.