NULL and IS DISTINCT FROM: NULL-Safe Equality in SQL

In SQL, NULL does not mean "empty" or "zero" but "value unknown." That single fact makes ordinary comparisons behave in surprising ways, and it is exactly where IS DISTINCT FROM earns its keep. Let's see why = NULL is never true and how to write comparisons that survive NULL.

Three-valued logic and why = NULL fails

Most SQL engines use three-valued logic: an expression can be TRUE, FALSE, or UNKNOWN. Any comparison that touches NULL evaluates to UNKNOWN, and rows that are UNKNOWN in a WHERE clause are simply dropped.

SELECT NULL = NULL;      -- NULL (not TRUE)
SELECT NULL <> 1;        -- NULL
SELECT NULL = 1;         -- NULL

That is why this filter always returns zero rows even though it reads like it should work:

-- Wrong: never returns rows, even if country really is NULL
SELECT id, email
FROM users
WHERE country = NULL;

Key rules:

• NULL = NULL is UNKNOWN, not TRUE.
• WHERE keeps only rows that evaluate to TRUE.
• Arithmetic with NULL is also NULL: amount + NULL is NULL.

IS NULL and IS NOT NULL

To test specifically for "absence of a value," use the dedicated predicates IS NULL and IS NOT NULL. They always return TRUE or FALSE, never the third state.

-- Correct way to find users without a country
SELECT id, email
FROM users
WHERE country IS NULL;

-- Users that have a country set
SELECT id, email
FROM users
WHERE country IS NOT NULL;

Watch out for NOT IN against a subquery: if even one value in the list is NULL, the whole NOT IN collapses to UNKNOWN and you get no rows back.

-- Gotcha: if any manager_id is NULL, this returns nothing
SELECT id, name
FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

Prefer NOT EXISTS, or explicitly filter out NULL inside the subquery.

IS DISTINCT FROM: NULL-safe equality

IS DISTINCT FROM compares two values as if NULL were an ordinary value. NULL IS DISTINCT FROM NULL is FALSE (they "do not differ"), while NULL IS DISTINCT FROM 1 is TRUE. The mirror operator, IS NOT DISTINCT FROM, is NULL-safe equality.

SELECT NULL IS DISTINCT FROM NULL;      -- false
SELECT NULL IS DISTINCT FROM 1;         -- true
SELECT 1 IS NOT DISTINCT FROM 1;        -- true
SELECT NULL IS NOT DISTINCT FROM NULL;  -- true

Compare the behavior of = and IS NOT DISTINCT FROM side by side:

• a = b: TRUE for equal non-NULLs, FALSE otherwise, but UNKNOWN whenever NULL is involved.
• a IS NOT DISTINCT FROM b: behaves like = but treats NULL as equal to NULL and always returns TRUE/FALSE.

In practice: change detection and dedup

A classic task: during a load, update a row only when something actually changed. With plain <>, a row whose name went from NULL to a real value (or vice versa) is not flagged as changed.

-- Detect real changes, NULL-safe
SELECT u.id
FROM users AS u
JOIN staging_users AS s ON s.id = u.id
WHERE u.name    IS DISTINCT FROM s.name
   OR u.country IS DISTINCT FROM s.country
   OR u.email   IS DISTINCT FROM s.email;

The same applies to deduplication when the key can hold NULL:

-- Two rows are duplicates even when country is NULL in both
SELECT a.id, b.id
FROM users AS a
JOIN users AS b
  ON a.email IS NOT DISTINCT FROM b.email
 AND a.country IS NOT DISTINCT FROM b.country
WHERE a.id < b.id;

Engine differences:

• PostgreSQL fully supports IS DISTINCT FROM and IS NOT DISTINCT FROM.
• MySQL/MariaDB use the <=> operator (NULL-safe equality) instead: a <=> b.
• ClickHouse offers isNull and friends; for NULL-aware comparison a coalesce-based expression is common, and exact support varies by version.

Bottom line: use IS NULL/IS NOT NULL to test for presence, and reach for IS DISTINCT FROM whenever you compare columns where NULL is a legitimate state.