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. That's 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 means "unknown" or "missing" — it isn't a value like zero or an empty string. 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 like FALSE for filtering, but it behaves differently under 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;
One more gotcha: aggregate functions like SUM, AVG, and COUNT(column) all ignore NULLs silently. COUNT(*) counts rows, but COUNT(column) counts only non-NULL values in that column — a frequent source of off-by-some-amount bugs.