Chris Harshman (12/9/2016)

---

It's difficult to tell without knowing the indexes on these tables, but I'm thinking Steve's query is more expensive since it's hitting all 3 base tables twice, and the only one that would really need to be hit twice is #ROLEPERSONS, and just one extra seek to #ROLES. So maybe something like this would be quicker and easier to read:

DECLARE @ControllerID INT;

SELECT @ControllerID = Role_ID FROM #ROLES WHERE Role_Name = 'Controller';

SELECT R.Role_ID, R.Role_Name, P.Person_ID, P.Name

FROM #ROLES AS R

INNER JOIN #ROLEPERSONS AS RP ON R.Role_ID = RP.Role_ID

INNER JOIN #PERSONS AS P ON RP.Person_ID = P.Person_ID

WHERE NOT EXISTS (SELECT NULL FROM #ROLEPERSONS rp2 WHERE rp2.Person_ID = p.Person_ID AND rp2.Role_ID = @ControllerID)

ORDER BY P.Person_ID;