Practical SQL Handbook, The: Using SQL Variants, Fourth Edition

Correlated Subquery Processing

Correlated subqueries are not as neat, but they are very valuable (they can handle problems you can't easily approach with joins or simple subqueries). For now, just understand the syntax, and get an idea of how correlated subqueries work. As you experiment, you'll become comfortable with them.

In the correlated subquery, the inner query cannot be evaluated independently: It references the outer query and is executed once for each qualified row in the outer query. In the example in Figure 8.2, the outer table (publishers) has three rows, so the inner query will run three times.

Conceptually, the processing follows these steps:

1.	The outer query finds the first name in the publishers table (for example, New Age ...