Answer: 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.
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 runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.
Example:
select empname, basicsal, deptno from emp
where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)
Found interesting? Add this to: