IS DISTINCT FROM: Comparing NULLs Without the Tricks

A synchronisation job compares a staging table against a target table and updates the rows that differ. It has worked for years. Then somebody makes the phone number column nullable, and from that day on changes to phone numbers silently stop being applied. No error, no warning, no failed job. The comparison simply stops returning those rows.

The cause is not a bug in the job. It is the rule that any comparison involving NULL produces UNKNOWN, and UNKNOWN is not TRUE, so the WHERE clause filters the row out. Every DBA knows this rule. Almost every codebase has at least one place where it was forgotten anyway.

Why the Equals Sign Cannot Compare NULLs

SQL uses three-valued logic. A predicate is TRUE, FALSE or UNKNOWN, and a WHERE clause only keeps rows where the predicate is TRUE. NULL does not mean "empty", it means "unknown", and asking whether two unknowns are equal has no sensible answer. So SQL Server returns the only honest one: unknown.

Here is the failure in its smallest reproducible form. Two tables, three rows, one changed email address each in rows 2 and 3, where one side is NULL:

CREATE TABLE dbo.Tgt (id INT PRIMARY KEY, name VARCHAR(20) NULL,
                      email VARCHAR(40) NULL, phone VARCHAR(20) NULL);
CREATE TABLE dbo.Src (id INT PRIMARY KEY, name VARCHAR(20) NULL,
                      email VARCHAR(40) NULL, phone VARCHAR(20) NULL);

INSERT dbo.Tgt VALUES (1,'Ann','ann@x.de',NULL), (2,'Bob',NULL,'0301'), (3,'Cy','cy@x.de','0302');
INSERT dbo.Src VALUES (1,'Ann','ann@x.de',NULL), (2,'Bob','bob@x.de','0301'), (3,'Cy',NULL,'0302');

-- the comparison almost everybody writes first
SELECT t.id
FROM dbo.Tgt t JOIN dbo.Src s ON s.id = t.id
WHERE t.name <> s.name OR t.email <> s.email OR t.phone <> s.phone;

id
--
(0 rows)

Zero rows. Bob got an email address, Cy lost his, and the query that exists specifically to notice such things reports that nothing happened. Swap the operator and the same query does its job:

SELECT t.id
FROM dbo.Tgt t JOIN dbo.Src s ON s.id = t.id
WHERE t.name  IS DISTINCT FROM s.name
   OR t.email IS DISTINCT FROM s.email
   OR t.phone IS DISTINCT FROM s.phone;

id
--
2
3

What IS DISTINCT FROM Actually Means

Read it as "is a different value from", where NULL counts as a value in its own right. Two expressions are distinct when they hold different values, and one of those values may be "no value at all". The operator answers with TRUE or FALSE and never with UNKNOWN.

• a IS DISTINCT FROM b is the null-safe replacement for a <> b
• a IS NOT DISTINCT FROM b is the null-safe replacement for a = b

Two details decide whether you can use it today:

• Version: SQL Server 2022 and later, Azure SQL Database, Azure SQL Managed Instance and Fabric SQL. On SQL Server 2019 and earlier it is a syntax error, and there is no trace flag or setting that brings it back.
• Compatibility level: not relevant. Tested on SQL Server 2022 against a database set to compatibility level 140, the operator compiles and runs normally. The engine version is what counts, not the database setting.

The Three Workarounds and What They Cost

Before 2022 there were three established ways to compare two possibly-null values, and all three still work. They differ sharply in how much they cost the optimizer. The test table below holds 100,000 rows with a nonclustered index on code, of which 100 rows are NULL:

CREATE TABLE dbo.Sarg (id INT IDENTITY PRIMARY KEY, code INT NULL,
                       filler CHAR(100) NOT NULL DEFAULT 'x');
-- 100,000 rows, code = row number modulo 1000, every 997th row NULL
CREATE NONCLUSTERED INDEX IX_Sarg_code ON dbo.Sarg (code);

DECLARE @p INT = 500;

SELECT COUNT(*) FROM dbo.Sarg WHERE code = @p;                                -- baseline
SELECT COUNT(*) FROM dbo.Sarg WHERE code IS NOT DISTINCT FROM @p;             -- 2022+
SELECT COUNT(*) FROM dbo.Sarg WHERE (code = @p OR (code IS NULL AND @p IS NULL));
SELECT COUNT(*) FROM dbo.Sarg WHERE EXISTS (SELECT code INTERSECT SELECT @p);
SELECT COUNT(*) FROM dbo.Sarg WHERE ISNULL(code, -1) = ISNULL(@p, -1);

