Purpose

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

This statement returns data about employees whose salaries exceed their department average. It 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 example specifies 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 example finds the department number (deptno) with multiple employees:

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

Example D

This example correlates a table with itself:

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