Why 'WHERE x = NULL' Never Works in SQL (And What to Use Instead)

Adapted from the SQL Essentials Companion Guide . 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 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. 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 somethin