Finding an ID not in another column

user_11107767 0
Viewed 899

I'm working on a little SQL exercise, and am scratching my head an this problem.

I am trying to find all the Employees to whom no other employee reports to. This is what the employees table looks like: 

   EmployeeId   LastName    FirstName   Title                 ReportsTo
             1  Adams       Andrew      General Manager       null
             2  Edwards     Nancy       Sales Manager         1
             3  Peacock     Jane        Sales Support Agent   2
             4  Park        Margaret    Sales Support Agent   2
             5  Johnson     Steve       Sales Support Agent   2
             6  Mitchell    Michael     IT Manager            1
             7  King        Robert      IT Staff              6
             8  Callahan    Laura       IT Staff              6

I thought a straightforward one of these queries would do it:

SELECT *
FROM employees
Where EmployeeId not in (select ReportsTo from employees)


SELECT *
FROM employees
Where EmployeeId not in (ReportsTo)

But those return the following results, which isn't what I'm looking for:

EmployeeId  LastName    FirstName   Title                ReportsTo
         2  Edwards     Nancy       Sales Manager        1
         3  Peacock     Jane        Sales Support Agent  2
         4  Park        Margaret    Sales Support Agent  2
         5  Johnson     Steve       Sales Support Agent  2
         6  Mitchell    Michael     IT Manager           1
         7  King        Robert      IT Staff             6
         8  Callahan    Laura       IT Staff             6

Why is NOT IN returning items that are definitely in that column? How would I go about returning items not in ReportsTo if I am using NOT IN incorrectly?

join sqlite sql
3 Answers

Use not exists with a correlated subquery (as commented by jarlh):

select *
from employees e
where not exists (
    select 1
    from employees e1 
    where e1.ReportsTo = e.EmployeeId   
)
user_106767160

The problem with your 1st query is that you use NOT IN with a list that contains a NULL value.
So a comparison of an EmployeeId like say 5:

5 NOT IN (null, 1, 2, 6)

will return NULL, because any comparison to NULL returns NULL and that EmployeeId will not be included in the results.
Change to:

SELECT *
FROM employees
Where EmployeeId not in (
  select ReportsTo 
  from employees 
  where ReportsTo is not null
);

See the demo.
Results:

| EmployeeId | LastName | FirstName | Title               | ReportsTo |
| ---------- | -------- | --------- | ------------------- | --------- |
| 3          | Peacock  | Jane      | Sales Support Agent | 2         |
| 4          | Park     | Margaret  | Sales Support Agent | 2         |
| 5          | Johnson  | Steve     | Sales Support Agent | 2         |
| 7          | King     | Robert    | IT Staff            | 6         |
| 8          | Callahan | Laura     | IT Staff            | 6         |
user_104988280

You can simply use the below query-- 

select * from employees emp where employeeID not in (select ReportsTo from employee)
user_31150560
Related