15.00 - Derived Tables - Teradata Database

Teradata Database SQL Data Manipulation Language

Teradata Database
Programming Reference

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:
  • 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:


    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),
         WHERE salary > average_salary 
         AND   dept_num = dept_no 
         ORDER BY dept_no, salary DESC;

    The answer set might look like this:


    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