Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
(10 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
<h3>The Derived Table (SELECT FROM SELECT)</h3>
+
__NOTOC__
−
<p>List the largest country for each region</p>
+
==Subquery Table, Derived Tables, Nested Queries==
−
<p>
+
<p>You can use the results from one query in another query</p>
−
Sometimes you need to use the results from one query in another.
+
==Subquery with FROM==
−
To show the largest country (by population) for each region you can join the list of countries
+
<div class='qu'>
−
to the list of regions (with max populations).
+
You may use a SELECT statement in the FROM line.
−
</p>
+
−
<p>
+
−
For example you can get the maximum population for each region using:
+
−
</p>
+
−
<pre>
+
−
SELECT region, MAX(population) AS maxpop
+
−
  FROM bbc
+
−
GROUP BY region</pre>
+
−
Note that this gives the population of the largest country - it does not give you the name of that
+
−
country.
+
−
<table border="1" style="" class="zoo"><tr>
+
−
<th>region</th>
+
−
<th>maxpop</th>
+
−
</tr>
+
−
<tr>
+
−
<td>Africa</td>
+
−
<td align="right">130200000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>Americas</td>
+
−
<td align="right">13000000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>Asia-Pacific</td>
+
−
<td align="right">1300000000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>Europe</td>
+
−
<td align="right">141500000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>Middle East</td>
+
−
<td align="right">74900000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>North America</td>
+
−
<td align="right">295000000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>South America</td>
+
−
<td align="right">182800000</td>
+
−
</tr>
+
−
<tr>
+
−
<td>South Asia</td>
+
−
<td align="right">1100000000</td>
+
−
</tr>
+
−
</table>
+
  
−
    
+
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.
 +
<source lang='sql' class='def'>
 +
SELECT name, ROUND(gdp_per_capita)
 +
   FROM
 +
  (SELECT name,
 +
          gdp/population AS gdp_per_capita
 +
    FROM bbc) X
 +
WHERE gdp_per_capita>20000
 +
</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>
  
 +
==Subquery with IN==
 
<div class='qu'>
 
<div class='qu'>
−
You may use a SELECT statement in the FROM line.
+
<p class='imper'>Find the countries in the same region as Bhutan</p>
−
In this example the table is <code>beast</code> and the columns are <code>id</code> and <code>legs</code>.
+
You may use a SELECT statement in the WHERE line - this returns a list of regions.
 +
 
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
−
SELECT name, A.region
+
SELECT name
−
   FROM bbc AS A JOIN
+
   FROM bbc
−
   (SELECT region,MAX(population) AS maxpop
+
WHERE region IN
−
    FROM bbc
+
   (SELECT region FROM bbc
−
  GROUP BY region) AS B
+
    WHERE name='Bhutan')
−
  ON (A.region=B.region
+
−
  AND A.population=maxpop)
+
 
</source>
 
</source>
 +
</div>
  
−
<source lang='sql' class='ans'>
+
==Correlated Subquery==
−
SELECT name, A.region
+
<div class='qu'>
−
FROM bbc AS A JOIN
+
<p>If a value from the outer query appears in the inner query this is "correlated subquery".</p>
−
  (SELECT region,MAX(population) AS maxpop
+
<p class='imper'>Show the countries where the population is greater than 5 times the average for its region</p>
−
    FROM bbc
+
 
−
  GROUP BY region) AS B
+
<source lang='sql' class='def'>
−
ON (A.region=B.region AND A.population=maxpop)
+
SELECT name
 +
  FROM bbc b1
 +
  WHERE population>
 +
  5*(SELECT AVG(population) FROM bbc
 +
    WHERE region=b1.region)
 
</source>
 
</source>
 
</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 01:33, 9 August 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')

Correlated Subquery

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:
Retrieved from "http://sqlzoo.net/w/index.php?title=SELECT_.._SELECT&oldid=3343"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense