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 @Y is not null)
or (@Y is null and @X is not null)
or (@X <> @Y)
Is there a smart way to write this expression? Thanks!
I think you could use COALESCE for that
WHERE coalesce(@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.
I typically use a technique I picked up from here
SELECT 1 AS valuesDifferent
WHERE EXISTS (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.
You could try using NULLIF like this:
WHERE NULLIF(@X,@Y) IS NOT NULL OR NULLIF(@Y,@X) IS NOT NULL
You can use ISNULL
WHERE ISNULL(@X,'') <> ISNULL(@Y,'')
SELECT 1 as valuesDifferent WHERE EXISTS (SELECT @X EXCEPT SELECT @Y). The answer you accepted doesn't work correctly forSET @X = ''; SET @Y = NULLwhere existsreturns true if the sub query it contains returns a row. This will happen in this case if the two values are distinct. Treatingnullas a distinct value