NULL and Three-Valued Logic

## The mistake and the fix ```sql SELECT * FROM users WHERE email = NULL; -- always empty SELECT * FROM users WHERE email IS NULL; -- the rows you wanted ``` The first query is not an error and produces no warning on most engines. It simply returns zero rows, forever, whatever the data looks like. That silence is why this is the single most common SQL trap in interviews and in production. ## NULL is a marker, not a value A NULL in a column does not mean zero, an empty string, or "a special value that equals other NULLs". It records that the value is *absent* — unknown, inapplicable, or not yet supplied. Two rows with a NULL `email` are not two rows that share an email address; they are two rows about which the same thing is unknown. That framing explains every rule that follows. Ask "is this unknown value equal to `'[email protected]'`?" and the only honest answer is "I cannot tell". Ask "is this unknown value equal to that other unknown value?" and the answer is still "I cannot tell". ## Three-valued logic Because of that, a SQL predicate does not return a boolean with two states. It returns one of **TRUE**, **FALSE**, or **UNKNOWN**. Every comparison operator — `=`, `<>`, `<`, `<=`, `>`, `>=` — returns UNKNOWN as soon as either operand is NULL: ```sql SELECT NULL = NULL; -- UNKNOWN (not TRUE) SELECT NULL <> NULL; -- UNKNOWN (not TRUE either) SELECT 5 > NULL; -- UNKNOWN ``` Note that `<>` does not "flip" UNKNOWN into something useful: the negation of "I cannot tell" is still "I cannot tell". ## Why the row disappears The `WHERE` clause is specified to retain a row **if and only if its search condition evaluates to TRUE**. Rows whose condition is FALSE are dropped, and so are rows whose condition is UNKNOWN — the clause makes no distinction between "definitely not a match" and "cannot be determined". `email = NULL` is UNKNOWN for every row (whether the email is missing or present), so every row is discarded. The same TRUE-only rule applies to `HAVING` and to a join's `ON` condition, which is why NULLs also quietly refuse to match in join predicates. ## IS NULL: a predicate, not a comparison `IS NULL` and `IS NOT NULL` are separate grammar in the standard. They ask a question about the *marker*, not about the value behind it, so they are two-valued: they always return TRUE or FALSE and can never yield UNKNOWN. ```sql -- rows with no email at all SELECT user_id FROM users WHERE email IS NULL; -- rows that definitely have one SELECT user_id FROM users WHERE email IS NOT NULL; ``` Because the two are exact complements, `COUNT` of the first plus `COUNT` of the second always equals the table's row count — something that is *not* true for `= 'x'` and `<> 'x'`. ## Empty string is not NULL A very common follow-up: `email = ''` and `email IS NULL` are different tests in standard SQL. An empty string is a known value of length zero; NULL is the absence of a value. A column can hold either, and a data-quality check often has to look for both: ```sql SELECT user_id FROM users WHERE email IS NULL OR email = ''; ``` Oracle is the notable exception here: it stores an empty string as NULL, so the distinction collapses on that engine. Elsewhere, keep the two apart deliberately. ## Related shapes worth recognising - `CASE WHEN email = NULL THEN 'missing' ELSE 'present' END` never takes the first branch, for exactly the same reason — the `WHEN` condition must be TRUE. - Parameterised queries hit this hard: a predicate like `WHERE email = :param` silently returns nothing when the application passes a NULL parameter, rather than "all rows" or "the NULL rows". If both behaviours are wanted, the query needs an explicit NULL branch or a NULL-safe comparison. - Sorting and grouping do *not* follow the comparison rule: `GROUP BY` and `DISTINCT` treat NULLs as alike and collapse them into one group. That inconsistency surprises people, and it is worth being able to say out loud that comparison semantics and grouping semantics are defined separately. ## How to say it in an interview Three sentences carry the whole answer: NULL means unknown; comparing with an unknown yields UNKNOWN; `WHERE` keeps only TRUE, so the row is dropped. Then give the fix, `IS NULL`, and mention that it is the one test that can never return UNKNOWN.