Difference Between Correlated Subquery and Nested Subquery

1. 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 doe not contain any reference to the outer query row.

2. Correlated subquery follows down to top approach i.e main query executed first (event though parenthesis are present) and then child query.

We can save, inner query condition is used in the outer query.

Nested subquery follows top-down approach i.e child query executed first and then parent query.

We can say Outer query condition is used in the inner query.

3. Example of Co-related Query:

select e1.emplname,e1.salary, e1.deptno from emp e1

where  e1.salary =(select max(salary) from emp e2 where e2.deptno= e2.deptno)

Example of Subquery:
select empname,salary,deptno from emp where (deptno,salary) in (select deptno,max(salary) from emp group by deptno)