ASA SQL User's Guide
  Using Subqueries
    How subqueries work

Correlated subqueries

In a simple query, the database server evaluates and processes the query's WHERE clause once for each row of the query. Sometimes, though, the subquery returns only one result, making it unnecessary for the database server to evaluate it more than once for the entire result set.

Uncorrelated subqueries 

Consider this query:

SELECT name, description
FROM product
WHERE quantity <  2 * (
   SELECT avg(quantity)
   FROM sales_order_items)

In this example, the subquery calculates exactly one value: the average quantity from the sales_order_items table. In evaluating the query, the database server computes this value once, and compares each value in the quantity field of the product table to it to determine whether to select the corresponding row.

Correlated subqueries 

When a subquery contains an outer reference, you cannot use this shortcut. For instance, the subquery in the query

SELECT name, description
FROM product
WHERE quantity < 2 * (
   SELECT avg(quantity)
   FROM sales_order_items
   WHERE product.id=sales_order_items.prod_id)

returns a value dependent upon the active row in the product table. Such a subquery is called a correlated subquery. In these cases, the subquery might return a different value for each row of the outer query, making it necessary for the database server to perform more than one evaluation.