Why = NULL Never Matches in SQL

The symptom

You filter for missing values with where phone = null (orwhere status != 'shipped') and get back fewer rows than expected or none at all. No error, just a wrong answer.

Why it happens

In SQL, NULL means "unknown." Comparing anything to an unknown is itself unknown, so phone = null evaluates toNULL, not true. A WHERE clause only keeps rows that are true, so those rows fall out.

The same trap bites <> / !=: a row whose value is NULL is neither equal nor unequal to your target, so a plainstatus <> 'shipped' silently skips every null-status row. Writestatus is distinct from 'shipped' instead, which treatsNULL as just another value and keeps those rows.

The fix

Use IS NULL and IS NOT NULL, which test for the unknown itself instead of comparing against it. Some Garden Shop customers have no phone number on file; run the pair below and see which version finds them.

select customer_id, first_name, phone
from customers
where phone = null

select customer_id, first_name, phone
from customers
where phone is null

Learn more

• Lesson 2.6 · Missing values with NULL
• Lesson 7.4 · Null-safe filtering
• Reference · Null handling