Having trouble understanding this SQL code

Question

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

Gordon Linoff · Accepted Answer · 2018-06-07 02:20:19Z

1

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.

edited Jun 7, 2018 at 2:20

answered Jun 6, 2018 at 16:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ross Presser Over a year ago

Except he wanted S.name, not S.sid

Gordon Linoff Over a year ago

@RossPresser . . . It is easy enough to add a join in to get the name.

Collectives™ on Stack Overflow

Having trouble understanding this SQL code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related