SELECT .. SELECT
Jump to navigation
Jump to search
Subquery Table, Derived Tables, Nested Queries
You can use the results from one query in another query
You may use a SELECT statement in the FROM line SELECT f FROM (SELECT ).
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, ROUND(gdp_per_capita)
FROM
(SELECT name,
gdp/population AS gdp_per_capita
FROM bbc) 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
Find the countries in the same region as Bhutan
You may use a SELECT statement in the WHERE line - this returns a list of regions.
SELECT name
FROM bbc
WHERE region IN
(SELECT region FROM bbc
WHERE name='Bhutan')
See also: