Correlated names and references
A correlated reference can appear in a search condition in a subquery. The reference is always in the form of X.C, where X is a correlation name and C is the name of a column in the table that X represents.
You can define a correlation name for any table appearing in a FROM clause. A correlation name provides a unique name for a table in a query. The same table name can be used many times within a query and its nested subselects. Specifying different correlation names for each table reference makes it possible to uniquely designate which table a column refers to.
The correlation name is defined in the FROM clause of a query. This query can be the outer-level SELECT, or any of the subqueries that contain the one with the reference. Suppose, for example, that a query contains subqueries A, B, and C, and that A contains B and B contains C. Then a correlation name used in C can be defined in B, A, or the outer-level SELECT. To define a correlation name, include the correlation name after the table name. Leave one or more blanks between a table name and its correlation name, and place a comma after the correlation name if it is followed by another table name. The following FROM clause defines the correlation names TA and TB for the tables TABLEA and TABLEB, and no correlation name for the table TABLEC.
FROM TABLEA TA, TABLEC, TABLEB TBAny number of correlated references can appear in a subquery. For example, one correlated name in a search condition can be defined in the outer-level SELECT, while another can be defined in a containing subquery.
Before the subquery is executed, a value from the referenced column is always substituted for the correlated reference.
Parent topic:
Correlated subqueries