Analytics with Hadoop - Big Data Made Easy: A Working Guide to the Complete Hadoop Toolset - page 263

The Subquery
SELECT statements can be nested as subqueries. Here are two examples of how that is done. The first is to use a SELECT
statement as a subquery in the FROM clause of an outer SELECT statement, as follows:
SELECT rd.* FROM
(
SELECT
myear,manufacturer,model,enginesz,cylinders
FROM
fuel.consumption
) rd ;
The outer SELECT statement uses a derived table called “rd” and selects all columns from rd using the column
list rd.* . The contents of the parentheses form the derived table named rd, which uses a subquery that selects the
following five columns from the external table fuel.consumption:
myear,manufacturer,model,enginesz,cylinders
This first example uses a subquery as a way to transform table data to form a derived table. For example, you
might want to filter table data or join a group of tables together to form a larger data set.
The second example uses a subquery in the WHERE clause of an SQL statement. In this manner, an outer SELECT
statement selects all column data ( fl.* ) from the table fuel.consumption, with a subquery in its WHERE clause to filter
on the engine size. The result is a table showing data for which the engine size is greater than average:
SELECT
fl.*
FROM
fuel.consumption fl
WHERE
fl.enginesz > ( SELECT AVG(st.enginesz) FROM fuel.consumption st )
In this second example, the subquery in parentheses selects the average (AVG) engine size value from
the instance of the fuel.consumption table with an alias of “st.” The outer query filters the contents of the fuel.
consumption table with an alias of “fl,” choosing only those rows where the fl.enginesz column is greater than the
average value.
Table Joins
Table joins allow you to join the data in one table to the data in a second table if there are comparable columns in each
table. For instance, the first table could contain a list of people, with each person having a unique identity number.
A second table might contain a list of addresses plus associated personal identity numbers. By joining the two tables
on the identity numbers, you are able to determine a person's name and address.
The following SQL example selects data from the earlier fuel.consumption table where the columns “myear,”
“manufacturer,” and “model” match another table called fuel.consumption3. Aliases have been used here for the
two tables—rd1 and rd2; this means there's less typing when you are specifying the table columns and so there's no