Representing 'not in' subquery as join

Assume employee Data set of

Emp_ID 1 2 3 4 5 6 7 

Assume Manger data set of

Emp_ID 1 2 3 4 5 8 9 select * from employees where emp_id not in (select distinct emp_id from managers); 

The above isn't joining tables so no Cartesian product is generated... you just have 7 records you're looking at...

The above would result in 6 and 7 Why? only 6 and 7 from Employee Data isn't in the managers table. 8,9 in managers is ignored as you're only returning data from employee.

select * from employees a, (select distinct emp_id from managers) b where a.emp_id!=b.emp_id; 

The above didnt' work because a Cartesian product is generated... All of Employee to all of Manager (assuming 7 records in each table 7*7=49) so instead of just evaluating the employee data like you were in the first query. Now you also evaluate all managers to all employees

so Select * results in

1,1 1,2 1,3 1,4 1,5 1,8 1,9 2,1 2,2... 

Less the where clause matches... so 7*7-7 or 42. and while this may be the answer to the life universe and everything in it, it's not what you wanted.

I also tried:

select * from employees a, (select distinct emp_id from managers) b where a.emp_id not in b.emp_id; 

Again a Cartesian... All of Employee to ALL OF Managers

So this is why a left join works

SELECT e.* FROM employees e LEFT OUTER JOIN managers m on e.emp_id = m.emp_id WHERE m.emp_id is null 

This says join on ID first... so don't generate a Cartesian but actually join on a value to limit the results. but since it's a LEFT join return EVERYTHING from the LEFT table (employee) and only those that match from manager.

so in our example would be returned as e.emp_Di = m.Emp_ID

1,1 2,2 3,3 4,4 5,5 6,NULL 7,NULL 

now the where clause so

6,Null 7,NULL are retained... 

older ansii SQL standards for left joins would have been *= in the where clause...

select * from employees a, managers b where a.emp_id *= b.emp_id --I never remember if the * is the LEFT so it may be =* and b.emp_ID is null; 

But I find this notation harder to read as the join can get mixed in with the other limiting criteria...

Your best bet would probably be a left join:

select e.* from employees e left join managers m on e.emp_id = m.emp_id where m.emp_id is null; 

The idea here is you're saying that you want to select everything from employees, including anything that matches in the manager table based on emp_id and then filtering out the rows that actually have something in the manager table.

Use Left Outer Join instead

select e.* from employees e left outer join managers m on e.emp_id = m.emp_id where m.emp_id is null 

left outer join will preserve the rows from m table even if they do not have a match i e table based on the emp_id field. The we filter on where m.emp_id is null - give me all the rows from e where there's no matching record in m table. A bit more on the subject can be found here:

Visual representation of joins

from employees a, (select distinct emp_id from managers) b implies cross join - all posible combinations between tables (and you needed left outer join instead)

The MINUS keyword should do the trick:

SELECT e.* FROM employees e MINUS Select m.* FROM managers m 

Hope that helps...

select * from employees where Not (emp_id in (select distinct emp_id from managers));