How does NULL and three-valued logic work in SQL?
Quick Answer
SQL uses three-valued logic: `TRUE`, `FALSE`, and `UNKNOWN`. Any comparison involving `NULL` (e.g., `x = NULL`, `x <> NULL`) evaluates to `UNKNOWN`, not `TRUE` or `FALSE` — which is why you must use `IS NULL`/`IS NOT NULL` instead of `= NULL`. Rows are only included by `WHERE`/`HAVING` when the condition is `TRUE`; both `FALSE` and `UNKNOWN` exclude the row, which trips people up in `NOT IN` queries containing NULLs.
Detailed Answer
NULL represents "unknown" or "missing," not a value like zero or an empty string — and that distinction drives SQL's three-valued logic.
The three truth values
Any predicate evaluates to TRUE, FALSE, or UNKNOWN. NULL compared to anything — including another NULL — produces UNKNOWN:
SELECT NULL = NULL; -- UNKNOWN (not TRUE!)
SELECT NULL <> NULL; -- UNKNOWN
SELECT 5 = NULL; -- UNKNOWN
SELECT 5 <> NULL; -- UNKNOWN
WHERE and HAVING only keep rows where the condition is TRUE — UNKNOWN is treated the same as FALSE for filtering purposes (but differently for NOT, see below).
Testing for NULL correctly
-- Wrong: always UNKNOWN, matches nothing
SELECT * FROM users WHERE middle_name = NULL;
-- Correct
SELECT * FROM users WHERE middle_name IS NULL;
SELECT * FROM users WHERE middle_name IS NOT NULL;
The classic NOT IN trap
This is the single most common NULL bug in production SQL:
-- If banned_ids contains even one NULL, this returns ZERO rows,
-- because "x NOT IN (1, 2, NULL)" expands to
-- "x <> 1 AND x <> 2 AND x <> NULL", and the last comparison is UNKNOWN,
-- which poisons the whole AND chain to UNKNOWN.
SELECT * FROM users WHERE id NOT IN (SELECT banned_id FROM bans);
Fix it with NOT EXISTS (which handles NULLs correctly) or by filtering NULLs out of the subquery explicitly:
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM bans b WHERE b.banned_id = u.id);
Useful NULL-handling functions
COALESCE(a, b, c) -- returns the first non-NULL argument
NULLIF(a, b) -- returns NULL if a = b, otherwise returns a
-- Avoid division by zero producing an error, return NULL instead
SELECT revenue / NULLIF(units_sold, 0) AS avg_price FROM sales;
-- Provide a default for a possibly-NULL column
SELECT COALESCE(nickname, first_name) AS display_name FROM users;
Also remember that aggregate functions like SUM, AVG, COUNT(column) all ignore NULLs silently — COUNT(*) counts rows, but COUNT(column) counts only non-NULL values in that column, which is a frequent source of off-by-some-amount bugs.