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

How to think about it

What the question is really testing

This is an early filter for candidates who have actually written production SQL. NULL behavior is counterintuitive — it trips up even experienced engineers in compound WHERE clauses. The question checks whether you understand three-valued logic and reach for IS NULL reflexively.

The root cause — three-valued logic

Regular programming languages have two-valued logic: TRUE or FALSE. SQL has three: TRUE, FALSE, and NULL (which means “unknown”).

When you write email = NULL, SQL doesn’t know whether the email equals NULL because NULL represents an unknown value — you can’t know if two unknowns are the same unknown. So the expression evaluates to NULL, not TRUE or FALSE. And WHERE only passes rows where the predicate is TRUE.

email = NULL  →  NULL  →  row is discarded

This happens for every row, which is why WHERE email = NULL always returns zero rows.

The correct syntax

-- Find rows where email is missing
SELECT * FROM users WHERE email IS NULL;

-- Find rows where email has a value
SELECT * FROM users WHERE email IS NOT NULL;

IS NULL is not a comparison — it is a special predicate that checks for the absence of a value and always returns TRUE or FALSE, never NULL.

Try it live — see NULL = NULL return nothing

NULL propagates through compound conditions

This is the follow-up trap. Even when you use a correct comparison on another column, NULL propagates through AND chains:

-- If discount IS NULL, the whole AND becomes NULL → row excluded
WHERE price > 100 AND discount < 0.2

If discount is NULL, the row is silently dropped even though price > 100 is TRUE. To include NULL-discount rows:

WHERE price > 100 AND (discount < 0.2 OR discount IS NULL)

Similarly, WHERE col != 'value' does NOT return rows where col is NULL. NULL comparisons always produce NULL — those rows vanish too. If you want “anything except this value, plus the NULLs”:

WHERE col != 'value' OR col IS NULL

The one-liner to say out loud: “NULL is unknown, and unknown compared to anything is still unknown — use IS NULL, not = NULL.”