The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →WHERE x = NULL does not find rows whose x value is NULL. Comparisons involving NULL do not evaluate to true, so a WHERE clause will not select those rows. Use WHERE x IS NULL to find them, and WHERE x IS NOT NULL to find rows with a known value.
Why = NULL does not match rows
Equality works for values SQL can compare, such as status = 'active'. NULL represents an unknown or missing value, so comparing another value with NULL cannot establish that the two are equal. In SQL Server, such a comparison produces UNKNOWN; MySQL describes the result as NULL. Neither is true. A WHERE clause selects rows only when its condition is true, which is why WHERE x = NULL does not return rows even when x is NULL.
As an Amazon Associate I earn from qualifying purchases.
Oracle’s MySQL 26.7 Reference Manual explicitly says an expr = NULL test cannot be used to search for NULL column values. Microsoft Learn likewise instructs SQL Server users to use IS NULL or IS NOT NULL, rather than comparison operators, to test for NULL.
Use IS NULL or IS NOT NULL
Write the predicate that matches the question you want to ask:
#1 Best Overall
-- Find rows where x has no known value
SELECT *
FROM your_table
WHERE x IS NULL;
-- Find rows where x has a known value
SELECT *
FROM your_table
WHERE x IS NOT NULL;These are the documented nullness tests in MySQL and SQL Server. SQLite’s language reference also documents NULL-related expression behavior; the syntax above is the direct way to express the null and non-null checks discussed here.
Why <> NULL is not the fix
Changing the operator does not solve the problem. WHERE x <> NULL is also a comparison with NULL, so it does not select rows as a test for “not null.” Use WHERE x IS NOT NULL instead. MySQL’s manual specifically contrasts death IS NOT NULL with death <> NULL.
NULL is not an empty string or zero
NULL is distinct from ordinary values such as an empty string ('') or zero (0). A blank phone-number string is still a value; zero is still a number. Neither means that the value is unknown or missing. MySQL’s examples distinguish these values and show that 0 IS NULL and '' IS NULL are false.
If you want to find empty strings, compare against the empty string explicitly; if you want to find zero, compare against zero. Use IS NULL only when you mean a NULL value.
What to remember across SQL dialects
The official documentation reviewed for MySQL and SQL Server directs users to IS NULL and IS NOT NULL for nullness checks. The cited SQLite reference covers NULL behavior in expressions. Specialized null-safe comparison operators, configuration modes, and behavior across every database product are outside that scope, so check the current documentation for your specific engine before relying on dialect-specific features.
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.
