Saturday, November 7, 2009

difference between Co-related sub query and nested sub query

• What is difference between Co-related sub query and nested sub query?
• 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.
• Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
• For example,
• Correlated Subquery:
• 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:
• select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

No comments:

Post a Comment