CODEWITHSUNDEEP
mysqlsqlsubquery
FrostyStraw
FrostyStraw

Reputation: 1656

Really hard time understanding this query

The following query is supposed to return "sailors who have reserved all boats"

and here's the mySQL code

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))

And I just...don't know how to read through it. I understand that the subquery that follows "NOT EXISTS" should return the boat id (bid) for all the records on the Boats table. So the query that follows Except should be returning the boat ids for all the boats that have been reserved...so that means if there is someone who reserved all boats, nothing should return, meaning the NOT EXISTS will evaluate to true and it will just give the name of that sailor? I think it's the last part that confuses me...how does it end up returning the name of the sailor?

Upvotes: 1

Views: 966

Answers (2)

shA.t
shA.t

Reputation: 16958

We have some boats those can be reserved by sailors, Sailors are registered and we know them, So the structure of tables are:

[Table: Boats]          [Table: Sailors]        [Table: Reserves]
+-----+--------+        +-----+----------+      +-----+-----+-----+
| bid | bname  |        | sid | sname    |      | rid | bid | sid |
+-----+--------+        +-----+----------+      +-----+-----+-----+
| 1   | Boat 1 |        | 1   | Sailor 1 |      | 1   | 1   | 1   |
| 2   | Boat 2 |        | 2   | Sailor 2 |      | 2   | 2   | 3   |
| 3   | Boat 3 |        | 3   | Sailor 3 |      +-----+-----+-----+
+-----+--------+        +-----+----------+

In the above data when you need to know which boats are not reserved; you can use below query that will give you bid => 3:

SELECT B.bid  FROM Boats B
EXCEPT
SELECT R.bid  FROM Reserves R;

And when you need to know which boats are not (ever) reserved by a sailor; you can use below query that will give you bid => [1, 3] for sid = 3:

SELECT B.bid  FROM Boats B
EXCEPT
SELECT R.bid  FROM Reserves R  WHERE R.[sid] = 3;

And when a sailor reserves all boats the above query will have no result, So NOT EXISTS(<above query>) will be true. Now you can use above query to found sailors that reserves all boats like this:

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]);

So If data of Reserves become something like this:

 [Table: Reserves]
 +-----+-----+-----+
 | rid | bid | sid |
 +-----+-----+-----+
 | 1   | 1   | 1   |
 | 2   | 2   | 1   |
 | 3   | 3   | 1   |
 +-----+-----+-----+

Your query will give a result of Sailor 1 ;).

More info:
EXCEPT returns distinct rows from the left input query that aren’t output by the right input query.
EXISTS: Specifies a subquery to test for the existence of rows.

Upvotes: 1

trust_nickol
trust_nickol

Reputation: 2114

You can transform the sql, the subselect

(SELECT B.bid  
 FROM Boats B)
Except
(SELECT R.bid  
 FROM Reserves R
 WHERE R.sid = S.sid)

should be the same as 

(SELECT B.bid  
 FROM Boats B
 WHERE B.sid <> S.sid)

If you substitute the part in the main sql you get

SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS 
(SELECT B.bid  
 FROM Boats B
 WHERE B.sid <> S.sid)

Select only those Sailors for which no Boats with other sid's exists, which is the same as "sailors who have reserved all boats"

Upvotes: 0

Related Questions