What is Correlated Subquery ?



Share on Google+Share on Google+

hemant kumar
What is Correlated Subquery ?
1 Answer(s)      6 years and 10 months ago
Posted in : SQL

What is Correlated Subquery ?

View Answers

November 20, 2010 at 11:24 AM


A query is called correlated subquery when both the inner query and the outer query are interdependent. For every row processed by the inner query, the outer query is processed as well. The inner query depends on the outer query before it can be processed.

The correlated subquery makes an actual reference, using a correlation variable, to the outer query. For example, here's a query with two subqueries:

    select stu_name , stu_mark , ( select avg(stu_mark)
 from students where class = t1.class ) as classaverage , 
( select avg(stu_mark) from students ) as schoolaverage from students t1

In the above example, t1 is the correlation variable that lets the correlated subquery refer to the table in the outer query. For each student, two averages are calculated: the average mark of all students in the same class, and the overall average of all students in all classes.