Difference between revisions of "SELECT .. SELECT"
From SQLZOO
| Line 1: | Line 1: | ||
| − | + | <h3>Subquery Table, Derived Tables, Nested Queries</h3> | |
<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> | ||
<div class='qu'> | <div class='qu'> | ||
| − | You may use a SELECT statement in the FROM line. | + | You may use a SELECT statement in the FROM line SELECT f FROM (SELECT ). |
In this case the derived table X has columns <code>name</code> and <code>gdp_per_capita</code>. | In this case the derived table X 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 SELECT can be used in the outer SELECT. | ||
| Line 13: | Line 13: | ||
WHERE region='North America' | WHERE region='North America' | ||
</source> | </source> | ||
| + | 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 | ||
</div> | </div> | ||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
<div>See also:</div> | <div>See also:</div> | ||
<ul> | <ul> | ||
Revision as of 23:57, 8 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 region='North America'
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
See also:
