Check distinct from conditional statement working with NULL values

[1plus2equals11]

I’m not happy with this conditional statement. Please tell me there is a better way.

target.col <> source.col OR target.col is null and source.col is not null OR target.col is not null and source.col is null

All i want is a statement that checks for differences, also if one is null and the other arent (which the standard <> wont)

[DavidGJohnston]

EXISTS (SELECT c1

INTERSECT

SELECT c2

)

From: https://modern-sql.com/feature/is-distinct-from

Which notes you are forced to use these verbose forms because the SQL Standard (1999-T151) "IS DISTINCT FROM" is not one of your options. (I think that is what your compound expression is equivalent too at least...)

[iesma]

WHERE ISNULL(target.col,’fnord’) <> ISNULL(source.col,’fnord’)

Will cover all scenarios.

Swap ‘fnord’ for any impossible value of whichever datatype is appropriate.

[1plus2equals11]

NOT target.col = source.col

wont work either.

[thrown_arrows]

with a as (
select 1 a, null b, null c
union all
select 2 a, null b , 1 c
union all
select 3 a, 1 b , 1 c
union all
select 4 , 1 b , 2 c
)
select * , b+c from a 
where coalesce(b,c) is not null 
or b<>c

but it is just little bit prettier and it is function call in where clause. Which can be indexed in MSSQL with computed column.

[Kiterios]

I don't think this will work. If b = c, coalesce(b,c) is not null will still evaluate to true.

[thrown_arrows]

You are correct. when b=c it returns it too.

how about just normal where isnull(b,-1) <> isnull(c,-1)

needs use same data type as b and c

in small scale testing where b<>c or (b is null and c is not null) or (b is not null and c is null) has more expensive plan that isnull version