Good job! Now, queries can also be used in other places. We can, for example, use a subquery instead of a table in the
FROM clause. Take a look:
INNER JOIN (
WHERE Country.Area < 1000
) AS SmallCountry
ON SmallCountry.ID = City.CountryID;
The above query returns information about the cities of small countries. Of course, there is no
SmallCountry table in our database, so... we create it 'on the fly' with a subquery in the
JOIN clause. Of course, we need a name for the table the subquery returns so that we may reference it in the main query, so we use an alias with the
AS keyword. In the end, the query shows cities together with their countries, provided that each country has an area of less than 1000.
Remember how joining two tables works? We need the condition in the
ON clause. Of course, we could put two tables in the
FROM clause, but the current layout is more elegant.