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.
- 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.
- 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.
- 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 Fallback 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