Sql-server – Conditional Join – Specific Column Select

join;performancesql server

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?

Best Answer

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
Related Question