Difference between revisions of "Using nested SELECT"

From SQLZOO
Jump to: navigation, search
(Created page with " <p>Using SELECT in SELECT</p> <p>See SELECT FROM SELECT for how to use a derived table.</p> <p>The result of a SELECT statement may be used as a value in anoth...")
 
(→‎Using binary operators over a set)
 
(18 intermediate revisions by 2 users not shown)
Line 1: Line 1:
−
    <p>Using SELECT in SELECT</p>
+
Return to the [[SELECT within SELECT Tutorial]]
−
<p>See SELECT FROM SELECT for how to use a [[derived table]].</p>
+
==Using SELECT in SELECT==
 +
<p>See SELECT FROM SELECT for how to use a [http://sqlzoo.net/w/index.php/SELECT_.._SELECT derived table].</p>
 
     <p>The result of a SELECT statement may be used as a value in
 
     <p>The result of a SELECT statement may be used as a value in
 
     another statement. For example the statement  
 
     another statement. For example the statement  
−
     <b>SELECT region FROM bbc WHERE name = 'Brazil'</b>
+
     <b>SELECT continent FROM world WHERE name = 'Brazil'</b>
  
 
     evaluates to <code>'South America'</code> so we can use this value to
 
     evaluates to <code>'South America'</code> so we can use this value to
−
     obtain a list of all countries in the same region as
+
     obtain a list of all countries in the same continent as
 
     'Brazil'</p>
 
     'Brazil'</p>
  
 
<div class='qu'>
 
<div class='qu'>
−
List each country in the same region as 'Brazil'.
+
List each country in the same continent as 'Brazil'.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
−
SELECT name FROM bbc WHERE region =  
+
SELECT name FROM world WHERE continent =  
−
(SELECT region
+
(SELECT continent
−
FROM bbc WHERE name = 'Brazil')
+
FROM world WHERE name = 'Brazil')
 
</source>
 
</source>
  
 
<source lang='sql' class='ans'>
 
<source lang='sql' class='ans'>
 
SELECT name  
 
SELECT name  
−
FROM bbc WHERE region =  
+
FROM world WHERE continent =  
−
   (SELECT region FROM bbc WHERE name = 'Brazil')
+
   (SELECT continent FROM world WHERE name = 'Brazil')
 
</source>
 
</source>
 
</div>
 
</div>
  
−
<h2>Notes</h2>
+
==Alias==
 
<p>Some versions of SQL insist that you give the subquery an <i>alias</i>. Simply put <code>AS somename</code> after the closing bracket:
 
<p>Some versions of SQL insist that you give the subquery an <i>alias</i>. Simply put <code>AS somename</code> after the closing bracket:
 
<pre>
 
<pre>
−
SELECT name FROM bbc WHERE region =  
+
SELECT name FROM world WHERE continent =  
−
   (SELECT region FROM bbc WHERE name='Brazil') AS brazil_region
+
   (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent
 
</pre>
 
</pre>
 
</p>
 
</p>
−
    <h2>Multiple results from the subquery</h2>
 
  
−
    <p>The subquery may return more than one result - if this happens the query will fail as
+
==Multiple Results==
−
you are testing one value against more than one value. It is safer to use IN to cope with  
+
 
−
this possibility</p>
+
The subquery may return more than one result - if this happens the query above will fail as you are testing one value against more than one value.
−
    <p>The phrase <code>(SELECT region FROM bbc WHERE name = 'Brazil' OR name='Mexico')</code>
+
It is safer to use IN to cope with this possibility.
 +
 
 +
The phrase <code>(SELECT continent FROM world WHERE name = 'Brazil' OR name='Mexico')</code>
 
will return two values ('North America' and 'South America'). You should use:
 
will return two values ('North America' and 'South America'). You should use:
−
<pre>SELECT name, region FROM bbc
+
SELECT name, continent FROM world
−
WHERE region IN (SELECT region FROM bbc WHERE name='Brazil'
+
WHERE continent IN
−
                                          OR name='Mexico')</pre></p>
+
  (SELECT continent FROM world WHERE name='Brazil'
 +
                                  OR name='Mexico')
  
 +
<div class='qu'>
 +
List each country and its continent in the same continent as 'Brazil' or 'Mexico'.
 +
<source lang='sql' class='def'>
 +
SELECT name, continent FROM world
 +
WHERE continent IN
 +
  (SELECT continent
 +
    FROM world WHERE name='Brazil'
 +
                  OR name='Mexico')
 +
</source>
 +
</div>
  
 +
==Subquery on the SELECT line==
 +
If you are certain that only one value will be returned you can use that query on the SELECT line.
 
<div class='qu'>
 
<div class='qu'>
−
List each country and its region in the same region as 'Brazil' or 'Mexico'.
+
<p class='imper'>Show the population of China as a multiple of the population of the United Kingdom</p>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 +
SELECT
 +
population/(SELECT population FROM world
 +
            WHERE name='United Kingdom')
 +
  FROM world
 +
WHERE name = 'China'
 
</source>
 
</source>
 +
</div>
  
−
<source lang='sql' class='ans'>
+
==Operators over a set==
−
SELECT name, region FROM bbc
+
These operators are ''binary'' - they normally take two parameters:
−
WHERE region IN (SELECT region
+
=    equals
−
FROM bbc WHERE name='Brazil'
+
>    greater than
−
OR name ='Mexico')
+
<    less than
 +
>=    greater or equal
 +
<=    less or equal
 +
You can use the words ALL or ANY where the right side of the operator might have multiple values.
 +
<div class='qu'>
 +
<p class='imper'>Show each country that has a population greater than the population of ALL countries in Europe.</p>
 +
Note that we mean greater than every single country in Europe; not the combined population of Europe.
 +
<source lang='sql' class='def'>
 +
SELECT name FROM world
 +
WHERE population > ALL
 +
      (SELECT population FROM world
 +
        WHERE continent='Europe')
 
</source>
 
</source>
 
</div>
 
</div>
 +
 +
Return to the [[SELECT within SELECT Tutorial]]

Latest revision as of 22:19, 8 December 2012

Return to the SELECT within SELECT Tutorial

Contents

[edit] Using SELECT in SELECT

See SELECT FROM SELECT for how to use a derived table.

The result of a SELECT statement may be used as a value in another statement. For example the statement SELECT continent FROM world WHERE name = 'Brazil' evaluates to 'South America' so we can use this value to obtain a list of all countries in the same continent as 'Brazil'

List each country in the same continent as 'Brazil'. 

SELECT name FROM world WHERE continent = 
(SELECT continent 
FROM world WHERE name = 'Brazil')
SELECT name 
FROM world WHERE continent = 
  (SELECT continent FROM world WHERE name = 'Brazil')

[edit] Alias

Some versions of SQL insist that you give the subquery an alias. Simply put AS somename after the closing bracket: 

SELECT name FROM world WHERE continent = 
  (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent

[edit] Multiple Results

The subquery may return more than one result - if this happens the query above will fail as you are testing one value against more than one value. It is safer to use IN to cope with this possibility.

The phrase (SELECT continent FROM world WHERE name = 'Brazil' OR name='Mexico') will return two values ('North America' and 'South America'). You should use: 

SELECT name, continent FROM world
WHERE continent IN
 (SELECT continent FROM world WHERE name='Brazil'
                                 OR name='Mexico')

List each country and its continent in the same continent as 'Brazil' or 'Mexico'. 

SELECT name, continent FROM world
WHERE continent IN
  (SELECT continent 
     FROM world WHERE name='Brazil'
                   OR name='Mexico')

[edit] Subquery on the SELECT line

If you are certain that only one value will be returned you can use that query on the SELECT line.

Show the population of China as a multiple of the population of the United Kingdom

SELECT
 population/(SELECT population FROM world
             WHERE name='United Kingdom')
  FROM world
WHERE name = 'China'

[edit] Operators over a set

These operators are binary - they normally take two parameters: 

=     equals
>     greater than
<     less than
>=    greater or equal
<=    less or equal

You can use the words ALL or ANY where the right side of the operator might have multiple values.

Show each country that has a population greater than the population of ALL countries in Europe.

Note that we mean greater than every single country in Europe; not the combined population of Europe. 

SELECT name FROM world
 WHERE population > ALL
      (SELECT population FROM world
        WHERE continent='Europe')

Return to the SELECT within SELECT Tutorial

Retrieved from "http://sqlzoo.net/w/index.php?title=Using_nested_SELECT&oldid=5133"
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense