Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
14. Aliases for tables
Other subqueries

Instruction

Wow! That wasn't an easy problem, so congratulations! Now, there may be examples where the same table is used both in the main query and in the correlated subquery. Try to figure out what the following example returns:

SELECT
  *
FROM City AS MainCity
WHERE MainCity.Population > (
  SELECT
    AVG(AverageCity.Population)
  FROM City AS AverageCity    
  WHERE AverageCity.CountryID = MainCity.CountryID
);

In this example, for each country, we want to find cities with a population greater than the average population of all cities in that country. The problem is that we look for cities in the main clause and check the average population value for cities in the subquery. The same table appears twice—no good.

This is why we must use aliases for tables. Take a look: in the subquery, we put

... FROM City AS AverageCity ...

and in the main query, we put:

... FROM City AS MainCity ...

As you can see, we gave two different temporary names (aliases) to the City table, one in the main query and one in the subquery. The alias comes after the table name, separated by a space. Remember, no commas here!

Exercise

For each country, find all information about cities in that country with a rating higher than the average rating of all cities in that country.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM City MainCity
WHERE MainCity.Rating > (
  SELECT
    AVG(AverageCity.Rating)    
  FROM City AverageCity   
  WHERE AverageCity.CountryID = MainCity.CountryID
);