Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to navigation Jump to search
Line 3: Line 3:
<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.
In this example the table is <code>beast</code> and the columns are <code>id</code> and <code>legs</code>.
In this case the derived table X has columns <code>name</code>,
<code>region</code> and <code>gdp_per_capita</code>
<source lang='sql' class='def'>
<source lang='sql' class='def'>
SELECT name, gdp_pre_capita
SELECT name, ROUND(gdp_per_capita)
   FROM
   FROM
   (SELECT name, gdp/population AS gdp_per_capita
   (SELECT name,region,
          gdp/population AS gdp_per_capita
     FROM bbc) X
     FROM bbc) X
WHERE region='North America'
</source>
</source>
</div>
</div>

Revision as of 23:39, 8 August 2012

The Derived Table (SELECT FROM SELECT)

You can use the results from one query in another query

You may use a SELECT statement in the FROM line. In this case the derived table X has columns name, region and gdp_per_capita

SELECT name, ROUND(gdp_per_capita)
  FROM
  (SELECT name,region,
          gdp/population AS gdp_per_capita
     FROM bbc) X
 WHERE region='North America'

What about ties?

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.

See also: