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 in which the 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.

SELECT select_list

FROM table1 t_alias1

WHERE expr rel_operator

(SELECT column_list

FROM table2t_alias2

WHERE t_alias1.columnrel_operatort_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 rel_operator

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column)

FROM table1 t_alias1

WHERE expr rel_operator

(SELECT column_list

FROM table2t_alias2

WHERE t_alias1.columnrel_operatort_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 rel_operator

(SELECT expr

FROM table2 t_alias2

WHERE t_alias1.column = t_alias2.column)

Correlated column names in correlated subqueries must be explicitly qualified with the table name of the parent.

The following statement returns data about employees whose salaries exceed their department average. This 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

(SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)

ORDER BY deptno

This is an example of a correlated subquery that returns row values:

SELECT * FROM dept "outer" WHERE 'manager' IN

(SELECT managername FROM emp

WHERE "outer".deptno = emp.deptno)

(SELECT managername FROM emp

WHERE "outer".deptno = emp.deptno)

This is an example of finding the department number (deptno) with multiple employees:

SELECT * FROM dept main WHERE 1 <

(SELECT COUNT(*) FROM emp WHERE deptno = main.deptno)

(SELECT COUNT(*) FROM emp WHERE deptno = main.deptno)

This is an example of correlating a table with itself:

SELECT deptno, ename, sal FROM emp x WHERE sal >

(SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)

(SELECT AVG(sal) FROM emp WHERE x.deptno = deptno)