SQL NULL comparisons: why `= NULL` never matches

NULL represents an unknown or missing value. SQL comparisons with an unknown yield UNKNOWN, not TRUE or FALSE. A WHERE clause keeps only TRUE, so column = NULL returns no rows.

Reproduce and fix it

WITH users(name, nickname) AS (
  VALUES ('Ada', 'ace'), ('Grace', NULL)
)
SELECT name
FROM users
WHERE nickname IS NULL;

Expected output: Grace. Use IS NOT NULL for the opposite check.

Equality when both nullable values should match

PostgreSQL supports a IS NOT DISTINCT FROM b, which treats two nulls as equal. A portable alternative is explicit:

WITH pairs(label, a, b) AS (
  VALUES ('same', 1, 1), ('both-null', NULL, NULL), ('different', 1, 2)
)
SELECT label
FROM pairs
WHERE a = b OR (a IS NULL AND b IS NULL)
ORDER BY label;

Do not replace nulls with an arbitrary sentinel through COALESCE unless that sentinel is impossible in the real data.

The `NOT IN` trap

SELECT (3 NOT IN (1, 2, NULL)) IS NULL;

This is UNKNOWN, not TRUE, because SQL cannot prove that the unknown value is different from 3. For anti-joins, prefer correlated NOT EXISTS:

WITH products(id) AS (VALUES (1), (2), (3)),
retired(product_id) AS (VALUES (2))
SELECT p.id
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1 FROM retired AS r WHERE r.product_id = p.id
)
ORDER BY p.id;

Aggregates and null

COUNT(column) ignores nulls; COUNT(*) counts rows. Most aggregates ignore null input, while COALESCE(SUM(amount), 0) is useful when an empty result should mean zero in your domain.

Failure mode

Making every nullable field an empty string or zero hides missingness and creates new ambiguity. Model absence deliberately and test filters with both null and non-null fixtures.

Try the examples in the SQL playground, then study NULL Handling and Filtering.