NULL in SQL: why = NULL never matches and how to handle missing values

NULL is not zero and not an empty string. It is a marker for “no value” or “unknown”. SQL follows a simple rule that causes many bugs: almost any operation involving an unknown value produces another unknown value.

Three-valued logic

A condition in SQL can be true, false or unknown. WHERE keeps only rows where the condition is true, so unknown rows are dropped silently.

SELECT NULL = NULL;    -- NULL (unknown), not true
SELECT NULL <> 1;      -- NULL
SELECT NULL + 1;       -- NULL
SELECT 'x' || NULL;    -- NULL in standard SQL, SQLite and PostgreSQL

MySQL uses CONCAT() for strings, and CONCAT('x', NULL) is also NULL. CONCAT_WS skips NULL arguments.

Test with IS NULL

SELECT name FROM customers WHERE email = NULL;      -- always returns nothing
SELECT name FROM customers WHERE email IS NULL;     -- correct
SELECT name FROM customers WHERE email IS NOT NULL;

Rows vanish from negative conditions

In the sample data, Dana’s city is NULL. This query looks like it returns everyone outside London, but it returns only Brian:

SELECT name FROM customers WHERE city <> 'London';

-- Include the unknowns explicitly:
SELECT name FROM customers WHERE city <> 'London' OR city IS NULL;

PostgreSQL and SQLite also have a NULL-safe comparison: city IS DISTINCT FROM 'London' in PostgreSQL, and city IS NOT 'London' in SQLite. MySQL uses the <=> operator: NOT (city <=> 'London').

The NOT IN trap

If a subquery used with NOT IN returns a NULL, no row can ever satisfy the condition, and the query returns an empty result. Use NOT EXISTS, or filter the NULLs out of the subquery:

SELECT name FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Replacing NULL with a default

SELECT name, COALESCE(city, 'unknown') AS city FROM customers;
SELECT 100 / NULLIF(0, 0);   -- NULLIF turns 0 into NULL: no division error

COALESCE returns its first non-NULL argument and works everywhere. IFNULL (MySQL, SQLite) and ISNULL (SQL Server) are two-argument variants. Note that MySQL’s ISNULL(x) only tests a value and does not replace it.

Aggregates ignore NULL

SELECT COUNT(*)     AS all_rows,     -- 4: counts rows
       COUNT(email) AS with_email    -- 3: counts non-NULL values
FROM customers;

• SUM, AVG, MIN and MAX skip NULLs. The average of 10, NULL and 20 is 15, not 10.
• SUM over zero rows, or only NULLs, returns NULL, not 0. Wrap it as COALESCE(SUM(amount), 0) when you need a number.
• If a NULL should count as zero, say so: AVG(COALESCE(score, 0)).

Grouping, sorting and uniqueness

• GROUP BY and DISTINCT put all NULLs into one group, even though NULL is not equal to NULL.
• Sort position differs: SQLite and MySQL put NULLs first in ascending order, PostgreSQL puts them last. PostgreSQL and SQLite (3.30+) accept ORDER BY city NULLS LAST; in MySQL, sort on city IS NULL first.
• A UNIQUE column usually allows many NULL rows. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to forbid that.

Prevent NULLs you don't want

The simplest fix is in the schema. Declare columns NOT NULL and give them a DEFAULT when a value must always exist. Then you only have to think about NULL where it really means “unknown”.