Optimize Query with subquery on the same table

Question

I have an Employee table and another one EmployeeManager, I need to do a query to show all employees that are not managers and another one that shows all employees that are managers.

This is the first query:

SELECT E.EmployeeId, E.FirstName, E.LastName
  FROM Employee E
 INNER JOIN (SELECT a.EmployeeId,
                (SELECT COUNT(*) FROM EmployeeManager b 
                      WHERE a.EmployeeId = b.Managerid) IsManager
               FROM EmployeeManager a) ER
    ON E.EmployeeId = ER.EmployeeId
   AND ER.IsManager = 0

Is there any better option? because I don't like at all.

Good question @HLGEM.. OP: Can you show some data or a fiddle? — Indi
– Indi, Commented Jan 10, 2014 at 17:10

Code Maverick · Accepted Answer · 2014-01-10 17:40:23Z

1

All Employees that are Managers:

SELECT   E.EmployeeId, E.FirstName, E.LastName
FROM     Employee E
JOIN     EmployeeManager M ON E.EmployeeId = M.ManagerId

All Employees that are not Managers:

SELECT   EmployeeId, FirstName, LastName
FROM     Employee
WHERE    EmployeeId NOT IN 
         (
             SELECT   ManagerId 
             FROM     EmployeeManager 
             WHERE    ManagerId IS NOT NULL
         )

Note: I added the WHERE ManagerId IS NOT NULL due to a comment from @usr that suggested NULLs may be allowed for ManagerId, which would cause the NOT IN to fail. Without knowing the schema, this covers that scenario.

edited Jan 10, 2014 at 17:40

answered Jan 10, 2014 at 17:15

Code Maverick

20.5k12 gold badges65 silver badges117 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

usr Over a year ago

Clean, minimal and fast. +1. But: The NOT IN will not work due to NULL values. Filter them out.

Code Maverick Over a year ago

@usr - I'm not following. Wouldn't it only be pulling Employees who aren't in the EmployeeManager table? Where are the NULL values coming from?

usr Over a year ago

Maybe EmployeeManager.ManagerId can contain nulls. If there is just one NULL, the NOT IN will never match anything.

Code Maverick Over a year ago

@usr - I assumed it was an identity column, but just incase you are right, I edited the post since we don't know what they OP's schema is.

Collectives™ on Stack Overflow

Optimize Query with subquery on the same table

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related