Chapter 1 · 7 min
NULL and three-valued logic
NULL is not a value you can compare with =. It means “unknown or absent”, and SQL evaluates
predicates involving it as neither true nor false. Imagine the orders table has a nullable
cancelled_at column. The practical consequence is blunt: WHERE column = NULL never returns the rows where the column is null, because the comparison itself is
unknown.
-- Returns nothing, even when cancelled_at is null.
SELECT order_id FROM orders WHERE cancelled_at = NULL;
-- The predicate that actually asks the question.
SELECT order_id FROM orders WHERE cancelled_at IS NULL;
IS NULL and IS NOT NULL are the only operators that test for missingness. IS DISTINCT FROM
is the null-safe comparison when you need equality semantics that treat two nulls as equal.
Three-valued logic in practice
NOT reverses true and false but leaves unknown as unknown. That is why a NOT IN list containing
a null can return no rows at all: each comparison against the null is unknown, and NOT (unknown)
is still unknown, so no row qualifies. Prefer NOT EXISTS or filter the null out of the list when
the subquery can produce one.
The same rule applies to aggregates. count(column) ignores nulls, while count(*) counts rows.
sum and avg skip nulls too, which means the denominator of an average is not necessarily the
row count. A question that asks for an average over all rows and an average over rows with a value
is asking two different things.
Coalesce and defaults
COALESCE(column, fallback) returns the first non-null argument. It is the right tool when a
report needs a display value instead of a blank:
SELECT order_id, COALESCE(cancelled_reason, 'not cancelled') AS reason
FROM orders;
Use it for presentation, not for filtering. Replacing nulls with a default before a WHERE changes
which rows match, and that change is easy to miss. Filter on the real column, then coalesce in the
select list.
What interviews look for
When a schema has nullable columns, say how nulls affect the answer before writing the query. “Rows
with no cancellation date” is IS NULL; “rows whose cancellation date is not February” excludes
nulls unless you add OR cancelled_at IS NULL. That distinction is the whole question, and it is
worth stating explicitly rather than hoping the engine guesses your intent.