Correlated Subqueries
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.
Syntax
SELECT outer_column
FROM outer_table
WHERE outer_column_value IN
(SELECT inner_column FROM inner_table
WHERE inner_column = outer_column)
Remarks
• In the syntax above, outer_column is called the correlation variable, because it references the outer query from the inner query.
• A correlated subquery is used if a statement needs to process a table in the inner query for each row in the outer query.
• A correlated subquery cannot be evaluated independent of its outer query. The inner query is dependent on the data from the outer query.
• Correlated subqueries differ from simple queries because of their order of execution and the number of times they are executed. A correlated subquery is executed repeatedly, once for each candidate row selected by the outer query. It always refers to the table mentioned in the FROM clause of the outer query.
Example
The query lists the managers who are over 40 and who manage a sales person who is over quota and who does not work in the same sales office as the manager.
SELECT name
FROM salesreps mgrs
WHERE age > 40 AND mgrs.EMP_NO IN
(SELECT manager
FROM salesreps emps
WHERE emps.quota > emps.sales
AND emps.rep_office <> mgrs.rep_office)