You can use two types of subqueries:
A simple subquery, or noncorrelated subquery, is a subquery that can be evaluated independently of its outer query and is processed only once for the entire statement. All the subqueries in this chapter’s examples so far have been simple subqueries (except Listing 8.6b).
A correlated subquery can’t be evaluated independently of its outer query; it’s an inner query that depends on data from the outer query. A correlated subquery is used if a statement needs to process a table in the inner query for each row in the outer query.
Correlated subqueries have more-complicated syntax and a knottier execution sequence than simple subqueries, but ...