iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
To find SQL rows where a column is NULL, use IS NULL; to find rows where it has a value, use IS NOT NULL. A condition such as WHERE phone = NULL does not match NULL values because ordinary comparisons involving NULL evaluate to UNKNOWN, not TRUE.
Why does = NULL find no rows?
SQL treats NULL as an unknown or missing value, not as an ordinary value that can be compared with =. In PostgreSQL, ordinary comparison operators return NULL—meaning unknown—if either input is NULL. MySQL likewise explains that a comparison with NULL is not true. Since a WHERE clause selects rows only when its condition is true, WHERE phone = NULL does not select rows whose phone is NULL. (PostgreSQL comparison documentation; MySQL NULL documentation)
What should you write instead?
Find rows where a value is NULL
SELECT *
FROM contacts
WHERE phone IS NULL;IS NULL tests whether the value is NULL and gives the nullness check that = NULL cannot provide.
Find rows where a value is present
SELECT *
FROM contacts
WHERE phone IS NOT NULL;This selects rows whose phone value is not NULL. MySQL documents that searches using IS NULL can use indexes and ranges where applicable. (MySQL IS NULL optimization)
#1 Best Overall
NULL is not the same as an empty string
An empty string is a value; NULL represents an unknown or missing value. To find empty strings, use = '', separately from the NULL test:
SELECT *
FROM contacts
WHERE phone = '';MySQL explicitly distinguishes NULL from ''. (MySQL NULL documentation)
How NULL changes compound conditions
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN (represented by NULL). That means a condition involving NULL does not always behave like ordinary two-valued Boolean logic. PostgreSQL documents, for example, that TRUE AND NULL evaluates to NULL, TRUE OR NULL evaluates to TRUE, FALSE AND NULL evaluates to FALSE, and NOT NULL remains NULL. (PostgreSQL logical operators)
Free tools Windows power users keep installed
One-click scans. No signup required.
When debugging a compound WHERE condition, evaluate the condition containing NULL and then how it combines with the other terms. If the final result is UNKNOWN rather than TRUE, the row is not selected.
How do you compare two values when either may be NULL?
Use a null-safe equality predicate when your intent is to treat two NULLs as equal. PostgreSQL supports IS NOT DISTINCT FROM: it treats two NULL inputs as equal, and a NULL paired with a non-NULL value as unequal.
WHERE a IS NOT DISTINCT FROM bIts inverse, IS DISTINCT FROM, treats a NULL and a non-NULL value as distinct. These predicates answer a different question from IS NULL: the latter tests one expression for nullness, while the former compares two expressions with defined behavior for NULL. (PostgreSQL comparison documentation)
Check the syntax for your database
For a simple NULL check, IS NULL and IS NOT NULL are the direct forms documented by PostgreSQL and MySQL. Null-safe comparisons between two expressions vary by database. PostgreSQL provides IS [NOT] DISTINCT FROM. SQLite supports null-aware IS and IS NOT comparisons; its compact forms are SQLite-specific extensions, and its documentation points to IS [NOT] DISTINCT FROM forms used by other database engines. Confirm the syntax in your target database’s documentation before using a null-safe equality expression across systems. (SQLite expression documentation)
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →PostgreSQL also has a transform_null_equals compatibility setting that can rewrite x = NULL to x IS NULL when enabled. PostgreSQL recommends correcting applications to use standard null-test behavior instead; do not rely on this setting as a general fix. (PostgreSQL comparison documentation)
Quick Recap
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.
