How to rewrite the sql script where in join case statement is used-CodePudding

I have the following sql code:

select t1.*
from t1
join t3 on t3.id = t1.id
join t2 on case when t1.date is null then t2.date = t3.date else t1.date = t2.date

but it is not optimal to use case statement in joins Is there a way to rewrite this, nothing comes to my mind

CodePudding user response：

Lets first swap t2.date so that it always occurs at the left hand side of the comparison operation:

case when t1.date is null then t2.date = t3.date else t1.date = t2.date

Becomes:

case when t1.date is null then t2.date = t3.date else t2.date = t1.date

Then we observe that t2.date always compared in both cases of the case statement, i.e. it is non-null. We can express the case-statement in the following coalesce statement:

t2.date = coalesce(t1.date, t3date)

Which looks a lot cleaner, but is still functionally the same as the case statement.

Mind that if you're not projecting any values from t2 and t3, it will be faster to do a where date in (select date from t2) or date in (select date from t3).