What is Correlated Subquery ?
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.