13.3. Correlated Subqueries

Subqueries can be non-correlated or correlated:

• A non-correlated subquery contains no references to the outer query and is not dependent on it. As a result, a non-correlated subquery could be evaluated as a completely separate statement.

• A correlated subquery contains references to the values in the outer query and cannot be evaluated independently of it.

In the following correlated subquery, we calculate which country on each populated continent has the largest population. The value of the column Continent, which appears in the outer query, is used to limit which rows to consider for the MAX() calculation in the subquery:

 mysql> SELECT Continent, Name, Population     -> FROM Country c     -> WHERE Population = (SELECT MAX(Population)     ->                     FROM Country c2     ->                     WHERE c.Continent=c2.Continent     ->                     AND Population > 0     ->                    ); +---------------+--------------------+------------+ | Continent     | Name               | Population | +---------------+--------------------+------------+ | Oceania       | Australia          |   18886000 | | South America | Brazil             |  170115000 | | Asia          | China              | 1277558000 | | Africa        | Nigeria            |  111506000 | | Europe        | Russian Federation |  146934000 | | North America | United States      |  278357000 | +---------------+--------------------+------------+ 

Note how the table qualifiers c and c2 are used in the example. This is necessary because the columns that are used to correlate values from the inner and outer queries come from different references to the same table and thus have the same name.