Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to: navigation, search
Line 20: Line 20:
  
 
<div class='qu'>
 
<div class='qu'>
<p class='imper'>Find the countries in the same region as Butan</p>
+
<p class='imper'>Find the countries in the same region as Bhutan</p>
 
You may use a SELECT statement in the WHERE line - this returns a list of regions.
 
You may use a SELECT statement in the WHERE line - this returns a list of regions.
  
Line 28: Line 28:
 
  WHERE region IN
 
  WHERE region IN
 
   (SELECT region FROM bbc
 
   (SELECT region FROM bbc
     WHERE name='Butan')
+
     WHERE name='Bhutan')
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 01:05, 9 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 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

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')
See also:
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense