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?

Improve this question
1
  • 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. Commented Feb 22, 2014 at 3:09

2 Answers 2

Reset to default
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.

Improve this answer
2
  • 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. Commented Feb 21, 2014 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. Commented Feb 21, 2014 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'.

Improve this answer
3
  • I think the subquery will be run once for each row of outer query, no matter what. Commented Feb 21, 2014 at 12:06
  • 2
    @user61852 not with a query optimizer that has at least a small amount of sense... Commented Feb 21, 2014 at 12:32
  • Almost any query optimizer will see that the can only return 1 data set for the non-correlated subquery. Commented Feb 22, 2014 at 3:00

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.