This section extends the subquery work in Section 11.8 by considering correlated and existential subqueries. The subqueries discussed earlier were simple, uncorrelated sub-queries. An uncorrelated subquery is computed once, then replaced by its result, and the outer query is run. In contrast, a correlated subquery relates its search condition to each row of a table named in the outer query and is effectively recomputed for each outer row (see Figure 11.58).

Figure 11.58: A correlated subquery relates its condition to each row of the outer query.

Because of repeated computation, correlated subqueries can sometimes be slow to run. However, they significantly extend the range of queries that can be expressed as a single SQL query and are well worth mastering. Let us refer to the condition inside the correlated subquery as the correlation condition. This condition typically takes the form

a Q b

where a is an inner column (listed in the from clause of the subquery), b is an outer column (listed in the from clause of the outer query), and Q is a comparator (e.g., =, >).

To avoid ambiguity, the column names for a or b may need to be qualified by a table name. The three basic cases are summarized in Table 11.18. Since local scope is assumed by default, we may normally leave a unqualified (unless it occurs in two inner tables). However, if a