Why does WHERE email = NULL return zero rows?

The query runs clean. No error, no warning. And it returns zero rows, every single time, even on a table full of missing emails. One character pair is responsible: = NULL.

Why = NULL can never match

NULL is not a value. It is the absence of one, and you cannot compare a value to absence.

email = NULL      -- unknown
email != NULL     -- unknown too
NULL = NULL       -- still unknown

SQL logic has three outcomes: true, false, and unknown. Any comparison involving NULL is unknown, and WHERE keeps only rows where the condition is true. Unknown rows are silently dropped, which is why the query fails without ever raising an error.

The 3-character fix

Ask about existence, not equality:

-- who has no email?
SELECT * FROM users
WHERE email IS NULL;

-- who has one?
SELECT * FROM users
WHERE email IS NOT NULL;

IS NULL is the only operator designed for absence. It always answers true or false, never unknown.

COALESCE: give absence a face

COALESCE returns the first non-null value in its list, left to right:

SELECT COALESCE(email, 'no email')
FROM users;

COALESCE(nickname, name, 'anon')

Perfect for display defaults and report labels. Resist the urge to use it to paper over data-quality problems that should be fixed upstream.

Trap 1: NOT IN meets NULL

One NULL in the list and every row disappears:

-- returns 0 rows if logs contains a NULL
WHERE id NOT IN
  (SELECT ref_id FROM logs)

-- fix: exclude the NULLs
WHERE id NOT IN
  (SELECT ref_id FROM logs
   WHERE ref_id IS NOT NULL)

id NOT IN (1, NULL) means id != 1 AND id != NULL, and that second test is unknown for every row, which sinks the whole condition. NOT EXISTS is immune to this and is often the safer pattern.

Trap 2: aggregates skip NULLs

Same column, two different answers:

COUNT(*)       -- counts all rows
COUNT(email)   -- counts non-null only
AVG(score)     -- ignores NULLs entirely

-- average over ALL rows instead:
SUM(score) / COUNT(*)

AVG divides by the count of non-null values. If NULL means zero in your data, say so explicitly with COALESCE(score, 0).

The takeaway

Treat NULL as a question the data has not answered yet. Test it with IS NULL, default it with COALESCE, and never let it near = or NOT IN unguarded. The print-ready PDF above fits the whole survival kit on one page.