Correlated Subquery

Correlated subquery references the outer query in the subqueries WHERE clause. Internally, correlated subqueries are very expensive to process because the inner query must be executed for every row returned by the outer query.

A correlated subquery references one or more columns in the outer query.

The following subquery is known as a correlated subquery because the subquery is related to the outer query.

SQL> SELECT empno, mgr,ename, sal
  2  FROM emp outer
  3  WHERE sal >
  4    (SELECT AVG(sal)
  5     FROM emp inner
  6     WHERE inner.empno = outer.mgr);

     EMPNO        MGR ENAME             SAL
---------- ---------- ---------- ----------
      7788       7566 SCOTT            3000
      7902       7566 FORD             3000

SQL>

Using EXISTS with a Correlated Subquery

SQL> --Using EXISTS with a Correlated Subquery
SQL>
SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE EXISTS
  4    (SELECT empno
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

     EMPNO ENAME
---------- ----------
      7902 FORD
      7698 BLAKE
      7839 KING
      7566 JONES
      7788 SCOTT
      7782 CLARK

6 rows selected.

SQL>

Using NOT EXISTS with a Correlated Subquery

SQL> SELECT empno, ename
  2  FROM emp outer
  3  WHERE NOT EXISTS
  4    (SELECT 1
  5     FROM emp inner
  6     WHERE inner.mgr = outer.empno);

     EMPNO ENAME
---------- ----------
      7844 TURNER
      7521 WARD
      7654 MARTIN
      7499 ALLEN
      7934 MILLER
      7369 SMITH
      7876 ADAMS
      7900 JAMES

8 rows selected.