SELECT within SELECT Tutorial
| Language: Project:Language policy | English • 日本語 • 中文 |
|---|
This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| ... | ||||
Bigger than Russia
List each country name where the population is larger than that of 'Russia'.
world(name, continent, area, population, gdp)
SELECTnameFROMworld WHEREpopulation> (SELECTpopulationFROMworld WHEREname='Romania')
SELECTnameFROMworld WHEREpopulation> (SELECTpopulationFROMworld WHEREname='Russia')
{{#ev:youtube|UPFwlsJFcsw}}
Richer than UK
Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.
SELECTnameFROMworld WHEREcontinent='Europe'ANDgdp/population> (SELECTgdp/populationFROMworld WHEREname='United Kingdom')
{{#ev:youtube|RELAqb4DlvY}}
Neighbours of Argentina and Australia
List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.
SELECTname,continent FROMworld WHEREcontinentIN( SELECTcontinent FROMworld WHEREnameIN('Australia','Argentina')) ORDERBYname
Between Canada and Poland
Which country has a population that is more than United Kingdom but less than Germany? Show the name and the population.
SELECTname,populationFROMworld WHEREpopulationBETWEEN (SELECTpopulation+1FROMworldWHEREname='United Kingdom') AND (SELECTpopulation-1FROMworldWHEREname='Germany')
Percentages of Germany
Germany (population roughly 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.
Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.
The format should be Name, Percentage for example:
| name | percentage |
|---|---|
| Albania | 3% |
| Andorra | 0% |
| Austria | 11% |
| ... | ... |
SELECTname,CONCAT(CAST(ROUND(100*population/(SELECTpopulationFROMworldWHEREname='Germany'),0)asint),'%') FROMworld WHEREcontinent='Europe'
{{#ev:youtube|lCbLvHUNBG4}}
To gain an absurdly detailed view of one insignificant feature of the language, read on.
We can use the word ALL to allow >= or > or < or <=to act over a list. For example, you can find the largest country in the world, by population with this query:
SELECTname FROMworld WHEREpopulation>=ALL(SELECTpopulation FROMworld WHEREpopulation>0)
You need the condition population>0 in the sub-query as some countries have null for population.
Bigger than every country in Europe
Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
SELECTnameFROMworld WHEREgdp>ALL (SELECTgdpFROMworld WHEREcontinent='Europe' ANDgdpISNOTNULL)
We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.
Largest in each continent
Find the largest country (by area) in each continent, show the continent, the name and the area:
SELECTcontinent,name,populationFROMworldx WHEREpopulation>=ALL (SELECTpopulationFROMworldy WHEREy.continent=x.continent ANDpopulation>0)
SELECTcontinent,name,area FROMworldx WHEREarea>=ALL (SELECTareaFROMworldy WHEREy.continent=x.continent andarea>0)
The above example is known as a correlated or synchronized sub-query.
A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.
One way to interpret the line in the WHERE clause that references the two table is "... where the correlated values are the same".
In the example provided, you would say "select the country details from world where the population is greater than or equal to the population of all countries where the continent is the same".
First country of each continent (alphabetically)
List each continent and the name of the country that comes first alphabetically.
SELECTcontinent,nameFROMworldx WHEREx.name<=ALL( SELECTnameFROMworldy WHEREx.continent=y.continent)
Difficult Questions That Utilize Techniques Not Covered In Prior Sections
Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
SELECTname,continent,populationFROMworldx WHERE25000000>=ALL( SELECTpopulationFROMworldy WHEREx.continent=y.continent ANDy.population>0)
Three time bigger
Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.
SELECTname,continentFROMworldxWHERE population>ALL (SELECTpopulation*3FROMworldy WHEREy.continent=x.continent ANDy.name!=x.name)