Difference between revisions of "SELECT .. SELECT"
From SQLZOO
m (Added more <code> tags) |
|||
| Line 4: | Line 4: | ||
==Subquery with FROM== | ==Subquery with FROM== | ||
<div class='qu'> | <div class='qu'> | ||
| − | You may use a SELECT statement in the FROM line. | + | You may use a <code>SELECT</code> statement in the <code>FROM</code> line. |
| − | In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>. | + | In this case the derived table <code>X</code> has columns <code>name</code> and <code>gdp_per_capita</code>. |
| − | The calculated values in the inner SELECT can be used in the outer SELECT. | + | The calculated values in the inner <code>SELECT</code> can be used in the outer <code>SELECT</code>. |
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
SELECT name, ROUND(gdp_per_capita) | SELECT name, ROUND(gdp_per_capita) | ||
| Line 17: | Line 17: | ||
</source> | </source> | ||
Notice that | Notice that | ||
| − | *the inner table is given an alias X | + | *the inner table is given an alias <code>X</code> |
*the first column in the inner query keeps its name | *the first column in the inner query keeps its name | ||
*the second column in the inner query has an alias | *the second column in the inner query has an alias | ||
| Line 25: | Line 25: | ||
<div class='qu'> | <div class='qu'> | ||
<p class='imper'>Find the countries in the same region as Bhutan</p> | <p class='imper'>Find the countries in the same region as Bhutan</p> | ||
| − | You may use a SELECT statement in the WHERE line - this returns a list of regions. | + | You may use a <code>SELECT</code> statement in the <code>WHERE</code> line - this returns a list of regions. |
<source lang='sql' class='def'> | <source lang='sql' class='def'> | ||
| Line 53: | Line 53: | ||
<div>See also:</div> | <div>See also:</div> | ||
<ul> | <ul> | ||
| − | <li>[ | + | <li>[[SELECT within SELECT Tutorial]]</li> |
<li>[[SELECT_.._JOIN |SELECT JOIN]]</li> | <li>[[SELECT_.._JOIN |SELECT JOIN]]</li> | ||
</ul> | </ul> | ||
Latest revision as of 04:44, 29 December 2012
[edit] Subquery Table, Derived Tables, Nested Queries
You can use the results from one query in another query
[edit] 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
[edit] 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')
[edit]
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:
