joined_table - Advanced SQL Engine - Teradata Database

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.

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 or keywords that are explicitly prohibited such as DEFAULT or PARTITION. For information about the DEFAULT built-in function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. For information about the PARTITION keyword, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

All expressions must be aliased. Otherwise, Vantage 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.

correlation_name

An alias for table_name. The keyword AS preceding correlation_name is optional.