Subqueries inside FROM

Joining Data in SQL

Maham Faisal Khan

Senior Content Developer, DataCamp

Subqueries inside FROM

SELECT continent, MAX(indep_year) AS most_recent
FROM states
GROUP BY continent;

| continent     | most_recent  |
| --------------|------------- |
| Asia          |        1984  |
| Europe        |        1814  |
| Oceania       |        1901  |
| North America |        1776  |
| South America |        1818  |

Focusing on records inside monarchs

SELECT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id

SELECT DISTINCT left_table.id, left_val
FROM left_table, right_table
WHERE left_table.id = right_table.id

Finishing off the subquery

-- Query to return continents with monarchs and the year the most recent country gained independence

SELECT DISTINCT monarchs.continent, sub.most_recent

FROM monarchs,    
    (SELECT 
        continent, 
        MAX(indep_year) AS most_recent
     FROM states
     GROUP BY continent) AS sub
WHERE monarchs.continent = sub.continent     

ORDER BY continent;

| continent   | most_recent  |
|-------------|--------------|
| Asia        |        1984  |
| Europe      |        1814  |

Let's practice!

Joining Data in SQL