15.00 - FROM Clause (Temporal Form) - Teradata Database

Teradata Database Temporal Table Support

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1182-015K

FROM Clause (Temporal Form)

Purpose  

Defines the set of tables, derived tables, or views that are referenced by the SELECT request.

Syntax  

Note: Temporal Table Support describes syntax that is especially relevant to temporal tables. Syntax that is not required, or that is not otherwise specific to temporal tables is generally not shown in this manual. For additional syntax, see SQL Data Definition Language, SQL Data Manipulation Language, and SQL Data Control Language.

 

Syntax Element …

Specifies …

table_name

the name of a base table, temporal table, derived table, or view.

VALIDTIME AS OF date_timestamp_expression

VALIDTIME AS OF (date_timestamp_expression)

that the retrieval of rows from table_name only includes rows where the period of validity overlaps the specified AS OF date or timestamp.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

The data type of date_timestamp_expression must be comparable with the element type of the valid-time column and the expression must not reference any columns.

AND

a keyword for specifying both a valid-time AS OF qualifier and a transaction-time AS OF qualifier.

TRANSACTIONTIME AS OF date_timestamp_expression

TRANSACTIONTIME AS OF (date_timestamp_expression)

that the retrieval of rows from table_name only includes rows where the transaction-time period in the rows overlaps the specified AS OF date or timestamp.

date_timestamp_expression can be a constant, scalar UDF, scalar subquery, or business calendar function that evaluates to a date or timestamp value.

The data type of date_timestamp_expression must be comparable with the element type of the transaction-time column and the expression must not reference any columns.

AS OF date_timestamp_expression

AS OF (date_timestamp_expression)

a date or timestamp value that qualifies the retrieval of rows from table_name in all existing time dimensions.

The data type of date_timestamp_expression must be comparable with the element types of all temporal columns and the expression must not reference any columns.

[AS] correlation_name

an alias for the table that is referenced by table_name.

(subquery)

the subquery that defines the derived table contents.

[AS] derived_table_name

an assigned name for the temporary derived table.

column_name

a list of column names or expressions listed in the subquery. Allows referencing subquery columns by name.

joined_table

either a single table name with optional alias name, or a joined table, indicating nested joins.

Note: AS OF is valid only when joined_table is a single table name with optional alias name.

CROSS JOIN

a cross join.

A CROSS JOIN is an unconstrained, or extended, Cartesian join.

Cross joins return the concatenation of all rows from the tables specified in its arguments. Two joined tables can be cross joined.

single_table

the name of a single base or derived table or view on a single table to be cross joined with joined_table.

[INNER] JOIN

a join in which qualifying rows from one table are combined with qualifying rows from another table according to some join condition.

This is the default join type.

LEFT OUTER JOIN

a left outer join.

LEFT indicates the table that was listed first in the FROM clause.

In a LEFT OUTER JOIN, matching rows as well as the rows from the left table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.

RIGHT OUTER JOIN

a right outer join.

RIGHT indicates the table that was listed second in the FROM clause.

In a RIGHT OUTER JOIN, matching rows as well as the rows from the right table that are not returned in the result of the inner join of the two tables, are returned in the outer join result and extended with nulls.

FULL OUTER JOIN

a full outer join.

FULL OUTER JOIN returns rows from both tables.

In a FULL OUTER JOIN, matching rows as well as rows from both tables that have not been returned in the result of the inner join, are returned in the outer join result and extended with nulls.

ON search_condition

one or more conditional expressions that must be satisfied by the result rows.

An ON condition clause is required for each INNER JOIN or OUTER JOIN specified in an outer join expression.

AS OF Qualifier Usage Notes

Use the AS OF qualifier in the FROM clause to view data as a snapshot at any point in time.

You can apply the AS OF qualifier to valid-time and transaction-time dimensions independently.

Valid-Time AS OF

The valid-time AS OF qualifier can only follow a valid-time table or a view or derived table.

A valid-time AS OF qualifier in the FROM clause overrides the temporal qualifier on valid time specified explicitly at the query level or implicitly by a session attribute for the specified table, view, or derived table only.

The valid-time column of the AS OF table, view, or derived table can appear anywhere in the query block. For example, the valid-time column can appear as a WHERE condition or JOIN condition.

Transaction-Time AS OF

The transaction-time AS OF qualifier can only follow a transaction-time table or a view or derived table.

A transaction-time AS OF qualifier in the FROM clause overrides the temporal qualifier on transaction time specified explicitly at the query level or implicitly by a session attribute for the specified table, view, or derived table only.

The transaction-time column of the AS OF table, view, or derived table can appear anywhere in the query block. For example, the transaction-time column can appear as a WHERE condition or JOIN condition.

Restrictions on AS OF for DELETE and UPDATE

The table associated with an AS OF qualifier in the FROM clause of an UPDATE or DELETE statement cannot be the table that the DELETE or UPDATE statement is modifying.

Related Information

 

For more information on...

See...

FROM clause

SQL Data Manipulation Language