15.10 - Joined Table Clause - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

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.

INNER JOIN
Keywords defining the join as an ordinary join.
LEFT OUTER JOIN
RIGHT OUTER 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.
table_name
Name of a single table if no tables are joined in this clause.
database_name
An optional database name if table_name is contained in a database other than the current database.
user_name
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.