SELECT .. SELECT

Subquery Table, Derived Tables, Nested Queries

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.

SELECTname,gdp_per_capita
FROM
(SELECTname,
gdp/populationASgdp_per_capita
FROMworld)X
WHEREgdp_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.

SELECTname
FROMworld
WHEREcontinentIN
(SELECTcontinentFROMworld
WHEREname='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

SELECTname
FROMbbcb1
WHEREpopulation>
5*(SELECTAVG(population)FROMbbc
WHEREregion=b1.region)