Using Subqueries To Customize An AS Clause | CREATE TABLE | Teradata Vantage - 17.10 - Using Subqueries To Customize An AS Clause - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
English (United States)

Subqueries are a powerful means for customizing the definition and data for a target table by using a query expression to select source columns for the target table definition.

If you use a subquery to copy the source table columns and, optionally, their collected statistics, the system does not copy their attributes from the source table to the target table unless you also specify 1 or more column descriptors, in which case it copies only the column attributes you specify explicitly in the subquery to the target table.

If you do not specify any column descriptors, the system copies only the data types (and, optionally, the collected statistics) of the selected source columns or the resultant data types of any columns used in a subquery expression to the target table. It does not copy any attributes such as FORMAT, DEFAULT, COMPRESS, and so on unless you specify them explicitly.

A column descriptor is a target table column name followed by a specification of 1 or more column attributes. If you specify column descriptors in the definition for the target table, then the column attributes they specify replace any contradictory attributes of the selected source column.

The system also copies any source column attributes from the selected columns that are not specified explicitly in a subquery column descriptor to the target table. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 for an example.

Because the source was derived from a subquery rather than from a base table definition:
  • The table-level attributes of table t are not copied to table t1.
  • The indexes defined for table t are not copied to table t1.
  • Because no explicit primary index was specified, the system selected the first valid column in t1, column a, to be its nonunique primary index.
There are several workarounds for the limitations of the subquery form of CREATE TABLE … AS.
  • Use the subquery form, but specify the indexes and other table column descriptors explicitly for the target table.
  • Use the non-subquery form of CREATE TABLE … AS.
  • Use the subquery form, then use ALTER TABLE. To add attributes to the new table, see ALTER TABLE (Basic Table Parameters)).
  • Either use the WITH DATA option for CREATE TABLE … AS (see Rules For AS … WITH NO DATA That Uses A Subquery To Define The Source) or create a new table, then populate it with the data from the original table using an INSERT … SELECT statement. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Following are rules for using of subqueries expressed in an AS clause.
  • Most valid subquery can be specified in the AS clause.
  • Any restrictions on valid subqueries also apply for the subquery forms of the AS clause. For example, the following restrictions apply.
    • You cannot specify an ORDER BY clause.
    • The WHERE, HAVING, QUALIFY, or ON clause of any condition specified within a recursive view definition cannot specify a SAMPLE clause within a predicate subquery.
    • The following table lists the rules for AS and NAMED clauses.
      Options Specified Description
      Both AS and NAMED clauses are specified The AS clause takes precedence and the column names for the target table are derived from the names it specifies.
      No column names are specified Names specified for expressions are used to name the derived columns of the target table.
  • The following restrictions apply to subqueries.
    Subquery Includes Description
    Column names Column names for the new table default to the existing names of the selected columns if you do not specify different names for them.
    Join expression Duplicate column names from the joined tables must be differentiated by fully qualified names.
  • The following copy traits are defined for the subquery forms of the AS clause.
    • Column-level attributes are not copied, except for data type.
    • Table-level attributes, including indexes, are not copied.
    • The following user or database default attributes are used for the target table unless you specify explicit values for them in the CREATE TABLE statement.

      The standard user and database defaults are defined in the following table.

      Table Option Permanent Table Defaults Global Temporary Table Defaults Volatile Table Defaults
      You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
      None None None
      Permanent journaling No Before

      No After

      Not permitted Not permitted
      Transaction journaling None Log Log
      Referential integrity constraint None Not permitted Not permitted
      CHECK or BETWEEN constraints None None Not permitted
      COMPRESS column None None Not permitted
      DEFAULT and TITLE clauses None None Not permitted
      Named Index None None Not permitted
      FREESPACE or DBSIZE User or Database default User or Database default User or Database default
      ON COMMIT Not applicable Delete Delete