Convert Exist condition to Join with T-SQL

Question

I am trying to convert the following T-SQL Select query to exclude "Exists" Clause and Include "Join" Clause. but i am ending up not getting the right result. can some one from this expert team help me with some tips.

select *
FROM HRData
INNER JOIN (
    SELECT eeceeid, MIN(eecdateoftermination) eTermDate
            FROM dbo.empcomp
            INNER JOIN 
            (
            SELECT xeeid FROM HRData_EEList
            INNER JOIN  dbo.empcomp t ON xeeid = eeceeid AND xcoid = eeccoid
            WHERE eecemplstatus = 'T' AND eectermreason <> 'TRO' AND eeccoid <> 'WAON6'
            AND EXISTS (    SELECT 1 FROM dbo.empded  
                            INNER JOIN dbo.dedcode on deddedcode = eeddedcode AND deddedtype = 'MED' AND (eedbenstopdate IS NULL OR eedbenstopdate > '12/31/2005')
                            WHERE eedeeid = xeeid AND eedcoid = xcoid )
            GROUP BY xeeid
            HAVING COUNT(*) > 1) Term ON xeeid = eeceeid
            group by eeceeid

) Terms ON eeid = eeceeid AND Termdate = eTermDate

Answer 1

The algorithm to convert EXISTS to JOIN is very simple.

Instead of

FROM   A
WHERE  EXISTS (SELECT *
               FROM   B
               WHERE  A.Foo = B.Foo) 

Use

FROM   A
       INNER JOIN (SELECT DISTINCT Foo
                   FROM   B) AS B
         ON A.Foo = B.Foo 

But the first one probably will be optimised better

Answer 2

Interesting request.

select *
FROM HRData
INNER JOIN (
    SELECT eeceeid, MIN(eecdateoftermination) eTermDate
            FROM dbo.empcomp
            INNER JOIN 
            (
            SELECT xeeid FROM HRData_EEList
            INNER JOIN  dbo.empcomp t ON xeeid = eeceeid AND xcoid = eeccoid
            INNER JOIN 
            (    SELECT DISTINCT xeeid,  xcoid FROM dbo.empded  
                            INNER JOIN dbo.dedcode on deddedcode = eeddedcode AND deddedtype = 'MED' AND (eedbenstopdate IS NULL OR eedbenstopdate > '12/31/2005')
                           -- WHERE eedeeid = xeeid AND eedcoid = xcoid 
            ) AS A ON xeeid = A.xeeid AND eedcoid  = A.eedcoid
            WHERE eecemplstatus = 'T' AND eectermreason <> 'TRO' AND eeccoid <> 'WAON6'            
            GROUP BY xeeid
            HAVING COUNT(*) > 1) Term ON xeeid = eeceeid
            group by eeceeid

) Terms ON eeid = eeceeid AND Termdate = eTermDate

Answer 3

Another method of converting an exists to a join is to use a ROW_NUMBER() in the subselect to assist in removing duplicates.

EXISTS:

FROM   A
WHERE  EXISTS (SELECT *
               FROM   B
               WHERE  B.Condition = 'true' AND A.Foo = B.Foo) 

JOIN:

FROM   A
JOIN (SELECT B.Foo, ROW_NUMBER() OVER (PARTITION BY B.Foo ORDER BY B.Foo) RN
      FROM   B
      WHERE  B.Condition = 'true') DT
ON A.Foo = DT.Foo AND DT.RN = 1

The ORDER BY is totally arbitrary since you don't care which record it selects, but it's required. You may be able to use (SELECT NULL) instead.