3

I have an issue with not exists sql query at w3schools

I want to select all customers that work with shipperid = 1 BUT not shipperid = 3. I tried the following:

select o1.customerid, o1.shipperid
from orders o1
where o1.shipperid=1 and not exists
(select o2.customerid from orders o2
where o1.orderid=o2.orderid
and o2.shipperid=3)
order by customerid
;

The above query gives all customers that work with shipperid = 1 and does NOT exclude customers who work with shipperid = 3. What is not correct with the query. (I need to speifically use not exists)

PS: I know the in solution:

select customerid, shipperid
from orders
where shipperid=1 and customerid not in (
select customerid
from orders
where shipperid=3
)
order by customerid;

Why does not the not exists solution work?

CHEBURASHKA
  • 1,623
  • 11
  • 53
  • 85
  • You should be interested by [this question](http://stackoverflow.com/questions/173041/not-in-vs-not-exists) in you already know `in` – glautrou Aug 12 '13 at 22:05
  • I still don't understand the question. What's wrong with the `NOT EXISTS`(note that it's better than `NOT IN`, see: http://www.sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join). – Tim Schmelter Aug 12 '13 at 22:12
  • The problem is that it does not work for me. For example I get `customerid` number 4 who works with shipperid number 1 as well as number 3. I do not know what is the problem with my `not exists` query – CHEBURASHKA Aug 12 '13 at 22:18

1 Answers1

2

I'm fairly certain that the problem lies in the way you're joining the correlated subquery, on orderid = orderid. I'm not familiar with this dataset, but it seems surprising that the same order would have different shippers, and it adds a condition not found in your 'correct' answer. This should work:

select o1.customerid
      ,o1.shipperid
from orders as o1
where o1.shipperid = 1 
and not exists (
    select o2.orderid 
    from orders as o2
    where o1.customerid = o2.customerid
    and o2.shipperid = 3)
order by customerid
;
prekolna
  • 1,525
  • 11
  • 16