I have tables like these:
Table Employee ( Emp_Id, Full_Name, ...) Table Order ( Submitter_Id, Recipient_Id, ...)
Then say that I have conditional join like this:
select isnull(Emp.Full_Name, Emp2.Full_name) as Recipient -- , other fields from Order left outer join Employee Emp on Emp.Emp_Id = Order.Recipient_Id left outer join Employee Emp2 on Emp2.Emp_Id = Order.Submitter_Id -- where clauses
Brief explaination: The query will return the name of recipient from specific order. An order may has recipient id being set, or using submitter id if the recipient is not set.
The condition: Both table
Employee has big amount of records inside, so joining them both is a costly operation. 80% - 90% of records in
recipient_id set, so joining to
submitter_id can be useless operation. Using isnull as join condition can resulting in
index scan (in my experience). Using
subquery for column maybe help, but the cost can be high because the operation can be row-by-row.
Is there any conditional join for case like this?