gotchasqlpostgresqlMajor
NULL comparisons: why = NULL always returns NULL, not false
Viewed 0 times
NULL comparisonIS NULLIS NOT NULLthree-valued logicNOT IN NULLCOALESCEIS NOT DISTINCT FROM
Problem
WHERE column = NULL returns no rows even when NULL values exist in the column. Developers write = NULL expecting it to filter for nulls, but this is a silent logic error.
Solution
Use IS NULL and IS NOT NULL for null comparisons:
-- WRONG (always returns 0 rows):
SELECT * FROM users WHERE deleted_at = NULL;
SELECT * FROM users WHERE deleted_at != NULL;
-- CORRECT:
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;
-- NULL-safe equality (returns true when both sides are NULL):
SELECT * FROM users WHERE deleted_at IS NOT DISTINCT FROM NULL;
-- COALESCE for null substitution:
SELECT coalesce(nickname, first_name, 'Anonymous') FROM users;
-- NULL in aggregations:
SELECT count(*) AS total, count(deleted_at) AS deleted FROM users;
-- count(*) counts all rows; count(col) skips NULLs
-- WRONG (always returns 0 rows):
SELECT * FROM users WHERE deleted_at = NULL;
SELECT * FROM users WHERE deleted_at != NULL;
-- CORRECT:
SELECT * FROM users WHERE deleted_at IS NULL;
SELECT * FROM users WHERE deleted_at IS NOT NULL;
-- NULL-safe equality (returns true when both sides are NULL):
SELECT * FROM users WHERE deleted_at IS NOT DISTINCT FROM NULL;
-- COALESCE for null substitution:
SELECT coalesce(nickname, first_name, 'Anonymous') FROM users;
-- NULL in aggregations:
SELECT count(*) AS total, count(deleted_at) AS deleted FROM users;
-- count(*) counts all rows; count(col) skips NULLs
Why
SQL uses three-valued logic: TRUE, FALSE, and UNKNOWN. Any comparison with NULL produces UNKNOWN, including NULL = NULL. WHERE clauses only pass rows where the condition is TRUE, so UNKNOWN rows are filtered out silently.
Gotchas
- NOT IN with a subquery that returns any NULL silently returns no rows due to null propagation
- GROUP BY treats NULL as a distinct group value, unlike equality comparison
- Unique indexes in PostgreSQL allow multiple NULL values (NULL IS NOT DISTINCT FROM NULL is false for unique constraint purposes)
- NULLIF(a, b) returns NULL when a = b, useful for division-by-zero guards
Code Snippets
NOT IN vs NOT EXISTS with NULL safety
-- NOT IN with NULL in subquery returns nothing:
SELECT * FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers WHERE deleted_at IS NULL);
-- If any customer has id=NULL (impossible with PK, but illustrative), zero rows return.
-- Safe alternative:
SELECT * FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM customers c
WHERE c.id = o.customer_id AND c.deleted_at IS NULL
);Context
Any query filtering, joining, or comparing nullable columns
Revisions (0)
No revisions yet.