Skip to content

Latest commit

 

History

History
98 lines (72 loc) · 2.96 KB

4.2 Using nested SELECT.md

File metadata and controls

98 lines (72 loc) · 2.96 KB

Using nested SELECT

Link to page - https://sqlzoo.net/wiki/Using_nested_SELECT

1. Using SELECT in SELECT

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

-- selects the continent of Brazil
SELECT continent FROM world WHERE name = 'Brazil'

SELECT name FROM world
WHERE continent = (SELECT continent FROM world WHERE name = 'Brazil');
name
Argentina
Bolivia
Brazil
Chile
Colombia
Ecuador
Guyana
Paraguay
Peru
Saint Vincent and the Grenadines
Suriname
Uruguay
Venezuela

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

2. 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.

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

3. 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') as "chineses"
  FROM world
WHERE name = 'China'
chineses
21.2987

4. Operators over a set

=     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, population FROM world
 WHERE population > ALL
      (SELECT population FROM world
        WHERE continent='Europe')
ORDER BY population DESC

Note ALL . It looks like "give me the MAXIMUM possible value from a set".