You write a query to find every customer with no phone number on file. WHERE phone = NULL looks obviously correct — and it returns zero rows, even though you can see NULL sitting right there in the column. Nothing crashes. No error. The query just quietly lies to you about what’s in the table.

This isn’t SQL being broken. It’s SQL being consistent about something most languages don’t force you to think about: NULL doesn’t mean “nothing,” it means “unknown.” And you can’t compare something to unknown with = and expect a real answer.

What’s actually happening

Take this table:

-- customers
| id | name        | phone      |
|----|-------------|------------|
| 1  | Jordan Lee  | 555-0142   |
| 2  | Sam Rivera  | NULL       |
| 3  | Alex Chen   | 555-0198   |
SELECT name FROM customers WHERE phone = NULL;
-- returns 0 rows

SQL doesn’t evaluate conditions as just true or false — it has a third result: unknown. phone = NULL asks “does this unknown value equal this other unknown value?” There’s no way to answer that, so SQL returns UNKNOWN for every single row, including Sam Rivera’s. And WHERE only keeps rows where the condition is TRUE. UNKNOWN doesn’t qualify, so the row gets filtered out — the exact same as if it had evaluated to FALSE.

This is true even for the row that “should” match. NULL = NULL isn’t TRUE — it’s also UNKNOWN. NULL never equals anything, not even another NULL. That’s the whole rule, and it applies uniformly, which is why = can’t be patched into working here — it’s not almost right, it’s answering a different question than the one you’re asking.

The fix, step by step

  1. Recognize the symptom: a query that runs cleanly but returns fewer rows than it should — especially zero rows when you can see matching data — with a NULL column somewhere in the WHERE clause.
  2. Swap = for IS NULL (or != for IS NOT NULL). These are dedicated operators built specifically to test for absence, not comparison operators being asked to do something they can’t.
  3. Rewrite the query: WHERE phone IS NULL instead of WHERE phone = NULL.
  4. Check compound conditions too. WHERE phone = NULL OR phone = '' has the same bug hiding in it — the OR doesn’t rescue the broken half.
  5. Confirm with a plain SELECT * on the table first, so you know what you’re actually expecting to match before trusting the filtered result.

Two mistakes worth knowing about ahead of time

Assuming != is the correct opposite. WHERE phone != NULL doesn’t return “everyone with a phone number” — it returns nothing, for the identical reason: != is still a comparison, and comparing anything to NULL is still UNKNOWN, still filtered out. If you want “has a value,” the operator is IS NOT NULL, not !=.

Trusting COUNT(*) to tell you the same thing as COUNT(column). COUNT(*) counts rows. COUNT(phone) counts only the rows where phone isn’t NULL — the two numbers can legitimately be different, and the gap between them is often the fastest way to notice you have more missing data than you thought, before it silently breaks a filter somewhere else.

A debugging habit that works

When a query returns fewer rows than expected, don’t start by rewriting the logic — start by running SELECT * FROM table with no WHERE clause at all, and look for NULL in any column your filter touches. If it’s there, the fix is almost always mechanical: swap the comparison operator for IS NULL or IS NOT NULL and rerun.

The habit worth keeping past this one query: before writing = NULL anywhere, ask whether the column can be NULL in the first place. If a column is genuinely required to always have a value, enforce that with a NOT NULL constraint at the schema level — the same instinct as tracing an undefined back to its source in JavaScript or a None back to a missing return in Python. Missing data is either expected and worth handling deliberately, or it’s a sign something upstream should never have let the row in empty to begin with.