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

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

name	ROUND(gdp_per..
Australia	26900
Austria	32300
Belgium	31030
…

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

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

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’

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

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:

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

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.

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.