Difference between revisions of "SELECT .. SELECT"
From SQLZOO
| (2 intermediate revisions by one user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | __NOTOC__ | |
| + | ==Subquery Table, Derived Tables, Nested Queries== | ||
<p>You can use the results from one query in another query</p> | <p>You can use the results from one query in another query</p> | ||
==Subquery with FROM== | ==Subquery with FROM== | ||
| Line 36: | Line 37: | ||
==Correlated Subquery== | ==Correlated Subquery== | ||
| − | <div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery". | + | <div class='qu'> |
| − | <p class='imper'>Show the countries where the population is greater than average for its region</p> | + | <p>If a value from the outer query appears in the inner query this is "correlated subquery".</p> |
| + | <p class='imper'>Show the countries where the population is greater than 5 times the average for its region</p> | ||
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
SELECT name | SELECT name | ||
FROM bbc b1 | FROM bbc b1 | ||
| − | WHERE population> | + | WHERE population> |
| − | (SELECT AVG(population) FROM bbc | + | 5*(SELECT AVG(population) FROM bbc |
| − | + | WHERE region=b1.region) | |
</source> | </source> | ||
</div> | </div> | ||
Revision as of 00:33, 9 August 2012
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, 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
Subquery with IN
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')
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)
See also:
