Truly null-safe comparison

Question

Is there a truly safe way of checking two nullable values are not equal in T-SQL that is shorter than this?

where
    A.MyField != B.MyField
or  (
        A.MyField is null
    and B.MyField is not null
)
or  (
        A.MyField is not null
    and B.MyField is null
)

Using isnull() isn't truly safe as it collapses null values into a 'real' value that could potentially exist in the data set, for eg:

where
    isnull(A.MyField, '') != isnull(B.MyField, '')

would incorrectly think that an empty string '' and null are equal, which is not the desired result. You could come up with a "known" value that never occurs or is exceedingly unlikely to occur, but this seems like a band-aid fix.

Setting ANSI_NULLS off is also undesirable for several reasons (most particularly that the feature is being depreciated).

Is there functionality that will do a "true" null-safe check, or is the code above the best way?

Answer 1

For versions from 2005+ You can use

WHERE  EXISTS (SELECT A.MyField
               EXCEPT
               SELECT B.MyField) 

From SQL Server 2022 you can use

WHERE  A.MyField IS DISTINCT FROM B.MyField