Recipe

NULL comparison: why = NULL never works

Short answer: col = NULL doesn't work — NULL is not a value, it's the absence of a value, and any comparison against NULL returns NULL (which is neither true nor false). Use col IS NULL and col IS NOT NULL. The same logic applies to col <> NULL, col != NULL, and NOT (col = NULL) — none of them work the way you'd want.

The wrong way and the right way #

sql

-- WRONG — every WHERE clause below returns no rows.
SELECT * FROM users WHERE email = NULL;
SELECT * FROM users WHERE email <> NULL;
SELECT * FROM users WHERE NOT email = NULL;

-- RIGHT — IS NULL / IS NOT NULL.
SELECT * FROM users WHERE email IS NULL;
SELECT * FROM users WHERE email IS NOT NULL;

Example #

Loading SQL editor...

Three-valued logic #

SQL is three-valued: TRUE, FALSE, and UNKNOWN (NULL). The rules:

NULL = NULL → NULL (not TRUE)
NULL <> 5 → NULL (not TRUE)
NULL AND TRUE → NULL
NULL OR TRUE → TRUE (the only operand that can short-circuit)
NULL OR FALSE → NULL
NOT NULL → NULL

WHERE clauses include only rows where the predicate evaluates to TRUE. UNKNOWN rows are filtered out — that's why = NULL returns no rows.

Common patterns #

sql

-- "Not equal, including nulls."
WHERE col IS NULL OR col <> 'x';

-- IS DISTINCT FROM — the null-safe inequality (Postgres / DuckDB / SQL Server / Snowflake).
WHERE col IS DISTINCT FROM 'x';            -- handles NULL correctly: NULL is "distinct from" 'x'

-- Equality treating NULL = NULL as TRUE.
WHERE col IS NOT DISTINCT FROM 'x';

-- MySQL "null-safe equality" operator.
WHERE col <=> 'x';                          -- TRUE if both NULL or both equal

-- Find rows where two columns disagree (including NULLs).
WHERE col_a IS DISTINCT FROM col_b;

-- "Has any value" — useful in CASE.
CASE WHEN col IS NULL THEN 'unknown' ELSE col END
COALESCE(col, 'unknown')                    -- equivalent

Dialect comparison #

Engine	Syntax	Gotcha
PostgreSQL / DuckDB	`a IS DISTINCT FROM b`	—
MySQL	`a <=> b`	null-safe equality — negate it to get distinctness; no `IS DISTINCT FROM`
SQL Server	`a IS DISTINCT FROM b`	2022+ only; older: `EXISTS (SELECT a INTERSECT SELECT b)`
BigQuery	`a IS DISTINCT FROM b`	—
Snowflake	`a IS DISTINCT FROM b`	`EQUAL_NULL(a, b)` is the function form
Oracle	`DECODE(a, b, 1, 0) = 1`	`DECODE` treats two NULLs as equal — the classic workaround
SQLite	`a IS b`	`IS` / `IS NOT` work as null-safe equality on any values, not just NULL literals

Dialect notes #

IS DISTINCT FROM: ANSI standard, supported on Postgres, DuckDB, SQL Server, Snowflake, BigQuery.
<=>: MySQL / SQLite null-safe equality operator. Returns TRUE when both sides are NULL. Same semantics as IS NOT DISTINCT FROM.
Some engines have an "ANSI_NULLS" setting (notably SQL Server). When OFF, = NULL does work — but this is deprecated and shouldn't be relied on. Always use IS NULL.
Aggregates ignore nulls regardless of three-valued logic — that's a separate behavior. See Ignore NULLs in aggregates.
IN / NOT IN with NULL subqueries: x NOT IN (subquery) returns no rows if the subquery contains a single NULL. Use NOT EXISTS instead.

Warning

WHERE col <> 'x' quietly excludes rows where col IS NULL. Most analyses end up wanting "everything except 'x', including the unknowns" — write WHERE col IS NULL OR col <> 'x' (or use IS DISTINCT FROM). The <> form excluding nulls is the source of "where did 5% of my rows go?" debugging sessions. Make the null intent explicit. Detail: IS NULL.