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.

A correlated Oracle subquery is evaluated once FOR EACH ROW as opposed to a normal subquery which is evaluated only once for each table.

You can reference the outer query inside the correlated subquery using an alias which makes it so handy to use.

SQL> select ename ,sal ,deptno from emp a where
a.sal < (select avg(sal) from emp b
where a.deptno = b.deptno)
order by deptno;

ENAME SAL DEPTNO
---------- ---------- ----------
CLARK 2450 10
MILLER 1300 10
SMITH 800 20
ADAMS 1100 20
WARD 1250 30
MARTIN 1250 30
TURNER 1500 30
JAMES 950 30

8 rows selected.




NOTE:
1) You can nest as many queries you want but it is recommended not to nest more than 16 subqueries in oracle.
2) If a subquery is not dependent on the outer query it is called a non-correlated subquery.