Within the body of a subquery, it is often necessary to refer to the value of a column in the active row of the main query. Consider the following query:
SELECT Name, Description FROM Products WHERE Quantity < 2 * ( SELECT AVG( Quantity ) FROM SalesOrderItems WHERE Products.ID = SalesOrderItems.ProductID );
This query extracts the names and descriptions of the products whose in-stock quantities are less than double the average ordered quantity of that product—specifically, the product being tested by the WHERE clause in the main query. The subquery does this by scanning the SalesOrderItems table. But the Products.ID column in the WHERE clause of the subquery refers to a column in the table named in the FROM clause of the main query—not the subquery. As SQL moves through each row of the Products table, it uses the ID value of the current row when it evaluates the WHERE clause of the subquery.
The Products.ID column in this subquery is an example of an outer reference. A subquery that uses an outer reference is a correlated subquery. An outer reference is a column name that does not refer to any of the columns in any of the tables in the FROM clause of the subquery. Instead, the column name refers to a column of a table specified in the FROM clause of the main query. As the above example shows, the value of a column in an outer reference comes from the row currently being tested by the main query.