IS DISTINCT FROM in SQL Server 2022: NULL-Safe Comparisons

Two nullable columns can hide a real change from ordinary inequality. IS DISTINCT FROM gives SQL Server 2022 a direct way to compare them without losing NULL transitions. That matters when a synchronization query decides which rows to update.

See What IS DISTINCT FROM Catches

Try a tiny table before changing a large synchronization procedure. The query with <> reports the pair 10 and 20, but ignores NULL versus 20 and 10 versus NULL. It also ignores NULL versus NULL, which is the one case that should remain unchanged. SQL's three-valued logic is working as designed; the change detector asked an incomplete question.

CREATE TABLE #changes (old_value int NULL, new_value int NULL);
INSERT #changes VALUES (10,20),(NULL,20),(10,NULL),(NULL,NULL),(10,10);
SELECT old_value,new_value FROM #changes
WHERE old_value <> new_value;
SELECT old_value,new_value FROM #changes
WHERE old_value IS DISTINCT FROM new_value;

Run the remaining comparison snippets in the same query session, where #changes remains available. The second query returns three changed pairs. I use this five-row matrix whenever a predicate will drive an UPDATE, DELETE, or MERGE. It is much easier to find a missing NULL branch here than after a production load skips customers. Which pair would your existing predicate miss? Write the expected result beside every test row.

Read IS DISTINCT FROM and IS NOT DISTINCT FROM

a IS DISTINCT FROM b is true when values differ, treating one NULL and one value as different. a IS NOT DISTINCT FROM b is true when values match, treating two NULLs as equal. These operators return true or false rather than UNKNOWN. They were added in SQL Server 2022, so check the target engine before deploying the syntax. A database compatibility setting does not make an older engine understand a new keyword.

SELECT old_value,new_value,
       CASE WHEN old_value IS DISTINCT FROM new_value
            THEN 1 ELSE 0 END AS changed,
       CASE WHEN old_value IS NOT DISTINCT FROM new_value
            THEN 1 ELSE 0 END AS same_value
FROM #changes;

The first condition is useful for change capture; the second is useful for matching nullable keys or parameters. Neither operator turns a questionable nullable business key into a sound key design. If rows can be identified by a stable non-null key, use that key to locate them and compare the nullable attributes separately.

Compare the Older Workarounds

Before SQL Server 2022, an explicit predicate expresses the same rule: values differ, or exactly one side is NULL. Parentheses matter because AND binds more tightly than OR. An ISNULL sentinel can look shorter, but it is unsafe if the sentinel is a valid value and it can put a function on an indexed column. Choose a sentinel only with a verified domain constraint and a reason to accept the plan shape.

SELECT old_value,new_value FROM #changes
WHERE old_value <> new_value
   OR (old_value IS NULL AND new_value IS NOT NULL)
   OR (old_value IS NOT NULL AND new_value IS NULL);
SELECT old_value,new_value FROM #changes
WHERE NOT EXISTS
(
    SELECT old_value INTERSECT SELECT new_value
);

INTERSECT treats NULL values as equal for set comparison, so the second pattern is a compact NULL-safe test. It can be expressive when comparing several columns, although its plan deserves inspection. I keep the explicit OR pattern in older code when a team needs a predicate that every maintainer can read without explaining set operators.

Put IS DISTINCT FROM in Change Detection

A common update loads a staging row, joins it to the current row by immutable ID, then updates only when one or more attributes changed. Place the NULL-safe test in the change condition, not in the identity join unless nullable identity is truly intended. For a multi-column record, join the comparisons with OR. A change from NULL to a value or back again will now be seen.

UPDATE t
   SET t.Phone = s.Phone,
       t.Email = s.Email
FROM dbo.Customer AS t
JOIN dbo.CustomerStage AS s ON s.CustomerID = t.CustomerID
WHERE t.Phone IS DISTINCT FROM s.Phone
   OR t.Email IS DISTINCT FROM s.Email;

This is a pattern; create those tables or substitute your real names before running it. On a three-row test it updated the two changed customers and skipped the one whose values, including a NULL phone, had not changed. Avoid a no-op update when no values changed because it still touches logging, indexes, triggers, and downstream capture. For MERGE, the same comparison can guard a WHEN MATCHED update, but review MERGE concurrency and correctness separately. A good predicate cannot repair an unsafe source or join.

Inspect Index Access Separately

NULL-safe semantics and seekability are different questions. Create an index on the searched column, turn on actual execution plan, and compare a selective equality lookup with each NULL-safe form. Seek Predicates, Predicate, Actual Rows Read, and logical reads show what the engine really did. An operator labeled Index Seek can still read a broad range and apply a residual comparison. The optimizer can transform one form differently from another on your version and data distribution.

CREATE INDEX IX_Customer_Phone ON dbo.Customer(Phone);
DECLARE @phone varchar(30) = '555-0100';
SELECT CustomerID FROM dbo.Customer
WHERE Phone IS NOT DISTINCT FROM @phone;
SELECT CustomerID FROM dbo.Customer
WHERE Phone = @phone OR (Phone IS NULL AND @phone IS NULL);

Use a real table and matching parameter type. If @phone is NULL, both queries have different selectivity than an ordinary phone lookup. A plan cached for one parameter can perform poorly for the other. I measure both cases and a representative non-null value before choosing a permanent form. Do not infer a seek from the syntax alone.

Test the Complete Matrix and Plan

Keep a regression test for NULL/NULL, NULL/value, value/NULL, same/value, and different/value. Check both the rows returned and the execution plan for a selective workload. If collation is relevant for strings, include case and accent examples too. The new operator uses the comparison rules of the operands, so a collation mismatch still needs a deliberate resolution.

When I replace an older sentinel expression, I count changed rows on a staging copy and reconcile each difference. A higher count can be the previously missed NULL changes, or it can reveal that the sentinel had masked a real value. Record the engine version, query text, and reads with the test. That evidence makes the rollout safer than relying on a tidy-looking predicate alone.

Choose the Predicate That States the Rule

The attraction of IS DISTINCT FROM is that another reader sees the intended NULL rule in one phrase. Use it where the engine supports it, and keep an explicit equivalent for older deployments. The operator makes comparison clearer; it does not remove the need for a stable join, representative test data, or a measured plan.

I have seen a single missed NULL transition leave a report wrong for weeks because the rows appeared unchanged to the ETL code. A five-row truth table would have found it in minutes. Put that small test in the change-detection review, then inspect the access path on the real table before calling the fix complete.

Related reading on this blog: Unique Indexes on Nullable Columns and Difference Between ISNULL and COALESCE.

IS DISTINCT FROM is not a substitute for a stable key, it is a NULL-safe comparison for values.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.