A subquery is correlated when it references columns of outer tables in an enclosing or containing outer query.
The expression correlated subquery comes from the explicit requirement for the use of correlation names in any correlated subquery in which the same table is referenced in both the internal and external query.
The reference from the inner query to its containing query is shown in this illustration.
When the inner table references a foreign key column of a different table, the column must be fully qualified in the interior WHERE clause.
The effect of a correlated subquery is to provide an implicit loop function within any standard SQL DML statement, including ABORT, DELETE, INSERT, and UPDATE.
A reference to variables in an outer expression by a variable in an inner expression is called an outer reference.
| IF a scalar subquery references tables in its containing query that are … | THEN it is said to be … |
|---|---|
| not referenced in its own FROM clause | correlated. |
| referenced in its own FROM clause | noncorrelated. |
If a column reference is made from an inner query to an outer query, then it must be fully qualified with the appropriate table name from the FROM clause of the outer query.
ANSI Compliance
Correlated subqueries are ANSI SQL:2011-compliant.