Why = NULL never works in SQL (and what to use instead)

You write this, run it, and get nothing:

SELECT name
FROM customers
WHERE phone = NULL

The data is there. There are customers with no phone. But the query returns zero rows. Why?

NULL is not a value. It’s the absence of a value.

In SQL, NULL means “unknown”. And you can’t compare anything to “unknown” using =.

Think of it this way: if I ask you “is John’s age equal to Mary’s age?” and you don’t know either one, the answer isn’t “yes” or “no” — it’s “there’s no way to know”.

SQL treats it the same way. Any comparison to NULL using = returns neither true nor false: it returns NULL (unknown). And WHERE only lets through what is true. Result: nothing passes.

-- all of these return NULL, never TRUE:
phone = NULL
phone <> NULL
NULL = NULL

Yes — even NULL = NULL is “unknown”. Two unknown values are not necessarily equal.

The right way: IS NULL and IS NOT NULL

To test for the absence of a value, there’s a dedicated operator:

SELECT name
FROM customers
WHERE phone IS NULL

And to get whoever does have a phone:

SELECT name
FROM customers
WHERE phone IS NOT NULL

Where this really bites

This bug rarely breaks the query — it runs, gives no warning, and just returns a wrong number. In inactive-customer analysis, churn, or incomplete sign-ups, a forgotten = NULL makes you conclude “there are no cases” when in fact the query is silently wrong.

To remember

• = NULL and <> NULL never return true.
• Use IS NULL / IS NOT NULL to test for the absence of a value.
• Be suspicious whenever a query that “should return something” comes back empty.