Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Very simple subqueries
Subqueries with multiple results
Correlated subqueries
Other subqueries
19. Subqueries in the FROM clause

Instruction

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.

SELECT *
FROM
  city,
  (
    SELECT *
    FROM country
    WHERE area < 1000
  ) AS small_country
WHERE small_country.id = city.country_id;

The above query finds cities from small countries. Of course, there is no table small_country in our database, so... we create it "on the fly" with a subquery in the FROM clause. Of course, we need a name for it, so we use an alias with the keyword AS. In the end, the query shows cities together with their countries, provided that the country has an area below 1,000. Remember how selecting from two tables works? We need the condition in the WHERE clause, because otherwise each city would be shown together with every possible country.

Exercise

Show mountains together with their countries. The countries must have at least 50,000 residents.

Stuck? Here's a hint!

Type:

SELECT *
FROM
  mountain,
  (
    SELECT
      *
    FROM country
    WHERE population >= 50000
  ) AS crowded_country
WHERE crowded_country.id = mountain.country_id;