Derived Tables - Teradata Database - Teradata Vantage NewSQL Engine

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. For information about views, see “CREATE VIEW” in Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

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 is similar to a viewed table.

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

Syntax

Syntax Elements

with modifier

See WITH Modifier.

(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. 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 statement that installed the function_name table function.

ANSI Compliance

Derived tables are ANSI SQL:2011-compliant.