Difference between revisions of "SELECT .. SELECT"

Revision as of 00:20, 9 August 2012

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

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')

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 average for its region

SELECT name
  FROM bbc b1
 WHERE population>
  (SELECT AVG(population) FROM bbc
    WHERE region=b1.region)

@@ 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>
+==Subquery with FROM==
 <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>.
 The calculated values in the inner SELECT can be used in the outer SELECT.
@@ Line 19: / Line 21: @@
 </div>
+==Subquery with IN==
 <div class='qu'>
 <p class='imper'>Find the countries in the same region as Bhutan</p>
@@ Line 31: / Line 34: @@
 </source>
 </div>
+==Correlated Subquery==
+<div class='qu'>If a value from the outer query appears in the inner query this is "correlated subquery".
+<p class='imper'>Show the countries where the population is greater than average for its region</p>
+<source lang='sql' class='def'>
+SELECT name
+  FROM bbc b1
+ WHERE population>
+  (SELECT AVG(population) FROM bbc
+    WHERE region=b1.region)
+</source>
+</div>
 <div>See also:</div>