skip to main content
Querying data stores with SQL : Supported SQL and Extensions : Subqueries : Correlated Subqueries
  

Try Now
Correlated Subqueries

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

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)