15.10 - Joined Table Clause - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
Release Number
December 2015
Content Type
Programming Reference
Publication ID
English (United States)

A recursive use of the syntax of a joined table within the definition of a joined table. The joined table clause is used only for multitable join indexes and cannot be specified in the definition of a column-partitioned join index.

Keywords defining the join as an ordinary join.
Keywords defining the join as either a left or a right outer join. Full outer join is not supported for join indexes. For most non-aggregate applications, this is the better choice for defining a join index. You cannot specify outer joins for a join index that also contains aggregate operators in its definition. When you specify an outer join, the following rules apply:
  • The outer table joining column for each condition must be contained in either the fixed column_name list or in the repeating column_name list, if using row-compression.
  • The inner table of each join condition must have at least one non-nullable column in either the fixed column_name list or in the repeating column_name list, if using row-compression.
  • The select list cannot specify a CASE or COALESCE column expression that is defined on a column set from the inner table of the outer join.
ON search_condition
A conditional expression for eliminating any rows from a query that do not evaluate to TRUE.
You can base an expression on a UDT column or an expression that references at least one column, with the following exceptions:
  • Expressions that include aggregate or OLAP functions
  • UDF expressions
  • Built-in functions that are explicitly prohibited such as DEFAULT and PARTITION. For details about these functions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.
All expressions must be aliased. Otherwise, Teradata Database returns an error to the requestor.
Multiple join conditions must be connected with the AND logical operator.
There must be an equality join between columns from the outer table and the inner table of the outer join. Then, you can specify any other conditions, such as an inequality between the joined tables and a single table condition on either of the joined tables.
Data types for any columns used in a join condition must be drawn from the same domain because neither explicit nor implicit data type conversions are permitted.
You cannot specify the ROWID keyword in the search condition of a join operation.
Name of a single table if no tables are joined in this clause.
An optional database name if table_name is contained in a database other than the current database.
An optional user name if table_name is contained in a user other than the current or user.
AS correlation_name
An alias for table_name. The keyword AS preceding correlation_name is optional.