0

I am unable to understand how this correlated subquery works on the relation Student={Sid,Sname,marks}

Select Sname from Student s1
where (select count (*) from Student s2 where s1.marks<=s2.marks)=3

I cannot understand the way in which I should comprehend this query starting from inner query. The count (*) returns the total count of rows in a group but here is it calculating for individual condition satisfied.

Can anyone help in this regard?

  • 1
    The inner query (correlated subquery) gets executed once for each row processed by the outer query. So, for each row of the Student table (s1), the subquery gets executed and it calculates the count of rows from Student table (s2) whose "marks" column has value greater than or equal to the value of the currently processed row from the outer query. Finally, the outer query's WHERE clause throws away all rows except those for which the inner query returns 3. – zgguy Jan 2 '16 at 19:12
  • 1
    @zgguy - You seem to have typed that into the wrong box. Seems more answer than comment. – Martin Smith Jan 2 '16 at 20:03
  • 1
    @zgguy: it is actually a complicated way of doing a running total - which could be done much easier using a window function. – a_horse_with_no_name Jan 2 '16 at 20:12
2

zgguy's explanation is very good. It is also illustrated nicely in the execution plan (I have used SSMS2014 to generate one):

execution plan

  • the inner query shows on the second "row"
  • nested loops means that for each row in the outer query, inner input in scanned and matching records are selected
  • filter (=3) is applied on records from previous step

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.