Conditional Join - Specific Column Select

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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 ;