Just finished Stanford lecture on SQL (by Prof. Jennifer Widom). However I have developed a confusion regarding the use of EXISTS clause. I thought it is just like a condition and an expression so that if it’s true, the above query proceeds (much like the Boolean AND). So having a slight doubt regarding this question:
Passenger = {pid, pname, Age}
Reservation = {pid, class, tid}
and tables are populated with some data and following query is executed:
SELECT pid
FROM Reservation
WHERE class = 'AC' AND EXISTS
(SELECT * FROM Passenger WHERE age > 65 AND Passenger.pid = Reservation.pid)
Now the thing that is getting me troubled is that I thought that the use of EXISTS is simply that the above main query proceeds if the subquery returns something. So as the subquery was returning something, I expected the query to return all PID’s where class = 'AC'. I didn’t think that it was executed tuple by tuple. So how to remove this confusion?
1
In this particular scenario, I would consider using a join on the Passenger table and a where clause to filter the results. Something like the below:
SELECT pid
FROM Reservation
INNER JOIN Passenger
ON Passenger.pid = Reservation.pid
WHERE class = 'AC' and age > 65
For me this is a clearer version and it is easier to understand what the query is actually doing.
The exists is operating on all rows in Reservation and checking whether they meet the exists query. For me this looks confusing and can be quicker to join directly on the table using the ids and filtering where necessary.
2
Because it refers to an object in the outer query (Reservation), the subquery is a correlated subquery. As it says in that wikipedia article,
The subquery is evaluated once for each row processed by the outer query
If the subquery weren’t correlated, your reasoning would be correct. For example, hypothetically,
SELECT pid
FROM Reservation
WHERE class = 'AC' AND EXISTS
(SELECT * FROM Passenger WHERE age > 65)
would, so long as there was at least one passenger over 65, return all Reservations with class='AC'.
3