CODEWITHSUNDEEP
sql
mgrant24601
mgrant24601

Reputation: 13

performance comparison on coalesce vs 'is null'

select * from zzz t1
INNER JOIN yyy T2
on (T1.col1 = T2.col1 )
and (T1.col2 = T2.col2 or (T1.col2 is null and T2.col2 is null ) )

VS

select * from zzz t1
INNER JOIN yyy T2
on (T1.col1 = T2.col1 )
and (coalesce(T1.col2, '\0') = coalesce(T2.col2, '\0'))

Or if there's a third, better, way to do this I'd appreciate that too. This is something I find myself constantly doing because half of our databases allow nulls and half of them don't so comparisons suck.

The only thing is I need to avoid things that aren't standard, I use too many different dbs and I'm trying hard to get away from anything that isn't supported by all of them unless the performance gain is utterly magical. (db2, oracle, sqlserver are the main ones I'm using)

Upvotes: 0

Views: 1210

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

If a database has NULL safe comparisons, then the best approach is:

select *
from zzz t1 join
     yyy T2
     on (T1.col1 = T2.col1 ) and
        (T1.col2 is not distinct from T2.col2);

(This is supported by DB2 but not Oracle or SQL Server.)

Otherwise, I think your two versions are going to be equivalent in most databases at least. The use of functions/or limits the ability to use indexes for col2.

Upvotes: 1

Related Questions