All five return the same 100 rows. The execution plans do not agree at all:

Two results deserve comment, because they contradict things people repeat about this topic.

First, the verbose OR form is not slow. The optimizer recognises the pattern and still produces a single index seek. It is long, it is easy to get wrong when six columns are involved, and it makes a MERGE statement unreadable, but it does not cost performance. Replace it for clarity, not for speed.

Second, the ISNULL sentinel form is the expensive one, and it was always the most popular. It loses the index because the column sits inside a function, so the engine has to compute ISNULL(code, -1) for all 100,000 rows before it can compare anything. That is the same rule described in Never Put Functions in WHERE Clauses, and it is the strongest practical argument for the new operator.

ISNULL and COALESCE Are Not Comparison Operators

This is where the confusion usually starts. ISNULL and COALESCE do not compare anything. They replace a NULL with something else. Using them to compare two values is a workaround built from the wrong tool, which is why it drags the sentinel problem and the index problem along with it.

They are also not interchangeable with each other, and the differences are larger than most code assumes.

Difference 1: the data type of the result

ISNULL returns the data type of its first argument. COALESCE returns the data type with the highest precedence across all arguments. When the first argument is narrower than the replacement, ISNULL truncates without a word:

SELECT ISNULL  (CAST(NULL AS VARCHAR(2)), 'abcdef') AS isnull_result,
       COALESCE(CAST(NULL AS VARCHAR(2)), 'abcdef') AS coalesce_result;

isnull_result|coalesce_result
-------------|---------------
ab           |abcdef

-- the declared types behind those two values
isnull_type|isnull_len|coal_type|coal_len
-----------|----------|---------|--------
varchar    |2         |varchar  |6

No error, no warning, just ab. In a report column or a generated key this is the kind of defect that survives testing and shows up in production as "the description looks cut off".

Difference 2: nullability of the result

The result of ISNULL can be NOT NULL, because the optimizer knows the second argument replaces every NULL. The result of COALESCE is always treated as nullable. This is invisible in a SELECT list and decisive in a computed column:

CREATE TABLE dbo.NullTest (
    id       INT NULL,
    c_isnull AS ISNULL(id, 0),
    c_coal   AS COALESCE(id, 0)
);

col     |typ|is_nullable
--------|---|-----------
id      |int|1
c_isnull|int|0        <-- NOT NULL
c_coal  |int|1        <-- nullable

That single bit decides what you can build on the column. With both computed columns marked PERSISTED and a primary key attempted on each:

PERSISTED ISNULL column  : PK created
PERSISTED COALESCE column: Cannot define PRIMARY KEY constraint on nullable column
                           in table 'CoalOnly'.

Same logic, same result values, and only one of them can carry a key.

Difference 3: how often the first argument is evaluated

COALESCE is not a function in the engine. It is expanded into a CASE expression, and the first argument appears twice in that expansion. ISNULL is a genuine built-in and is evaluated once. The plan shows it directly:

SELECT ISNULL((SELECT val FROM dbo.Big WHERE id = 999), -1);

  |--Compute Scalar(DEFINE:([Expr1003]=isnull([dbo].[Big].[val],(-1))))
       |--Nested Loops(Left Outer Join)
            |--Constant Scan
            |--Clustered Index Seek(OBJECT:([dbo].[Big].[PK__Big...]), SEEK:([id]=(999)))


SELECT COALESCE((SELECT val FROM dbo.Big WHERE id = 999), -1);

  |--Compute Scalar(DEFINE:([Expr1006]=CASE WHEN [val] IS NOT NULL THEN [val] ELSE (-1) END))
       |--Nested Loops(Left Outer Join, PASSTHRU:(IsFalseOrNull [val] IS NOT NULL))
            |--Nested Loops(Left Outer Join)
            |    |--Constant Scan
            |    |--Clustered Index Seek(OBJECT:([dbo].[Big].[PK__Big...]), SEEK:([id]=(999)))
            |--Clustered Index Seek(OBJECT:([dbo].[Big].[PK__Big...]), SEEK:([id]=(999)))

Two seeks instead of one, and the CASE rewrite is right there in the Compute Scalar. With an aggregate subquery the optimizer often adds a spool and the second access costs nothing measurable, so this is not a reliable disaster. It is a reliable trap: put an expensive subquery or a non-deterministic expression in the first argument of a COALESCE and you have no guarantee it runs once.

