Post

Using Derived Tables and Nested SELECT

Using Subquery and Derived Tables

I had difficult time understanding the world x and world y concept from the yet completed post “SELECT Quiz”. I found this one small hidden hyperlink “SELECT from SELECT” inside another hidden hyperlink “Using nested SELECT” from “SELECT within SELECT”, expanding more about derived tables. I am going to cover on that first.

Derived table is when I could use result from one query in another query.

1) Subquery with FROM

  • may use a SELECT statement in the FROM line
  • in this case the derived table X has columns name and gdp_per_capita
  • calculated values in the inner SELECT can be used in the outer SELECT
1
2
3
SELECT name, ROUND(gdp_per_capita) FROM
  (SELECT name, gdp/population AS gdp_per_capita FROM bbc) X
WHERE gdp_per_capita>20000

Happening here:

  • outer query gather countries that has gdp_per_capita greater than 20000
  • inner query gather ALL countries with their gdp_per_capita

Noting:

  • inner table is given an alias x
  • first column in inner query keeps its name
  • second column in inner query has an alias gdp/population AS gdp_per_capita
nameROUND(gdp_per..
Australia26900
Austria32300
Belgium31030
 


2) 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
1
2
SELECT name FROM bbc
WHERE region IN (SELECT region FROM bbc WHERE name = 'Bhutan')
  • no derived table, has a inner query in WHERE
  • inner query identifies the region that ‘Bhutan’ is in
  • outer query gather all countries in the region yet identified until inner query
name
Afghanistan
Bangladesh
Bhutan


3) 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
1
2
SELECT name FROM bbc b1
WHERE population > 5 * (SELECT AVG(population) FROM bbc WHERE region=b1.region)
  • outer query gathers countries FROM aliased bbc b1 table with population greater than 5 times inner query result
  • inner query calculates average population in each region
name
Brazil
China
India
Nigeria
Russia


Using Nested SELECT

  • result of a SELECT statement may be used as a value in another statement
  • statement SELECT continent FROM world WHERE name = ‘Brazil’ evaluates to ‘South America’
  • can use this value to obtain a list of all countries in the same continent as ‘Brazil’

1) Using SELECT in SELECT List each country in the same continent as ‘Brazil’

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

Alias

  • some versions of SQL insist that you give the subquery an alias
  • using AS somename after the closing bracket
1
2
SELECT name FROM world
WHERE continent = (SELECT continent FROM world WHERE name='Brazil') AS brazil_continent

I decided to research a bit more about alias and found this juicy information in wiki. It is a feature of SQL that most of relational database management systems (RDBMSs) support.

What alias can do:

  • reduce the amount of code required for a query
  • make simpler to understand
  • can be used as an obfuscation technique to protect the real names of database fields
  • can alias tables and columns

Alias table is also called a correlation name.

Multiple Results

  • subquery may return more than one result
  • if more than one result, will fail as testing one value against more than one value
  • safer to use IN to cope with this possibility

FAIL: (SELECT continent FROM world WHERE name = 'Brazil' OR name='Mexico') will return two values (‘North America’ and ‘South America’)

  • Saying “apple” and “orange” at the same time is impossible

BETTER:

1
2
SELECT name, continent FROM world
WHERE continent IN (SELECT continent FROM world WHERE name='Brazil' OR name='Mexico')
  • outer query gather countries and continents
  • inner query gather continents where ‘Brazil’ and ‘Mexico’ are in


3) Subquery on the SELECT line

  • if certain that only one value will be returned then can use that query on the SELECT line
  • show the population of China as a multiple of the population of the United Kingdom
1
2
SELECT population/(SELECT population FROM world WHERE name = 'United Kingdom')
FROM world WHERE name = 'China'
population/(S..
21.2987

ALL or ANY

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

4) 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.
1
2
SELECT name FROM world
WHERE population > ALL (SELECT population FROM world WHERE continent='Europe')
name
Bangladesh
Brazil
China
Egypt
Ethiopia
India
Indonesia
Japan
Mexico
Nigeria
Pakistan
Philippines
Russia
United States
Vietnam

Now I am heading back to older post SELECT Quiz to clarify, since now that I understand a bit more.

This post is licensed under CC BY 4.0 by the author.

Comments powered by Disqus.