Why does WHERE column = NULL never return rows in SQL?

How to think about it

This is an early filter for candidates who’ve actually written production SQL. NULL behaviour is counterintuitive enough to trip up experienced engineers in compound WHERE clauses, so the interviewer is checking whether you understand three-valued logic and reach for IS NULL by reflex.

Most languages have two-valued logic — true or false. SQL has three: true, false, and NULL (“unknown”). When you write email = NULL, the engine can’t know whether an unknown value equals an unknown value, so the expression evaluates to NULL — not true, not false. And WHERE only passes rows where the predicate is true. That happens for every row, which is why the query always returns nothing.

A worked example — the empty result, then the fix

SELECT id, name, email FROM users WHERE email = NULL;

id	name	email
(zero rows)

IS NULL isn’t a comparison — it’s a dedicated predicate that asks “is this value absent?” and always returns true or false, never NULL:

SELECT id, name, email FROM users WHERE email IS NULL;

id	name	email
2	Bea	NULL
3	Chen	NULL

Same table, same intent — only the operator changed, and now the two missing-email rows come back.

The follow-up: NULL propagates through compound conditions

The trap deepens in AND/!= chains. A NULL on one column poisons the whole predicate even when another comparison is true:

-- if discount IS NULL, the AND becomes NULL -> row dropped even though price > 100 is TRUE
WHERE price > 100 AND discount < 0.2

-- include the NULL-discount rows explicitly:
WHERE price > 100 AND (discount < 0.2 OR discount IS NULL)

The same applies to inequality: WHERE status != 'inactive' silently excludes rows where status is NULL — they aren’t inactive, but they aren’t returned either. To keep them, say so:

WHERE status != 'inactive' OR status IS NULL