The full comparison

The last row is the point of this article. Whichever of the two you prefer, neither is the right tool for asking whether two values differ.

Three Questions That Look the Same and Are Not

Most of the confusion around NULL handling comes from mixing up three genuinely different questions. Once they are separated, the choice of tool is obvious.

That last point is worth spelling out in code, because it is a real regression waiting in any "optional search parameter" procedure:

-- optional filter: a NULL parameter means "do not filter at all"
WHERE (@p IS NULL OR code = @p)          -- @p NULL  -> all 100,000 rows

-- null-safe comparison: a NULL parameter means "find the rows that are NULL"
WHERE code IS NOT DISTINCT FROM @p       -- @p NULL  -> the 100 NULL rows

Both are correct. They answer different questions, and swapping one for the other changes a search screen from "show everything" to "show almost nothing" without touching a single test case that passes a real value.

Where It Fits in Real Code

Change detection in a MERGE or UPDATE

This is the case that pays for itself immediately. Compare the two versions of the same condition across four nullable columns:

-- before
WHERE  (t.name  <> s.name  OR (t.name  IS NULL AND s.name  IS NOT NULL)
                            OR (t.name  IS NOT NULL AND s.name  IS NULL))
   OR  (t.email <> s.email OR (t.email IS NULL AND s.email IS NOT NULL)
                            OR (t.email IS NOT NULL AND s.email IS NULL))
   -- and two more of these

-- after
WHERE  t.name  IS DISTINCT FROM s.name
   OR  t.email IS DISTINCT FROM s.email
   OR  t.phone IS DISTINCT FROM s.phone
   OR  t.city  IS DISTINCT FROM s.city

In a MERGE, that condition belongs in the WHEN MATCHED AND ... clause so that unchanged rows are not rewritten. See T-SQL MERGE: The Synchronization Statement That Needs Caution for the rest of the MERGE caveats, and MERGE for Slowly Changing Dimensions for the same pattern inside a dimension load, where nullable attributes are the norm rather than the exception.

Joining on a nullable key

SELECT t.id
FROM dbo.Tgt t
JOIN dbo.Src s
  ON  s.id    = t.id
  AND s.email IS NOT DISTINCT FROM t.email;   -- matches NULL to NULL

Constraints and computed columns

The operator is allowed anywhere a predicate is allowed, including CHECK constraints and computed columns, both verified on SQL Server 2022:

-- a and b may never hold the same value, and two NULLs count as the same value
CREATE TABLE dbo.ChkTest (
    a INT NULL,
    b INT NULL,
    CONSTRAINT CK_ab CHECK (a IS DISTINCT FROM b)
);

CREATE TABLE dbo.CompTest (
    a       INT NULL,
    b       INT NULL,
    changed AS CAST(CASE WHEN a IS DISTINCT FROM b THEN 1 ELSE 0 END AS BIT)
);

In a constraint the difference is bigger than it looks, because a CHECK constraint accepts a row when its predicate is TRUE or UNKNOWN. It only rejects an outright FALSE. That gives the classic CHECK (a <> b) a hole exactly where you thought the rule applied:

Two NULLs slip straight through the old constraint. If your rule is "these two columns must never hold the same value", the <> version has been letting one case through for as long as the table has existed.

Limits

• The legacy large object types are out. text, ntext and image are rejected with Msg 402, The data types text and text are incompatible in the is not operator. The modern types are fine: varchar(max) and xml both work.
• There is no downgrade path. Code using the operator will not deploy to SQL Server 2019 or earlier. If the same scripts have to run on both, stay with the OR form, which is correct everywhere and, as measured above, costs nothing.
• It does not make NULL meaningful. A column where NULL means four different things in four different rows is still a modelling problem. The operator makes comparing them predictable, not correct. See Unique Indexes on Nullable Columns for another place where "unknown" and "no value" get quietly conflated.

Q&A

The Bottom Line

Three-valued logic is not going away, and = will never match two NULLs. What changed in SQL Server 2022 is that you no longer have to choose between a comparison that is verbose, one that is unreadable and one that is both slow and subtly wrong.

IS DISTINCT FROM and IS NOT DISTINCT FROM say what you mean, always return a definite answer, and still let the optimizer seek. ISNULL and COALESCE stay where they belong: replacing missing values, not comparing them, and not interchangeable with each other when the result feeds a computed column, a key or an expensive subquery.