Derived Tables - Teradata Database

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.

This option enables the FROM clause to specify a spool made up of selected data from an underlying table set. The derived table acts like a viewed table.

You can invoke a scalar UDF from any point in a derived table.

Syntax

Syntax Elements - Derived Tables

(subquery)

Nested SELECT expressions.

You can specify NORMALIZE in a subquery.

You cannot specify SELECT AND CONSUME statements in a subquery. You cannot specify an EXPAND ON clause in a subquery.

AS

Optional introductory clause to the derived table name.

derived_table_name

Name of the derived table.

column_name

List of column names or expressions listed in the subquery. Enables referencing subquery columns by name.

Specify column names only. Do not use forms such as table_name.column_name or database_name.table_name.column_name.

If specified, the number of names in the list must match the number of columns in the RETURNS TABLE clause of the CREATE FUNCTION statement that installed the function_name table function on the Teradata platform. The alternate names list corresponds positionally to the corresponding column names in the RETURNS TABLE clause.

If omitted, the names are the same as the column names in the RETURNS TABLE clause of the CREATE FUNCTION request that installed the function_name table function on the Teradata platform.

Syntax Elements - WITH Statement Modifier

You can search a table using iterative self-join and set operations. Multiple definitions of the nonrecursive form can be used with a single SELECT statement by specifying different queries by name.

query_name

Name of the nonrecursive query.

column_name

Name of a column in the named query definition.

select_expression

Nonrecursive SELECT statement that retrieves the row data to store in the named query.

You can specify an expression that returns a UDT in a column list only if its transform group has a fromsql routine. The system automatically converts the expression from its UDT value to the external type via the fromsql routine before returning it to a client application. For details, see Specifying UDTs in an SQL Request.

You can specify a row-level security constraint column in the select list of a SELECT request. However, the column cannot be specified as part of an arithmetic expression in the select list. The value returned for the column is the coded value for the row-level security constraint from the row.

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.

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.

  In this query, 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.

Restricted Use With SELECT AND CONSUME Statements

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

Example: 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),
                               employee
     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.

Related Topics

For more information about views, see “CREATE VIEW” in SQL Data Definition Language Syntax and Examples .

For more information about specific statements, see:

• “ABORT”
• “DELETE”
• “ROLLBACK”