CODEWITHSUNDEEP
sql-servert-sqlnull
stefano m
stefano m

Reputation: 4224

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 @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!

Upvotes: 1

Views: 149

Answers (4)

Martin Smith
Martin Smith

Reputation: 452947

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.

Upvotes: 1

Jesuraja
Jesuraja

Reputation: 3844

You can use ISNULL

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

Upvotes: -1

Dimitris Kalaitzis
Dimitris Kalaitzis

Reputation: 1426

You could try using NULLIF like this:

WHERE NULLIF(@X,@Y) IS NOT NULL OR NULLIF(@Y,@X) IS NOT NULL

Upvotes: 0

Dimt
Dimt

Reputation: 2328

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.

Upvotes: 3

Related Questions