SELECT .. SELECT

Language:Project:Language policy	English • 日本語

Subquery Table, Derived Tables, Nested Queries

You can use the results from one query in another query

Subquery with FROM

You may use a SELECT statement in the FROM line.

In this case the derived table X has columns name and gdp_per_capita. The calculated values in the inner SELECT can be used in the outer SELECT.

SELECT name, gdp_per_capita
  FROM
  (SELECT name,
          gdp/population AS gdp_per_capita
     FROM world) X
 WHERE gdp_per_capita>20000

Notice that

the inner table is given an alias X
the first column in the inner query keeps its name
the second column in the inner query has an alias

Subquery with IN

Find the countries in the same continent as Bhutan

You may use a SELECT statement in the WHERE line - this returns a list of continent.

SELECT name
  FROM world
 WHERE continent IN
  (SELECT continent FROM world
    WHERE name='Bhutan')

Correlated Subquery

If a value from the outer query appears in the inner query this is "correlated subquery".

Show the countries where the population is greater than 5 times the average for its region

SELECT name
  FROM bbc b1
 WHERE population> 
  5*(SELECT AVG(population) FROM bbc
     WHERE region=b1.region)