To find rows where a column is NULL, write WHERE column_name IS NULL; to exclude them, write WHERE column_name IS NOT NULL. A condition such as WHERE column_name = NULL does not match those rows because ordinary SQL comparisons with NULL evaluate to UNKNOWN, not TRUE.
Why does = NULL return no rows?
NULL represents an unknown or missing value, not an ordinary value that equality can compare. In PostgreSQL, an ordinary comparison returns NULL—meaning “unknown”—when either input is NULL. MySQL likewise explains that a comparison with NULL is not true. A WHERE clause selects rows only when its condition is true, so WHERE phone = NULL does not select rows with a NULL phone value. See the PostgreSQL comparison documentation and MySQL’s NULL guidance.
As an Amazon Associate I earn from qualifying purchases.
What should you write instead?
Find NULL values
SELECT *
FROM contacts
WHERE phone IS NULL;IS NULL tests whether the value is NULL and returns a definite true-or-false result.
Exclude NULL values
SELECT *
FROM contacts
WHERE phone IS NOT NULL;IS NOT NULL matches rows where the expression has a value.
#1 Best Overall
Test for an empty string separately
SELECT *
FROM contacts
WHERE phone = '';An empty string is not the same as NULL. MySQL explicitly distinguishes the two: use IS NULL for a NULL value and = '' for an empty string. This distinction matters when interpreting or cleaning data; a column can contain either, both, or neither. See MySQL’s examples.
Why can NULL change a larger condition?
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN, represented by NULL. Consequently, a condition containing NULL does not always behave like ordinary two-valued Boolean logic. For example, TRUE OR NULL is TRUE, while NOT NULL remains NULL; FALSE AND NULL is FALSE. A filter still selects only a condition that evaluates to TRUE. PostgreSQL documents these rules in its logical-operators reference.
When debugging a compound WHERE clause, work out the result of each comparison first. Replacing = NULL with IS NULL is appropriate when the intent is to test whether one expression is NULL; it is not a general rewrite for every expression involving NULL.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How do you compare two values when either might be NULL?
If the intent is to compare two expressions while treating two NULLs as equal, use a null-safe equality predicate supported by your database. PostgreSQL provides IS NOT DISTINCT FROM:
WHERE a IS NOT DISTINCT FROM bThis treats two NULL inputs as equal and treats one NULL and one non-NULL input as unequal. Its inverse, IS DISTINCT FROM, treats those two cases as distinct. These predicates answer a different question from a IS NULL: the latter tests one expression for nullness, while the former compares two expressions. PostgreSQL documents both in its comparison reference.
Does the syntax vary by database?
IS NULL and IS NOT NULL are the direct nullness tests shown in the cited PostgreSQL, MySQL, and SQLite documentation. For null-safe equality between two expressions, check the target database’s documentation rather than assuming shorthand is portable.
Quick Recap
- PostgreSQL: supports
IS DISTINCT FROMandIS NOT DISTINCT FROM. It also documents a compatibility setting,transform_null_equals, that can rewritex = NULLtox IS NULLwhen enabled. PostgreSQL recommends changing application queries to use the standard null test instead of relying on that setting. PostgreSQL comparison documentation. - SQLite: provides null-aware
ISandIS NOTcomparisons. Its documentation identifies the compact forms as SQLite extensions and points toIS [NOT] DISTINCT FROMforms used by other database engines. SQLite expression documentation. - MySQL: documents
IS NULLandIS NOT NULL; it also notes that anIS NULLsearch can use indexes and ranges where applicable. MySQL NULL guidance and MySQL IS NULL optimization.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →
