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…
In SQL, using the condition "WHERE phone = NULL" to find customers without a phone number does not work as expected. This is because NULL does not represent "nothing" but rather "unknown". In SQL, comparing a value to NULL with the equality operator (=) always results in UNKNOWN, which is neither TRUE nor FALSE. Consequently, the WHERE clause filters out all rows, returning zero results even when there are matching records.
To correctly query for customers with no phone number, replace the equality operator with the IS NULL operator. For example, change "WHERE phone = NULL" to "WHERE phone IS NULL". This dedicated operator is specifically designed to test for the presence of NULL values. The same principle applies to other conditions using NULL. For instance, "phone != NULL" also returns no results because != is a comparison operator, and comparing anything to NULL results in UNKNOWN.
To effectively debug queries that return fewer rows than expected, first run a plain SELECT * FROM table without any WHERE clause to identify any NULL values in the relevant columns. Then, modify the query to use IS NULL (or IS NOT NULL) instead of = (or !=) for NULL checks. Remember, a column defined as NOT NULL enforces that it must always have a value, so if you suspect missing data, consider enforcing this constraint.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
