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?

Improve this question
3
  • 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
    Commented Jan 2, 2016 at 19:12
  • 1
    @zgguy - You seem to have typed that into the wrong box. Seems more answer than comment.
    – Martin Smith
    Commented Jan 2, 2016 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.
    – user1822
    Commented Jan 2, 2016 at 20:12

1 Answer 1

Reset to default
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
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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