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);
