SQL Reference ยท Operators and Predicates
SQL IS DISTINCT FROM
Reference for IS DISTINCT FROM and IS NOT DISTINCT FROM: compare values where NULL equals NULL, with the SQLite IS operator and MySQL <=> as equivalents.
a IS DISTINCT FROM b is a comparison that treats NULL as an ordinary value. Two NULLs are considered not distinct (equal), and a NULL compared with any non-NULL value is distinct (different). The result is always true or false, never NULL.
IS NOT DISTINCT FROM is the NULL-safe version of =. It is the standard SQL answer to "did this column change?" when the column can be empty. Several databases offer it under other names, and SQLite does it with the plain IS operator.
SELECT 1 IS NOT DISTINCT FROM NULL; -- false SELECT NULL IS NOT DISTINCT FROM NULL; -- true
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
a IS [NOT] DISTINCT FROM b
| Expression | a, b both non-NULL |
One side NULL |
Both NULL |
|---|---|---|---|
a = b |
true or false | NULL |
NULL |
a IS NOT DISTINCT FROM b |
same as = |
false | true |
a IS DISTINCT FROM b |
same as <> |
true | false |
Returns: a boolean, never NULL.
Dialect differences
| Database | NULL-safe equal | NULL-safe not equal |
|---|---|---|
| Standard SQL | a IS NOT DISTINCT FROM b |
a IS DISTINCT FROM b |
| PostgreSQL | IS NOT DISTINCT FROM |
IS DISTINCT FROM |
| SQLite | a IS b (also IS NOT DISTINCT FROM since 3.39) |
a IS NOT b |
| MySQL | a <=> b |
NOT (a <=> b) |
| SQL Server | IS NOT DISTINCT FROM (SQL Server 2022 and later) |
IS DISTINCT FROM |
| Oracle | No operator in older releases: DECODE(a, b, 1, 0) = 1. Oracle 23ai adds IS [NOT] DISTINCT FROM. |
-- MySQL SELECT * FROM employees WHERE manager_id <=> NULL; -- Portable fallback for any database WHERE a = b OR (a IS NULL AND b IS NULL)
Usage notes
- SQLite's
ISis not just forNULL.a IS bworks for any two values, sox IS 5is allowed and means "equal, withNULLsafe". - Typical use: change detection.
WHERE old.price IS DISTINCT FROM new.pricefinds changed rows even when one side isNULL, which<>would miss. - Merging data and joins on nullable keys:
ON a.code IS NOT DISTINCT FROM b.codematchesNULLwithNULL. This can prevent the optimizer from using an index, so measure it. - Unique constraints follow a related idea in some databases: PostgreSQL 15 added
UNIQUE NULLS NOT DISTINCT. - For one fixed value,
IS NULLandIS NOT NULLare simpler: seeIS NULL. <>versusIS DISTINCT FROMin aWHERE: the first discards rows where either side isNULL; the second keeps them when exactly one side isNULL.