CODEWITHSUNDEEP
sqloracle-database
brightpants
brightpants

Reputation: 525

Not In query not working

I'm trying to run this query, it's supposed to get all employees who're not drivers in wh_route, the driver is a fk for

The following queries are working and giving me back results: 

select distinct driver from wh_route;
select id from pa_employee o where o.id not in (1,2,3,4,5);

But when I try to do this:

select id from pa_employee o where o.id not in (select distinct driver from wh_route);

It doesn't return any results.

Tables for reference:

WH_ROUTE
id    driver
1     1
2     2

PA_EMPLOYEE
id
1
2
3

The expected behaviour is that it returns me the id 3, but it returns me nothing

Upvotes: 0

Views: 34

Answers (3)

brightpants
brightpants

Reputation: 525

Just figured out: in the wh_route table there was a null value for driver, so the working query became 

select id 
  from pa_employee o 
  where o.id not in (
    select distinct driver 
      from wh_route 
      where id is not null);

Upvotes: 1

ravioli
ravioli

Reputation: 3833

You may also want to remove the DISTINCT in your sub-SELECT since it's not needed. That may speed up your query.

Upvotes: 0

jarlh
jarlh

Reputation: 44766

NOT IN is somewhat tricky if the sub-query returns null values. Try NOT EXISTS instead:

select id from pa_employee o
where o.id not exists (select * from wh_route where driver = o.id);

Upvotes: 1

Related Questions