Derived Tables

In SQL statements, a referenced table can be a base table, a derived table, a queue table, or a view. The semantics of derived tables and views are identical.

A derived table is obtained from one or more other tables as the result of a subquery.

ANSI Compliance

Derived tables are ANSI SQL:2011-compliant.

Uses for Derived Tables

Derived tables are an optional feature that you can specify in the FROM clause of SELECT, ABORT, ROLLBACK, and DELETE statements.

You can use derived tables for these purposes:

To avoid CREATE and DROP TABLE statements for storing retrieved information that is only needed for one request.

To enable you to code more sophisticated, complex join queries.

For examples, see “Inner Joins” on page 247 and “Outer Joins” on page 253.

Rules and Restrictions for Derived Tables

The rules and restrictions are:

The semantics of derived tables and views are identical, as are their restrictions.

The scope of a derived table is limited to the level of the SELECT statement calling the subquery.

A unique table correlation name is required for each derived table you create within a statement.

You cannot specify any of these SQL syntactical elements in a derived table:

ORDER BY

WITH

WITH … BY

Recursion

Fully qualified column names are mandatory when you specify otherwise ambiguous column names in the select list of a subquery used to build a derived table.

This rule is parallel to, and consistent with, the rules for creating a view.

The following query fails because the columns specified in the select list of the subquery that builds the derived table are not qualified:

      SELECT *

      FROM (SELECT *

            FROM tab1 AS t1, tab2 AS t2

            WHERE t1.col2 = t2.col3) AS derived_table;

      *** Failure 3515 Duplication of column COL1 in creating a Table,

      View, Macro or Trigger.

      Statement# 1, Info =95

      *** Total elapsed time was 1 second.

The query is correctly written as follows:

      SELECT *

      FROM (SELECT t1.col1, t1.col2, t1.col3, t2.col1,

                   t2.col2, t2.col3

            FROM tab1 AS t1, tab2 AS t2

            WHERE t1.col2=t2.col3) AS derived_table (t1_col1,

                  t1_col2, t1_col3, t2_col1, t2.col2, t2_col3);

The subquery that defines the contents of a derived table cannot contain SELECT AND CONSUME statements.

Using Derived Tables To Do Multilevel Aggregation

You cannot specify aggregates in a WHERE clause predicate; however, derived tables make such calculations easy to perform.

The following example shows how derived tables facilitate this.

     SELECT name, salary, average_salary

     FROM (SELECT AVG(salary)

           FROM employee) AS workers (average_salary), employee

     WHERE salary > average_salary

     ORDER BY salary DESC;

The query returns this answer set:

`name` `----`	`salary` `------`	`average_salary` `--------------`
`Russel S`	`55,000.00`	`38,147.62`
`Watson L`	`56,000.00`	`38,147.62`
`Phan A`	`55,000.00`	`38,147.62`
`Aguilar J`	`45,000.00`	`38,147.62`
`Carter J`	`44,000.00`	`38,147.62`

In this example, the WHERE condition compares values from rows in the base table employee with the (in this case single) values of the column average_salary in the derived table workers.

The following example is more elaborate, grouping the same information by dept_no. In this example, the statement returns all salaries that exceed their respective department averages.

     SELECT name, salary, dept_no, average_salary

     FROM (SELECT AVG(salary), dept_no

           FROM employee

           GROUP BY dept_no) AS workers (average_salary,dept_num),

                               employee

     WHERE salary > average_salary

     AND   dept_num = dept_no

     ORDER BY dept_no, salary DESC;

The answer set might look like this:

name	salary	dept_no	average_salary
Chin M	38,000.00	100	32,625.00
Moffit H	35,000.00	100	32,625,00
Russel S	55,000.00	300	47,666.67
Phan A	55,000.00	300	47,666.67
Watson L	56,000.00	500	38,285.71
Carter J	44,000.00	500	38,385.71
Smith T	42,000.00	500	38,285.71
Aguilar J	45,000.00	600	36,650.00

In this example, the derived table is a grouped table, providing a set of rows. The outer WHERE clause needs an equality join between the department number (dept_no) of the base table, employee, and the derived table.

Restricted Use With SELECT AND CONSUME Statements

The subquery that defines the contents of a derived table cannot contain SELECT AND CONSUME statements.

For More Information

For more information about the semantics of views, see “CREATE VIEW” in SQL Data Definition Language Detailed Topics.

For more information about specific statements, see:

“ABORT” on page 301

“DELETE” on page 346

“ROLLBACK” on page 484