Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
(3 intermediate revisions by one user not shown)
Line 1: Line 1:
<h3>Subquery Table, Derived Tables, Nested Queries</h3>
+
__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 SELECT f FROM (SELECT ).
+
You may use a SELECT statement in the FROM line.
 +
 
 
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 19: Line 22:
 
</div>
 
</div>
  
 +
==Subquery with IN==
 
<div class='qu'>
 
<div class='qu'>
 
<p class='imper'>Find the countries in the same region as Bhutan</p>
 
<p class='imper'>Find the countries in the same region as Bhutan</p>
Line 31: Line 35:
 
</source>
 
</source>
 
</div>
 
</div>
 +
 +
==Correlated Subquery==
 +
<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>

Revision as of 00: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:
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense