Difference between revisions of "SELECT .. SELECT"
From SQLZOO
| Line 11: | Line 11: | ||
gdp/population AS gdp_per_capita | gdp/population AS gdp_per_capita | ||
FROM bbc) X | FROM bbc) X | ||
| − | WHERE | + | WHERE gdp_per_capita>20000 |
</source> | </source> | ||
Notice that | Notice that | ||
| Line 18: | Line 18: | ||
*the second column in the inner query has an alias | *the second column in the inner query has an alias | ||
</div> | </div> | ||
| + | |||
| + | <div class='qu'> | ||
| + | <p class='imper'>Find the countries in the same region as Butan</p> | ||
| + | You may use a SELECT statement in the WHERE line - this returns a list of regions. | ||
| + | |||
| + | <source lang='sql' class='def'> | ||
| + | SELECT name | ||
| + | FROM bbc | ||
| + | WHERE region IN | ||
| + | (SELECT region FROM bbc | ||
| + | WHERE name='Butan') | ||
| + | </source> | ||
| + | </div> | ||
| + | |||
<div>See also:</div> | <div>See also:</div> | ||
<ul> | <ul> | ||
Revision as of 00:03, 9 August 2012
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 Butan
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='Butan')
See also:
