Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 Order and Employee has big amount of records inside, so joining them both is a costly operation. 80% - 90% of records in Order has 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?

share|improve this question
    
I need to test the suggested answers first before accepting any of the answers below. –  Fendy Oct 16 '13 at 1:40
add comment

3 Answers

up vote 2 down vote accepted

Regarding your comment about the scan, that happens because using ISNULL() in the join manipulates the field, so it's no longer SARGable. It may be faster to split this up, rather than try to do all the work in a single query. For example:

DECLARE @Recipient_ID INT, @Submitter_ID INT

SELECT 
@Recipient_ID = Order.Recipient_Id, 
@Submitter_ID = Order.Submitter_Id
FROM Order
-- where clauses

IF @Recipient_ID IS NULL

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Submitter_ID

ELSE

SELECT Emp.Full_Name
FROM Employee Emp
WHERE Emp.Emp_Id = @Recipient_ID

EDIT based on comment: Since there can be many orders, what about this? It is SARGable so should index well, and you're not querying the table multiple times.

with cte as (
SELECT 
COALESCE(Order.Recipient_Id, Order.Submitter_Id) AS EmployeeID
FROM Order
-- where clauses
)

SELECT Emp.Full_Name
    FROM Employee Emp
INNER JOIN cte on cte.EmployeID = Emp.Emp_Id
share|improve this answer
    
You are right, isnull making it non-sargable. However the query I mentioned is kind of reporting, so the resulting rows will be many (from many orders). So if-else ing using the parameter cannot be achieved. I can, though, joining using UNION ALL, though the performance is not yet tested. –  Fendy Oct 14 '13 at 3:11
    
I'm not sure if SO notifies of answer edits, so please check out what I added to see if it helps. –  Nicholai Oct 14 '13 at 3:18
    
Unfortunately, I don't think your query will be treated as sargable, since coalesce can be treated like isnull as well (I may be wrong though). However I think it can works using temp table and not CTE, hasn't tried though. –  Fendy Oct 14 '13 at 3:31
1  
Ack, you're right! I forgot that CTE executes during the SELECT, so a temp table will be faster. Another experiment is to try ...FROM Order INNER JOIN Employee Emp ON Emp.Emp_ID = CASE WHEN Order.Recipient_Id IS NULL THEN Order.Submitter_Id ELSE Order.Recipient_Id END. Since we haven't manipulated the field, and Employee is the table to search, I think it will remain SARGable. See sqlblog.com/blogs/rob_farley/archive/2010/02/02/… –  Nicholai Oct 14 '13 at 3:55
    
Interesting, I haven't know that CASE WHEN can be treated differently with ISNULL. Will has this query tested then, to see whether it works or not. –  Fendy Oct 14 '13 at 4:10
add comment

You will probably get better performance by using a UNION. This almost always works better in cases where I need to do a conditional join or filter.

SELECT Emp.Full_Name AS Recipient
FROM [Order]
    INNER JOIN Employee Emp ON Emp.Emp_Id = [Order].Recipient_Id
UNION --Or UNION ALL if you don't care about duplicates and want faster results
SELECT Emp2.Full_name
FROM [Order]
    INNER JOIN Employee Emp2 ON Emp2.Emp_Id = [Order].Submitter_Id
share|improve this answer
add comment

You can also try this change in the condition of the second join:

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
                                 AND Order.Recipient_Id IS NULL
   where clauses ;

and this, splitting the query into 2 parts, with UNION:

select Emp.Full_Name as Recipient  -- , other fields
  from Order
    INNER join Employee Emp on Emp.Emp_Id = Order.Recipient_Id
  where clauses
UNION ALL
select Emp2.Full_name as Recipient  -- , other fields
  from Order
    left outer join Employee Emp2 on Emp2.Emp_Id = Order.Submitter_Id
  where (clauses)
    AND Order.Recipient_Id IS NULL ;

And another idea. It looks simpler but I'm not at all sure about efficiency:

select Emp.Full_Name as Recipient  -- , other fields
  from Order
    left outer join Employee Emp on Emp.Emp_Id = 
                                    COALESCE(Order.Recipient_Id, Order.Submitter_Id)
   where clauses ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.