Correlated subqueries

A correlated subquery is a subquery that references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement.

A correlated subquery answers a multiple-part question whose answer depends on the value in each row processed by the parent statement. For example, you can use a correlated subquery to determine which employees earn more than the average salaries for their departments. In this case, the correlated subquery specifically computes the average salary for each department.

Syntax

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 table1 t_alias1
SET column =
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
 
DELETE FROM table1 t_alias1
WHERE column operator
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);

Note: Correlated column names in correlated subqueries must be explicitly qualified with parent table name.

Examples

Example A: Simple correlated subquery

The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to emp, the table containing the salary information, and then uses the alias in a correlated subquery:

SELECT deptno, ename, sal
FROM emp x
WHERE sal > (SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno)
ORDER BY deptno; 

Example B: Correlated subquery which returns row values

SELECT *
FROM dept "outer"
WHERE '1997-06-12 02:02:02' IN
(SELECT hiredate
FROM emp
WHERE "outer".deptno = emp.deptno);

Example C: Correlated subquery with atomic return value

Finding deptno with multiple employees:

SELECT *
FROM dept main
WHERE 1 <
(SELECT COUNT(*)
FROM emp
WHERE deptno = main.deptno);

Example D: Correlating a table with itself

SELECT deptno, ename, sal
FROM emp x
WHERE sal >
(SELECT AVG(sal)
FROM emp
WHERE x.deptno = deptno);