CODEWITHSUNDEEP
sqlsql-servert-sqlquery-optimization
Mike
Mike

Reputation: 158

Querying for inequality on nullable columns in T-SQL

I know that both the == and =! operators outright ignore NULL values on either side, because NULL is the lack of a value and therefore cannot EQUAL another value, nor can it NOT EQUAL another value. However, when you must perform a != on columns that might have nulls, you need to code around this. In the sample code below, I have done just this, to yield the results that I want (which is the middle six rows out of the eight I've inserted). However, I can't say I'm thrilled with the complexity of this code and can't help but wonder if T-SQL provides an alternative way to get the same result. I've considered using ISNULL (or COALESCE) on either side of the !=. However, who's to say that whatever I chose as the default value with those functions isn't a valid value in the table? (For example ISNULL(val1, '') clearly wouldn't work because there are places where '' is stored as a value).

TLDR - Is there a cleaner expression to use in the WHERE clause below that would produce the same 6 results, without using ISNULL or COALESCE?

drop  table if exists #values 
create table #values
(val1       varchar(10),
 val2       varchar(10))

insert #values
values ('', ''), ('', NULL), (NULL, ''), ('A', ''), ('', 'A'), ('A', NULL), (NULL, 'A'), ('A', 'A')

select * 
from #values
where val1 != val2 OR (val1 IS NULL AND val2 IS NOT NULL) OR  (val1 IS NOT NULL AND val2 IS NULL)

Upvotes: 0

Views: 39

Answers (0)

Related Questions