CODEWITHSUNDEEP
sqlsql-servert-sql
Chad Baldwin
Chad Baldwin

Reputation: 2602

Finding rows where two values not equal, with nulls

I'm interested to know what the common practices are for this situation.

You need to find all rows where two columns do not match, both columns are nullable (Exclude where both columns are NULL). None of these methods will work:

WHERE A <> B --does not include any NULLs

WHERE NOT (A = B) --does not include any NULLs

WHERE (A <> B OR A IS NULL OR B IS NULL) --includes NULL, NULL

Except this...it does work, but I don't know if there is a performance hit...

WHERE COALESCE(A, '') <> COALESCE(B, '')

Lately I've started using this logic...it's clean, simple and works...would this be considered the common way to handle it?:

WHERE IIF(A = B, 1, 0) = 0
--OR
WHERE CASE WHEN A = B THEN 1 ELSE 0 END = 0

Upvotes: 0

Views: 104

Answers (2)

Adinugraha Tawaqal
Adinugraha Tawaqal

Reputation: 371

How about using except ?

for example if i want to get all a and b that is not a=b and exclude all null values of a and b

select a, b from tableX where a is not null and b is not null
except
select a,b from tableX where a=b

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

This is a bit painful, but I would advise direct boolean logic:

where (A <> B) or (A is null and B is not null) or (A is not null and B is null)

or:

where not (A = B or A is null and B is null)

It would be much simpler if SQL Server implemented is distinct from, the ANSI standard, NULL-safe operator.

If you use coalesce(), a typical method is:

where coalesce(A, '') <> coalesce(B, '')

This is used because '' will convert to most types.

Upvotes: 4

Related Questions