Difference between revisions of "Using nested SELECT"

From SQLZOO
Jump to: navigation, search
Line 31: Line 31:
 
</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 from the subquery==
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 region FROM bbc 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, region FROM bbc
WHERE region IN (SELECT region FROM bbc WHERE name='Brazil'
+
WHERE region IN (SELECT region FROM bbc WHERE name='Brazil'
                                          OR name='Mexico')</pre></p>
+
                                            OR name='Mexico')
  
  
Line 46: Line 47:
 
List each country and its region in the same region as 'Brazil' or 'Mexico'.
 
List each country and its region in the same region as 'Brazil' or 'Mexico'.
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
</source>
 
 
<source lang='sql' class='ans'>
 
 
SELECT name, region FROM bbc
 
SELECT name, region FROM bbc
 
WHERE region IN (SELECT region  
 
WHERE region IN (SELECT region  
FROM bbc WHERE name='Brazil'
+
                  FROM bbc WHERE name='Brazil'
OR name ='Mexico')
+
                              OR name ='Mexico')
 
</source>
 
</source>
 
</div>
 
</div>

Revision as of 19:13, 22 August 2012

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 region FROM bbc WHERE name = 'Brazil' evaluates to 'South America' so we can use this value to obtain a list of all countries in the same region as 'Brazil'

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

SELECT name FROM bbc WHERE region = 
(SELECT region 
FROM bbc WHERE name = 'Brazil')
SELECT name 
FROM bbc WHERE region = 
  (SELECT region FROM bbc WHERE name = 'Brazil')

Notes

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

SELECT name FROM bbc WHERE region = 
  (SELECT region FROM bbc WHERE name='Brazil') AS brazil_region

Multiple results from the subquery

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 region FROM bbc WHERE name = 'Brazil' OR name='Mexico') will return two values ('North America' and 'South America'). You should use:

SELECT name, region FROM bbc
WHERE region IN (SELECT region FROM bbc WHERE name='Brazil'
                                           OR name='Mexico')


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

SELECT name, region FROM bbc
WHERE region IN (SELECT region 
                   FROM bbc WHERE name='Brazil'
                               OR name ='Mexico')
Personal tools
Namespaces

Variants
Actions
Reference
Toolbox
Google AdSense