Skip to content
Tutorials Center
My learning

Topics

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.

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

Try it

SQL NULL-safe comparison with IS in SQLite

Press Run (or Ctrl + Enter) to see the result here.

SQL Find employees whose manager is not manager 2 ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
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.
SQL
-- 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 IS is not just for NULL. a IS b works for any two values, so x IS 5 is allowed and means "equal, with NULL safe".
  • Typical use: change detection. WHERE old.price IS DISTINCT FROM new.price finds changed rows even when one side is NULL, which <> would miss.
  • Merging data and joins on nullable keys: ON a.code IS NOT DISTINCT FROM b.code matches NULL with NULL. 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 NULL and IS NOT NULL are simpler: see IS NULL.
  • <> versus IS DISTINCT FROM in a WHERE: the first discards rows where either side is NULL; the second keeps them when exactly one side is NULL.