Sql Excel : Correlated Subqueries

A correlated subquery occurs when the subquery includes a reference to the outer
query. An example shows this best. Consider the following question: Which zip
code in each state has the maximum population and what is the population? One
way to approach this problem uses a correlated subquery:
SELECT zc.stab, zc.zcta5, zc.totpop
FROM ZipCensus zc
WHERE zc.totpop = (SELECT MAX(zcinner.totpop)
FROM ZipCensus zcinner
WHERE zcinner.stab = zc.stab
)
ORDER BY zc.stab
The “correlated” part of the subquery is the inner WHERE clause, which specifies
that the state in a record processed by the subquery must match the state in the
outer table.
Conceptually, the database engine reads one row from zc (the table referenced in
the outer query). Then, the engine finds all rows in zcinner that match this state.
From these rows, it calculates the maximum population. If the original row
matches this maximum, it is selected. The engine then moves on to the next row in
the outer query.
Correlated subqueries are sometimes cumbersome to understand. Although
complicated, correlated subqueries are not a new way of processing the data; they
are another example of joins. The following query produces the same results:
SELECT zc.stab, zc.zcta5, zc.totpop
FROM ZipCensus zc JOIN
(SELECT zc.stab, MAX(zc.totpop) as maxpop
FROM ZipCensus zc
GROUP BY zc.stab) zcsum
ON zc.stab = zcsum.stab AND
zc.totpop = zcsum.maxpop
ORDER BY zc.stab

This query makes it clear that ZipCensus is summarized by stab to calculate the
maximum population. The JOIN then finds the zip code (or possibly zip codes) that
matches the maximum population, returning information about them. In
addition, this method makes it possible to include other information, such as the
number of zip codes where the maximum population is achieved. This can be
calculated using COUNT(*)in zcsum.
The examples throughout this book tend not to use correlated subqueries for
SELECT queries, preferring explicit JOINs instead. Joins provide more flexibility for
processing and analyzing data and, in general, SQL engines do a good job of
optimizing JOINs. There are some situations where the correlated subquery may
offer better performance than the corresponding JOIN query or may even be
simpler to understand.