2

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?

  • You can make the subquery a lot faster by selecting only the primary key from the table. You aren't using any of the other data returned by SELECTing *. EXISTS performs a boolean comparison of you subquery (correlated or not). This article explains some of the process in detail. – Adam Zuckerman Feb 22 '14 at 3:09
3

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.

  • 1
    I don't think this makes the intent clearer, but does shed some light for the OP. Exists is pretty explicit in what you're trying to do. To me, a join returns related data from both tables. Using an outer join only to find out what doesn't exist in the other table (and looking for a null) is even more confusing although it is a common technique. When I see exists, I know there are only so many things you could be trying to do. – JeffO Feb 21 '14 at 16:55
  • I guess so. Maybe its personal coding styles. I prefer the join way because for me the intention comes across a lot clearer. Especially when it is a big query. – Rob Aston Feb 21 '14 at 17:24
1

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'.

  • I think the subquery will be run once for each row of outer query, no matter what. – Tulains Córdova Feb 21 '14 at 12:06
  • 2
    @user61852 not with a query optimizer that has at least a small amount of sense... – AakashM Feb 21 '14 at 12:32
  • Almost any query optimizer will see that the can only return 1 data set for the non-correlated subquery. – Adam Zuckerman Feb 22 '14 at 3:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.