Syntax - Advanced SQL Engine - Teradata Database

FROM source_spec [,...]

source_spec

{ table_name [ temporal_qualifier ] [ [AS] correlation_name ] |

  ( subquery ) [AS] derived_table_name
    [ ( column_name [,...] ) ] [ temporal_qualifier ] |

  joined_table [ temporal_qualifier ]
    { CROSS JOIN single_table |

      [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ]
        JOIN joined_table [ temporal_qualifier ] ON search_condition
    }
}

temporal_qualifier

{ valid_time_qualifier [ AND transaction_time_qualifier ] |
  transaction_time_qualifier [ AND valid_time_qualifier ] |
  AS OF { date_timestamp_expression | ( date_timestamp_expression ) }
}

valid_time_qualifier

{ VALIDTIME AS OF
  { date_timestamp_expression | ( date_timestamp_expression ) } |
  { CURRENT | NONSEQUENCED } VALIDTIME
}

transaction_time_qualifier

{ TRANSACTIONTIME AS OF
  { date_timestamp_expression | ( date_timestamp_expression ) } |
  { CURRENT | NONSEQUENCED } TRANSACTIONTIME
}

table_name

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

VALIDTIME AS OF date_timestamp_expression

VALIDTIME AS OF (date_timestamp_expression)

Specifies 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

Specifies 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)

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

CURRENT VALIDTIME

Specifies that the retrieval of rows from table_name includes only current rows in the valid-time dimension, which are rows having a period of validity that overlaps TEMPORAL_DATE or TEMPORAL_TIMESTAMP. The valid-time column of table_name is considered to be in the scope of the query, and can therefore be used as any other column in the table, such as in a WHERE or JOIN condition.

CURRENT VALIDTIME in a FROM clause takes precedence over a validtime qualifier at the statement level. For example, consider the following query:

SEQUENCED VALIDTIME SELECT Policy.policy_id 
FROM Policy CURRENT VALIDTIME, Policy_History;

In this case, the SEQUENCED VALIDTIME at the statement level is ignored for Policy, which has a temporal qualifier in the FROM clause. SEQUECED VALIDTIME is applied to Policy_History, however, which does not have a temporal qualifier in the FROM clause.

NONSEQUENCED VALIDTIME

Specifies that table_name is treated as a non-temporal table. Results in a table without a valid-time column.

CURRENT TRANSACTIONTIME

Specifies that the retrieval of rows from table_name includes only rows that are open in the transaction-time dimension.

CURRENT TRANSACTIONTIME in a FROM clause takes precedence over a transactiontime qualifier at the statement level. For example, consider the following query:

NONSEQUENCED TRANSACTIONTIME SELECT Policy_Types.Policy_Name 
FROM Policy_Types CURRENT TRANSACTIONTIME, Policy_History; 

In this case, the NONSEQUENCED TRANSACTIONTIME at the statement level is ignored for Policy_Types, which has a temporal qualifier in the FROM clause. NONSEQUECED TRANSACTIONTIME is applied to Policy_History, however, which does not have a temporal qualifier in the FROM clause.

NONSEQUENCED TRANSACTIONTIME

Specifies that table_name is treated as a non-temporal table. Results in a table without a transaction-time column.

AS OF date_timestamp_expression

AS OF (date_timestamp_expression)

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

[AS] correlation_name

Specifies an alias for the table that is referenced by table_name.

(subquery)

Specifies the subquery that defines the derived table contents.

[AS] derived_table_name

Specifies an assigned name for the temporary derived table.

column_name

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

joined_table

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

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

CROSS JOIN

Specifies 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

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

[INNER] JOIN

Specifies 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

Specifies 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

Specifies 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

Specifies 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

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