SQL2022: Finally! – IS [NOT] DISTINCT FROM

Ok, this isn’t really a huge problem to begin with, but it catches everyone out at some time or another. Particularly new SQL coders. It also makes for some horrendous predicates that can be difficult to decipher and format in a way that makes them understandable.

So here’s the deal with SQL – NULL doesn’t equal NULL¹.

DECLARE @x CHAR(1) = 'A'

IF (@x = @x)
  PRINT 'Compared!'

Now you run this and you’ll get “Compared!”. Now let’s change the DECLARE to NULL

DECLARE @x CHAR(1) = NULL

IF (@x = @x)
  PRINT 'Compared!'

When you run this, you get nothing back – because no two NULLs are the same, even if they were the very same variable. Same applies for comparisons between columns – you have to tie yourself in knots within your predicate to cover all the eventualities²:

WHERE
  ( NOT
    (
      t1.[column] <> t2.[column]
      OR t1.[column] IS NULL
      OR t2.[column] IS NULL
    )
    OR
    (
      t1.[column] IS NULL
      AND t2.[column] IS NULL
    )
  )

But that’s no longer the case in SQL2022 – after many years (25 to be exact), this has been implemented:

WHERE  t1.[column] IS NOT DISTINCT FROM t2.[column]

Change the above two DECLARE @x examples to use IS NOT DISTINCT³ and they’ll both output “Compared!”.

Ok, it’s a little verbose and could probably be better served with proper comparator but it should be your new go-to means of solving NULL != NULL. It also puts to an end to the cheesy solution of having a set of comparator scalar functions laying around for each datatype.

1. https://modern-sql.com/concept/three-valued-logic ↩︎
2. https://stackoverflow.com/questions/10416789/how-to-rewrite-is-distinct-from-and-is-not-distinct-from-in-sql-server-20008r2 ↩︎
3. https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16 ↩︎