Correlated Subquery

A correlated subquery is a type of nested subquery that uses columns from the outer query in its WHERE clause.

For example, a query to list employees whose salary is more than their department’s average:

SQL> select oe.deptno, oe.ename, oe.sal
  2  from   emp oe
  3  where  sal >
  4            -- Correlated subquery starts
  5               (select avg(ie.sal) from emp ie
  6                where ie.deptno = oe.deptno)
  7            -- Correlated subquery ends
  8  order by oe.deptno;
    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 JONES            2975
        20 SCOTT            3000
        20 FORD             3000
        30 ALLEN            1600
        30 BLAKE            2850
6 rows selected.


In this query, the inner query is a correlated subquery: it references department number of the outer query. The outer query uses the correlated subquery to calculate the average salary for each department.
While a simple subquery is evaluated only once for each table, a correlated subquery is evaluated once for each row.

The outer query (also called parent query) can be a SELECT, UPDATE or DELETE.

Syntax of the Correlated Subquery
SELECT
SELECT select_list
    FROM table1 t_alias1
    WHERE expr operator
        (SELECT column_list
            FROM table2 t_alias2
            WHERE t_alias1.column
               operator t_alias2.column);

UPDATE
UPDATE table1 t_alias1
    SET column =
        (SELECT expr
            FROM table2 t_alias2
            WHERE t_alias1.column = t_alias2.column);

DELETE
DELETE FROM table1 t_alias1
    WHERE column operator
        (SELECT expr
            FROM table2 t_alias2
            WHERE t_alias1.column = t_alias2.column);