How To Compare Different Values In Sql Server

I must to check if two values, X and Y are different. If both are null, they must be considered as equal. The unique way I found is: select 1 as valueExists where (@X is null and

Solution 1:

I think you could use COALESCE for that

WHEREcoalesce(@X, '') <>coalesce(@Y, '')

What it does it returns an empty string if one of variables is null, so if two variables are null the two empty strings become equal.

Solution 2:

I typically use a technique I picked up from here

SELECT1ASvaluesDifferentWHEREEXISTS (SELECT @X
               EXCEPT
               SELECT @Y) 

WHERE EXISTS returns true if the sub query it contains returns a row. This will happen in this case if the two values are distinct. null is treated as a distinct value for the purposes of this operation.

Solution 3:

You could try using NULLIF like this:

WHERENULLIF(@X,@Y) ISNOTNULLORNULLIF(@Y,@X) ISNOTNULL

Solution 4:

You can use ISNULL

WHERE ISNULL(@X,'') <> ISNULL(@Y,'')