Correlated Subquery

A Correlated subquery is a subquery which is evaluated once for each row processed through the parent statement. For instance, let us display the information of employees whose salary is greater than the average salary of their own department.
The first query displays the deptno along with the average salary.
SELECT AVG(SAL),DEPTNO FROM EMP GROUP BY DEPTNO;

AVG(SAL)    DEPTNO
2916.6667      10
2175              20
1566.6667      30

The subsequent query displays the records where the matching condition is satisfied.

Example

SELECT EMPNO,ENAME,DEPTNO,SAL FROM EMP B WHERE SAL >=
(SELECT AVG(SAL) FROM EMP A WHERE A.DEPTNO=B.DEPTNO GROUP BY DEPTNO);
The execution works in the following manner:

1.    The department number is obtained.
2.    The department number is then equated with the Parent Statement
3.    If that row’s salary is greater than the average salary that particular row is returned.

EMPNO      ENAME    DEPTNO    SAL
7839           KING        10        5000
7698           BLAKE      30        2850
7566           JONES      20        2975
7499           ALLEN      30        1600
7902           FORD       20        3000
7788           SCOTT     20        3000

For every parent record selected the inner-query is executed. The subsequent example shows the nth maximum salary: Example 11.9
SELECT SAL FROM EMP A WHERE &N = (SELECT COUNT(DISTINCT(SAL)) FROM EMP WHERE A.SAL <=SAL);

The output will be: Enter value for n: 1

old    1: SELECT SAL FROM EMP A WHERE &N =
new    1: SELECT SAL FROM EMP A WHERE 1 =

SAL
5000