NULL in SQL isn’t a value. It’s a marker that says “there is no value here”, and the language treats any question about a missing value as unanswerable. Ask whether a missing country equals 'US' and SQL doesn’t say no; it says “unknown”. That third answer, alongside true and false, is what three-valued logic means, and it explains a whole category of queries that return fewer rows than you expected.
Everything below was run on PostgreSQL 16.15. The logic is standard SQL; where other databases differ (sorting, mainly), it’s called out.
Comparisons with NULL are unknown
SELECT NULL = NULL AS eq,
NULL <> 1 AS ne,
NULL IS NULL AS is_null,
NULL IS DISTINCT FROM NULL AS distinct_nn,
1 IS DISTINCT FROM NULL AS distinct_1n; eq | ne | is_null | distinct_nn | distinct_1n
----+----+---------+-------------+-------------
| | t | f | tThe blank cells are NULL, which is how PostgreSQL represents a boolean unknown. NULL = NULL isn’t true: two missing values aren’t known to be equal. NULL <> 1 isn’t true either: a missing value isn’t known to differ from 1. The only reliable tests are the IS family. IS NULL asks “is this missing?”. IS DISTINCT FROM is a comparison that treats NULL as an ordinary value, so two NULLs are not distinct and NULL is distinct from 1. It’s the operator to reach for when comparing nullable columns, for example in change detection (WHERE old.email IS DISTINCT FROM new.email).
AND, OR and NOT with a third value
The truth tables make sense once you read unknown as “could be either”:
SELECT (true AND NULL) AS t_and_n, (false AND NULL) AS f_and_n,
(true OR NULL) AS t_or_n, (false OR NULL) AS f_or_n,
(NOT NULL::boolean) AS not_n; t_and_n | f_and_n | t_or_n | f_or_n | not_n
---------+---------+--------+--------+-------
| f | t | |false AND unknown is false: whatever the unknown is, the AND fails. true OR unknown is true for the same reason. The other combinations depend on the missing value, so they stay unknown. NOT unknown is still unknown, which is the rule that surprises people most.
The final piece is how clauses use the result. WHERE, HAVING, ON and CASE WHEN keep or take only rows where the condition is true. Unknown is treated like false there. CHECK constraints reject only rows where the condition is false, so unknown passes. Those two rules produce every NULL bug in this guide.
The rows that fall through both sides
A users table with a few NULLs:
CREATE TABLE users (
id int PRIMARY KEY, name text, country text, deleted_at timestamptz,
discount int CHECK (discount BETWEEN 0 AND 50),
referral_code text UNIQUE
);
INSERT INTO users VALUES
(1, 'Ana', 'PT', NULL, 10, 'A1'),
(2, 'Ben', NULL, NULL, NULL, NULL),
(3, 'Chen', 'US', '2026-09-01', NULL, NULL),
(4, 'Dee', 'US', NULL, 20, 'D4');SELECT count(*) AS total,
count(*) FILTER (WHERE country = 'US') AS us,
count(*) FILTER (WHERE NOT (country = 'US')) AS not_us
FROM users; total | us | not_us
-------+----+--------
4 | 2 | 1Two plus one is three, not four. Ben, whose country is unknown, is in neither group. country = 'US' is unknown for him, and so is NOT (country = 'US'). This is the property interviewers probe: a condition and its negation don’t always partition a table. Any report that splits rows into “matches” and “doesn’t match” with a WHERE and its negation loses the NULL rows silently.
If “not US” should include unknown countries, say so explicitly:
SELECT count(*) FROM users WHERE country IS DISTINCT FROM 'US'; -- 2
-- or: WHERE country <> 'US' OR country IS NULLNOT IN and the NULL in the list
SELECT name FROM users WHERE id NOT IN (1, NULL); name
------
(0 rows)You might expect Ben, Chen and Dee. But 2 NOT IN (1, NULL) means 2 <> 1 AND 2 <> NULL, which is true AND unknown, which is unknown. Every row is unknown or false, so nothing comes back. With a literal list you’d spot the NULL. With NOT IN (SELECT customer_id FROM orders), one NULL in a column of millions does the same thing. Use NOT EXISTS instead; the EXISTS guide shows the rewrite.
Constraints: CHECK lets NULL through, UNIQUE allows many
Ben’s discount is NULL, and the insert succeeded despite CHECK (discount BETWEEN 0 AND 50). The check evaluated to unknown, and CHECK only rejects false. If a value must be present, add NOT NULL; CHECK alone doesn’t imply it.
Ben and Chen both have a NULL referral_code under a UNIQUE constraint, and that’s allowed: NULLs aren’t equal to each other, so they don’t collide. That’s usually what you want (“unique if present”). PostgreSQL 15 added a way to opt out:
CREATE TABLE t2 (code text, CONSTRAINT u UNIQUE NULLS NOT DISTINCT (code));
INSERT INTO t2 VALUES (NULL);
INSERT INTO t2 VALUES (NULL);
-- ERROR: duplicate key value violates unique constraint "u"
-- DETAIL: Key (code)=(null) already exists.Aggregates, CASE, sorting and concatenation
Aggregates skip NULLs. avg(discount) is 15 (the average of 10 and 20), while avg(coalesce(discount, 0)) is 7.5. Which is right depends on what NULL means: if it means “no discount”, it’s 0 and the second is correct; if it means “not recorded”, the first is. count(*) and count(col) differ for the same reason, covered in count semantics.
Simple CASE can’t match NULL. CASE country WHEN NULL THEN 'unknown' ELSE country END returned NULL for Ben, not 'unknown', because the simple form compares with =. Write CASE WHEN country IS NULL THEN 'unknown' ELSE country END, or coalesce(country, 'unknown').
Sorting. PostgreSQL sorts NULLs as if larger than every value: last in ascending order, first in descending. Ben came last with ORDER BY country and first with ORDER BY country DESC. SQLite and MySQL do the opposite (NULLs first in ascending order). If the position matters, write NULLS FIRST or NULLS LAST and don’t rely on the default.
String concatenation. 'x' || NULL is NULL in PostgreSQL, while concat('x', NULL) returns 'x'. A first_name || ' ' || last_name expression turns the whole name NULL if either part is missing.
Deciding what NULL means
Most NULL bugs are really modelling bugs: one column uses NULL for several meanings. deleted_at NULL means “not deleted”, a clear meaning that queries can rely on (WHERE deleted_at IS NULL). country NULL might mean “user didn’t say”, “we didn’t ask” or “not applicable for this account type”, and every report has to guess. When the distinction matters, store it: a NOT NULL column with an explicit 'unknown' value, or a separate status column. When a value is always required, NOT NULL is the cheapest constraint you can add, and it removes the three-valued logic from every query on that column.
Interview practice: the retention segment that lost its best customers
A marketing team wants to email everyone who did not cancel this year, meaning customers who are still active plus those who cancelled in earlier years and might come back. The query is:
SELECT id, email
FROM customers
WHERE NOT (cancelled_at >= '2026-01-01');Active customers have cancelled_at NULL. The campaign went out, and the team noticed none of their active customers received it. Explain why, and rewrite the query.
Answer
For an active customer, cancelled_at >= '2026-01-01' compares NULL with a date, which is unknown. NOT unknown is still unknown, and WHERE keeps only true rows, so every active customer was filtered out. The query returned only people who cancelled before 2026, which is the opposite of the most valuable part of the segment. The NOT reads like “everyone except this year’s cancellations”, but it can’t turn unknown into true.
The rewrite has to say how NULL is treated:
SELECT id, email
FROM customers
WHERE cancelled_at IS NULL
OR cancelled_at < '2026-01-01';cancelled_at IS DISTINCT FROM doesn’t help directly here because the test is a range, not equality, so the explicit IS NULL branch is the clearest form. I’d verify with one query that counts every group from the same snapshot, count(*) alongside count(*) FILTER (WHERE cancelled_at IS NULL), count(*) FILTER (WHERE cancelled_at < '2026-01-01') and count(*) FILTER (WHERE cancelled_at >= '2026-01-01'), and check that the three filtered counts add up to the total. A future-dated cancelled_at (a scheduled cancellation) would land in the third group, which is worth raising with the team as a product decision.