User's Guide
   PART 1. Working with Databases
     CHAPTER 7. Using Subqueries       

Outer references

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 product 
WHERE quantity < 2 * (
   SELECT avg (quantity) 
   FROM sales_order_items 
   WHERE product.id = sales_order_items.prod_id)

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 sales_order_items table. But the product.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 product table, it uses the id value of the current row when evaluates the WHERE clause of the subquery.

Description of an outer reference 

The product.id column in this subquery is an example of an outer reference. A subquery that uses an outer reference is called 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.