Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
Line 1: Line 1:
<h3>The Derived Table (SELECT FROM SELECT)</h3>
+
<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>
 
<h2>What about ties?</h2>
 
<p>If one region has two countries with the same, highest population then the query will
 
produce the right answers but there will be duplication- both countries will be shown.
 
</p>
 
 
<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:
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense