MadeEasy Test Series: How many tuples will be present in the output of the following SQL query.

Question

 

---

My doubt here is, if NOT EXISTS gets an empty set as the input then every tuple of the table in the outer query must satisfy the condition. Am I right?

For example, in the above question  – 

The inner query :

((Select B.Bid FROM Boats B

WHERE B.color <> ‘Red’) 

EXCEPT 

(Select R.Bis FROM Reserves R

WHERE R.Sid = S.Sid))

The output of this query will be a null set.

Now, when we execute the outer query, it should print the names of all the sailors (including duplicates), right?

So the answer should be 10. But in the test, they are saying the answer is 1. (Providing a snippet of the same below)
Can anyone please explain whether I am approaching this right, or if I am missing something?

---

Answer 1

Your concept regarding NOT Exist is correct, but here the inner subquery will not be empty for any sid except sid=22(Justin)
For eg consider sid=31 inner subquery returns {101,103}-{102,103,104}={101}
similarly for sid=64 inner subquery returns {101,103}-{101,102}={103}
sid=22 inner subquery returns {101,103}-{101,102,103}={}
Analysis of inner subquery
For any sid ‘x’,

[ ((Select B.Bid FROM Boats B

WHERE B.color <> ‘Red’) ----bid of non red boats
EXCEPT 
(Select R.Bis FROM Reserves R
WHERE R.Sid = S.Sid)) -------bid of boats reserved by x ] ie

Bid of non red boats – bid of boats reserved by x=Bid of non red boats not reserved by x,

So,for any sid s, the inner subquery returns Bid of non red boats not reserved by x
And as per the qstn this innery query should be empty(that is, if there isn’t be any bid of non red boats not reserved by x), then that sid x will come in output, which in turn means x must reserve all non red boats
So sid=22 only satisfies the condn, hence only 1 tuple will come in the output

Comments

@DEBANJAN DAS2k Got it, thanks! :D

---

Yes, the inner (final) query will execute tuple by tuple because it is dependent on the outer one, so first we execute outer query for one tuple then we execute the final query, however the middle query is executed only once. Am I correct here?

---

no, this is a correlated subquery, for EACH tuple of the table considered in outer query,the entire inner query will be executed,but yes the result of middle query(by which i think u meant finding bid of non red boats) will be unchanged
so correlated subquery executes tuple by tuple