NULL comparison with = or !=

Severity: Warning

Comparing a column to NULL using =, !=, or <> doesn’t do what you’d expect. In SQL’s three-valued logic, any comparison with NULL evaluates to unknown — not true or false. This means WHERE status = NULL matches zero rows, even when status is actually NULL.

Example

-- This never matches, even for rows where deleted_at IS NULL
SELECT * FROM users WHERE deleted_at = NULL;

-- This also never matches
SELECT * FROM users WHERE deleted_at != NULL;

Why it happens

SQL uses three-valued logic: true, false, and unknown. NULL represents an unknown or missing value. Since you can’t know whether an unknown value equals another unknown value, the result of any = or != comparison involving NULL is unknown. WHERE clauses only keep rows where the condition is true, so rows with unknown are filtered out — meaning both = NULL and != NULL return empty results.

How to fix it

Use IS NULL or IS NOT NULL:

-- Correct: find rows where deleted_at is null
SELECT * FROM users WHERE deleted_at IS NULL;

-- Correct: find rows where deleted_at is not null
SELECT * FROM users WHERE deleted_at IS NOT NULL;

For comparing two columns that might be null, use IS NOT DISTINCT FROM:

-- True when both are NULL, or both have the same non-null value
SELECT * FROM users WHERE old_email IS NOT DISTINCT FROM new_email;

Detection

Query Doctor flags this nudge when it finds an =, !=, or <> expression where either side is a NULL literal. The nudge marker points to the comparison expression in the formatted SQL view.