Difference between revisions of "SELECT .. SELECT"

From SQLZOO
Jump to navigation Jump to search
 
(2 intermediate revisions by 2 users not shown)
Line 1: Line 1:
{{Languages}}
__NOTOC__  
__NOTOC__  
==Subquery Table, Derived Tables, Nested Queries==
==Subquery Table, Derived Tables, Nested Queries==
Line 4: Line 5:
==Subquery with FROM==
==Subquery with FROM==
<div class='qu'>
<div class='qu'>
You may use a SELECT statement in the FROM line.
You may use a <code>SELECT</code> statement in the <code>FROM</code> 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 <code>X</code> 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 <code>SELECT</code> can be used in the outer <code>SELECT</code>.
<source lang='sql' class='def'>
<source lang='sql' class='def'>
SELECT name, ROUND(gdp_per_capita)
SELECT name, ROUND(gdp_per_capita)
Line 17: Line 18:
</source>
</source>
Notice that
Notice that
*the inner table is given an alias X
*the inner table is given an alias <code>X</code>
*the first column in the inner query keeps its name
*the first column in the inner query keeps its name
*the second column in the inner query has an alias
*the second column in the inner query has an alias
Line 25: Line 26:
<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>
You may use a SELECT statement in the WHERE line - this returns a list of regions.
You may use a <code>SELECT</code> statement in the <code>WHERE</code> line - this returns a list of regions.


<source lang='sql' class='def'>
<source lang='sql' class='def'>
Line 53: Line 54:
<div>See also:</div>
<div>See also:</div>
<ul>
<ul>
   <li>[http://sqlzoo.net/w/index.php/SELECT_within_SELECT_Tutorial SELECT in SELECT Tutorial]</li>
   <li>[[SELECT within SELECT Tutorial]]</li>
   <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>
   <li>[[SELECT_.._JOIN |SELECT JOIN]]</li>
</ul>
</ul>

Latest revision as of 15:28, 18 April 2018

Language:Project:Language policy English  • 日本語

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: