Simpler way to do IS NOT DISTINCT FROM

Is the following the simplest way to write out the IS NOT DISTINCT FROM without using that clause?

   ColA IS NULL AND ColB IS NOT NULL 
OR ColA IS NOT NULL AND ColB IS NULL
OR ColA != ColB

Or is there a simpler way?

IS [NOT] DISTINCT FROM is the simplest way of doing this.

On previous versions you can do (Fiddle)

• EXISTS (SELECT colA INTERSECT SELECT colB) for IS NOT DISTINCT
• EXISTS (SELECT colA EXCEPT SELECT colB) for IS DISTINCT

though the more verbose

• NOT EXISTS (SELECT colA INTERSECT SELECT colB)

sometimes gives a better execution plan for the IS DISTINCT case (example).

See Undocumented Query Plans: Equality Comparisons for some discussion on various alternative approaches for this.

Generally I've found execution plans are fine with the above approaches though occasionally I have found writing out the condition in full as

• WHERE ColA = ColB OR (ColA IS NULL AND ColB IS NULL) (for the NOT DISTINCT case)
• WHERE ColA <> ColB OR (ColA IS NULL AND ColB IS NOT NULL) OR (ColA IS NOT NULL AND ColB IS NULL) (for the DISTINCT case)

does give a better execution plan.

The INTERSECT/EXCEPT are very convenient when you need to do this type of comparison across multiple columns...

EXISTS (SELECT t1.colA, t1.colB, t1.colC 
        INTERSECT 
        SELECT t2.colA, t2.colB, t2.colC)

... but check the plans to see that they are being optimised efficiently in the context you are using them.