Alternative methodology instead of NOT IN

Question

Employee Table

EmployeeId 

E001         
E002         
E003         
E004

Service Table

ServiceDate     EmployeeId

4/07/2014       E001
3/07/2014       E002
3/01/2014       E004

I want to list the details of employee(s) who has/have never done any car service.

The result should be:

EmployeeId
E003

I tried outer join LEFT OUTER JOIN but it got messed up.

You should not use outer join. Just join or use where not exists. — user2672165, Jul 31 '14 at 11:07

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

You can use a LEFT JOIN instead of using NOT IN, like so:

select e.* 
from employee e
left join service s on e.employeeid = s.employeeid
where s.employeeid is null

The LEFT JOIN ensures that you get a result set where every employee may or may not have a matching entry in Service table. The WHERE then filters that result set to retain only those employees which do not have a corresponding entry in Service, which is equivalent to your NOT IN method.

You should understand that there is nothing wrong with using a subquery in this case, but using a join may be preferable for performance reasons. This question provides an excellent analysis of joins vs subqueries.

edited May 23 '17 at 11:57

Community

1
1

answered Jul 31 '14 at 11:17

shree.pat18

21,449
3
43
63

I believe what you have here (or its equivalent) is in fact what most decent SQL engines will internally convert the OP's original query into. – 500 - Internal Server Error Jul 31 '14 at 11:23
Yes, I believe that sort of optimisation is done quite commonly. – shree.pat18 Jul 31 '14 at 11:25

score 0 · Answer 2 · edited May 23 '17 at 11:57

0

SELECT * 
FROM Employee e 
WHERE (
      SELECT COUNT(*) 
      FROM service s 
      WHERE e.EmployeeId = s.EmployeeId
      ) = 0;

I do not recommend doing this, though.

Another solution with better performance (from Conffusion's comment)

SELECT * 
FROM Employee e 
WHERE NOT EXISTS (
      SELECT *
      FROM service s 
      WHERE e.EmployeeId = s.EmployeeId
      );

edited May 23 '17 at 11:57

Community

1
1

answered Jul 31 '14 at 11:10

ntv1000

626
6
16

1

or: where not exists (select 1 from service where e.employeeid = s.employeeid). Not exists is faster then count. – Conffusion Jul 31 '14 at 11:14

score 0 · Answer 3 · answered Jul 31 '14 at 11:11

didNot tested, but I think it can work:

select *
from (select distinct E.*, S.employeeid serviceEmployeeid 
        from employee E left outer join service S on e.employeeid = S.employeeid)
where serviceEmployeeid is null

added distinct for the situation a single employee had multiple services.

score 0 · Answer 4 · answered Jul 31 '14 at 11:19

0

I think this would produce your solution.

SELECT EmployeeId
FROM 
  EMPLOYEE
  LEFT OUTER JOIN
  SERVICE
  ON (EMPLOYEE.EmployeeId = SERVICE.EmployeeId)
WHERE
  SERVICE.ServiceId IS NULL
;

answered Jul 31 '14 at 11:19

liselorev

73
4

Alternative methodology instead of NOT IN

4 Answers4