Confused about NOT EXISTS

Question

"Find the names of sailors who have reserved all boats."

SQL code:

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS ( SELECT B.bid
                   FROM Boats B WHERE NOT EXISTS ( SELECT R. bid
                                                   FROM Reserves R            
                                                   WHERE R.bid = B.bid
                                                   AND R.sid = S.sid ))

Could anyone please explain/translate the above sql code? Why is there NOT EXISTS but not IN. And why is value checking happens in the innermost subquery (WHERE R.bid = B.bid AND R.sid = S.sid) ?

Possible duplicate of [NOT IN vs NOT EXISTS](http://stackoverflow.com/questions/173041/not-in-vs-not-exists) — Igor, Oct 28 '16 at 17:08
Then what are you confused on exactly? Have you already looked at the documentation and examples on MSDN? https://msdn.microsoft.com/en-us/library/ms188336.aspx — Igor, Oct 28 '16 at 17:11
@Igor, im confused about the logic of NOT EXIST. the problem states `"Find the names of sailors who have reserved all boats."`, but what im confused about is what does `NOT EXISTS` do here ? Why not use `EXISTS` ? — Mike, Oct 28 '16 at 17:12
@Igor, could you please translate the sql code into plain english ?? — Mike, Oct 28 '16 at 17:13
Right, and how do you determine if a sailor has reserved ALL boats? Well, another way to think about that is: there does NOT EXIST a boat that the sailor hasn't reserved. — aquinas, Oct 28 '16 at 17:14
See also http://stackoverflow.com/questions/35398701/exist-and-not-exists-query-misunderstanding. — philipxy, Nov 13 '16 at 01:55

score 2 · Answer 1 · answered Oct 28 '16 at 17:21

"Sailor S has reserved all boats" is logically equivalent to "there does not exist a boat that Sailor S has not reserved". This gives you the first layer of the query:

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS ( SELECT B.bid
                   FROM Boats B WHERE ...

"Sailor S has not reserved boat B" is logically equivalent to "there does not exist a reservation for boat B by sailor S". This produces the final piece the second subquery:

NOT EXISTS ( SELECT R. bid
               FROM Reserves R            
               WHERE R.bid = B.bid
                AND R.sid = S.sid )

In this innermost subquery, B.bid and S.sid refer back to the tables referenced in the outer query blocks. This is called a correlated subquery, and means it is evaluated separately for each row produced by other outer query, using the values from that row.

Confused about NOT EXISTS

1 Answers1