SQL Server 2022 introduced the IS [NOT] DISTINCT FROM predicate that compares the equality of two expressions and guarantees a true or false result, even if one or both operands are NULL.

Normally if we compare two NULL values, they will always be different (although this will depend on your ANSI_NULLS setting – setting ANSI_NULLS to OFF will result in NULLs being treated as equal). The IS [NOT] DISTINCT FROM predicate enables us to compare NULLs as though they’re equal, even when our ANSI_NULLS setting is set to ON.

Syntax

The syntax goes like this:

expression IS [NOT] DISTINCT FROM expression

The [NOT] part means that we have the option of specifying NOT to negate the DISTINCT FROM operation.

The IS [NOT] DISTINCT FROM predicate is used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.

Example

Suppose we have a table with the following data:

SELECT * FROM t1;

Result:

c1     c2    
------ ------
     1      1
     1      2
     1   NULL
  NULL      1
  NULL   NULL

Here’s a query that uses IS DISTINCT FROM against that table:

SELECT * FROM t1
WHERE c1 IS DISTINCT FROM 1;

Result:

c1     c2    
------ ------
  NULL      1
  NULL   NULL

We can see that the only rows that are returned are those that don’t contain 1 in the c1 column.

Let’s switch it around to IS NOT DISTINCT FROM:

SELECT * FROM t1
WHERE c1 IS NOT DISTINCT FROM 1;

Result:

c1     c2    
------ ------
     1      1
     1      2
     1   NULL

This time we get all the rows that weren’t returned in the previous query. That’s because the value 1 is not distinct from 1 (i.e. it’s the same).

Now let’s use NULL as the second expression:

SELECT * FROM t1
WHERE c1 IS DISTINCT FROM NULL;

Result:

c1     c2    
------ ------
     1      1
     1      2
     1   NULL

This time I specified c1 IS DISTINCT FROM NULL and so none of the rows that have NULL in the c1 column are returned.

And let’s negate it with NOT:

SELECT * FROM t1
WHERE c1 IS NOT DISTINCT FROM NULL;

Result:

c1     c2    
------ ------
  NULL      1
  NULL   NULL

This time we got all rows that have NULL in the c1 column.

The above query returned the same result that we’d get if we used IS NULL:

SELECT * FROM t1
WHERE c1 IS NULL;

Result:

c1     c2    
------ ------
  NULL      1
  NULL   NULL

Now let’s compare the two columns themselves:

SELECT * FROM t1
WHERE c1 IS DISTINCT FROM c2;

Result:

c1     c2    
------ ------
     1      2
     1   NULL
  NULL      1

This time, the only rows returned are those where the values in the two columns are distinct from each other.

And let’s negate it with NOT:

SELECT * FROM t1
WHERE c1 IS NOT DISTINCT FROM c2;

Result:

c1     c2    
------ ------
     1      1
  NULL   NULL

This time we only get those rows where the two columns contain values that are not distinct. In other words, we only get those rows where the two columns contain the same value – even if that value is NULL.

This is actually a different result to what we’d get if we were to use an equality operator such as the equals (=) operator. Here’s what that produces:

SELECT * FROM t1
WHERE c1 = c2;

Result:

c1     c2    
------ ------
     1      1

This time it doesn’t return the row with the NULLs.

Furthermore, if we use the not equal to (<>) operator, we also don’t get the rows that contain NULL:

SELECT * FROM t1
WHERE c1 <> c2;

Result:

c1     c2    
------ ------
     1      2

So we can see that the IS [NOT] DISTINCT FROM predicate provides a useful way for us to handle NULL values in a way we wouldn’t be able to if we were to rely solely on the equality operators.

When using the = and <> equality operators, we can actually get a different result, depending on our current ANSI_NULLS setting.

Also, SET ANSI_NULLS ON affects a comparison only if one of the operands of the comparison is either a variable that is NULL or a literal NULL. If both sides of the comparison are columns or compound expressions, the setting does not affect the comparison.

See SQL Server ANSI_NULLS Explained for more information on the ANSI_NULLS setting.