CODEWITHSUNDEEP
sqlpostgresql
joey madahay
joey madahay

Reputation: 21

Having trouble understanding this SQL code

So I've been studying for an exam and ive come across a question that I kind of need help with.

The code is trying to find the names of sailors that reserve all boats:

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

Now I feel like I get most of it but the only issue I'm having is Except and Not Exists and how they work together when doing division like the case presented above. I think I get what not exists and except do separately but together is where my confusion lies

Upvotes: 2

Views: 101

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

The subquery for the not exists gets all boats that are not in reserves for that sailor. I would call this "unreserved boats for the sailor".

The outer query then gets all sailors that have no boats that are unreserved for that sailor. In other words, the overall query is getting sailors that have reserved all boats.

This is definitely not how I would write the query. I think the intention of this version is much clearer:

select sid
from reserves
group by sid
having count(*) = (select count(*) from boats);

Note: this might need count(distinct bid), if a sailor can reserve the same boat more than once.

Upvotes: 1

Related Questions