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.