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.
Syntax
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)
Notes
Correlated column names in correlated subqueries must be explicitly qualified with the table name of the parent.
Example A
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
Example B
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)
Example C
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)
Example D
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)
Correlated column names in correlated subqueries must be explicitly qualified with the table name of the parent.