CODEWITHSUNDEEP
sql-server
LCJ
LCJ

Reputation: 22662

Avoid referring table two times in the WHERE clause

Following is a simplified version of my database in SQL Server 2005. I need to select employees based on business units. Each employee has home department, parent department and visiting department. Based on the department, business unit can be found out.

Following query works fine. But it has scan on the #DepartmentBusinesses table two times. Is there a way to use the table #DepartmentBusinesses only once by making it as a CASE statement or similar?

DECLARE @SearchBusinessUnitCD CHAR(3)
SET @SearchBusinessUnitCD = 'B'

--IF HomeDeptID = ParentDeptID, then @SearchBusinessUnitCD should be present for the VisitingDeptID
--IF HomeDeptID <> ParentDeptID, then @SearchBusinessUnitCD should be present for the ParentDeptID

CREATE TABLE #DepartmentBusinesses (DeptID INT, BusinessUnitCD CHAR(3))
INSERT INTO #DepartmentBusinesses
    SELECT 1, 'A' UNION ALL 
    SELECT 2, 'B' 

CREATE NONCLUSTERED INDEX IX_DepartmentBusinesses_DeptIDBusinessUnitCD ON #DepartmentBusinesses (DeptID,BusinessUnitCD)

DECLARE @Employees TABLE (EmpID INT, HomeDeptID INT, ParentDeptID INT, VisitingDeptID INT)
INSERT INTO @Employees 
    SELECT 1, 1, 1, 2 UNION ALL
    SELECT 2, 2, 1, 3

SELECT * 
FROM @Employees
WHERE
    (
            HomeDeptID = ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = VisitingDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD)
            )
    OR 
    (
            HomeDeptID <> ParentDeptID
        AND
            EXISTS (
                SELECT 1
                FROM #DepartmentBusinesses
                WHERE DeptID = ParentDeptID
                    AND BusinessUnitCD = @SearchBusinessUnitCD
            )
    )

DROP TABLE #DepartmentBusinesses

Plan

enter image description here

Upvotes: 3

Views: 64

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72235

You can give this a try:

SELECT e.* 
FROM @Employees AS e
INNER JOIN #DepartmentBusinesses AS d 
  ON (d.DeptID = e.VisitingDeptID AND e.HomeDeptID = e.ParentDeptID) OR
     (d.DeptID = e.ParentDeptID AND e.HomeDeptID <> e.ParentDeptID)
WHERE d.BusinessUnitCD = @SearchBusinessUnitCD

Upvotes: 2

Devart
Devart

Reputation: 122042

SELECT * 
FROM @Employees e
WHERE EXISTS (
        SELECT 1
        FROM #DepartmentBusinesses t
        WHERE t.BusinessUnitCD = @SearchBusinessUnitCD
            AND (
                (e.HomeDeptID = e.ParentDeptID AND t.DeptID = e.VisitingDeptID)
                OR
                (e.HomeDeptID != e.ParentDeptID AND t.DeptID = e.ParentDeptID)
            )
    )

Upvotes: 4

Related Questions