Difference between revisions of "SELECT .. SELECT"
m (Added more <code> tags) |
|||
(8 intermediate revisions by one other 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== | ||
<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> | + | |
− | <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 <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) | ||
FROM | FROM | ||
− | (SELECT name | + | (SELECT name, |
gdp/population AS gdp_per_capita | gdp/population AS gdp_per_capita | ||
FROM bbc) X | FROM bbc) X | ||
− | WHERE region=' | + | WHERE gdp_per_capita>20000 |
+ | </source> | ||
+ | Notice that | ||
+ | *the inner table is given an alias <code>X</code> | ||
+ | *the first column in the inner query keeps its name | ||
+ | *the second column in the inner query has an alias | ||
+ | </div> | ||
+ | |||
+ | ==Subquery with IN== | ||
+ | <div class='qu'> | ||
+ | <p class='imper'>Find the countries in the same region as Bhutan</p> | ||
+ | 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'> | ||
+ | SELECT name | ||
+ | FROM bbc | ||
+ | WHERE region IN | ||
+ | (SELECT region FROM bbc | ||
+ | WHERE name='Bhutan') | ||
</source> | </source> | ||
</div> | </div> | ||
− | < | + | ==Correlated Subquery== |
− | <p> | + | <div class='qu'> |
− | + | <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'> | ||
+ | SELECT name | ||
+ | FROM bbc b1 | ||
+ | WHERE population> | ||
+ | 5*(SELECT AVG(population) FROM bbc | ||
+ | WHERE region=b1.region) | ||
+ | </source> | ||
+ | </div> | ||
+ | |||
+ | |||
<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> |
Revision as of 04:44, 29 December 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: