There are several problems with Query 1.
First, why does it have trailing
))? Either they shouldn’t be there, or there are some missing
Second, the placement of the
EXCEPT is wrong.
The example is this…
SELECT sname FROM ... WHERE NOT EXISTS (...) EXCEPT SELECT bid FROM ...
But it appears that, for a few reasons, it wants to be this…
SELECT sname FROM ... WHERE NOT EXISTS (SELECT bid FROM ... EXCEPT SELECT bid FROM ... )
(It is currently “excepting” from the main query, not from the sub-query in the WHERE clause. Moving the
EXCEPT inside the brackets fixes that.)
Finaly, even if that was fixed, it still wouldn’t help. The sub-query would then become…
NOT EXISTS (SELECT bid FROM boats B EXCEPT SELECT bid FROM reservations R WHERE R.bid = B.bid AND R.sid = S.sid )
Unfortunately, one query in the
EXCEPT can’t reference the other.
R.sid = S.sid is fine, because that’s an inner query referencing and outer query. (Those are known as “correlated” sub-queries – in one sense the whole correlated sub-query is executed repeatedly, once for each row in the outer query.)
R.bid = B.bid is “jibberish” because the rows in
reservations are being processed entirely independently from the rows in