Difference between Correlated subquery and Nested subquery

Wednesday, 5 December 2012

Difference between Correlated subquery and Nested subquery

Correlated subquery
Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value
 from the row selected by the outer query.
Correlated subquery follows down to top approach i.e., main query is executed first(even though parenthesis      
 are present) and then child query. 
We can also say:In a subquery.
Example:
 select e1.empname, e1.basicsal, e1.deptno from emp e1
 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)


Nested subquery
Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the
 outer query row.
We can also say: in a Correlated subquery,Inner query condition is used in the outer query.
Nested subquery follows top-down approach i.e., child query is executed first and then parent .
Outer query condition is used in the the inner query.
Example:
 select empname, basicsal, deptno from emp
 where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

No comments:

Post a comment