FROM source - Teradata Vantage

For more information about the FROM clause, see FROM 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.

table_name

The name of a table containing the column or columns to include in the join index or the name of a table to join with at least one other table to form a join index. You cannot define a join index on any of the following database objects:

• Global temporary tables
• Join indexes
• Journal tables
• Ordinary views
• Queue tables
• Recursive views
• Volatile tables

Each table in the FROM clause must have at least one column in either the fixed column_name list or in the repeating column_name list, if you are using row-compression.

In a single-table join index, the column_name list must contain all columns in the FROM clause table.

You can only define column partitioning for a single-table join index.

Each table name can be qualified by a database name if necessary and can have a maximum of 64 different columns referenced in the entire join index definition.

Every table must be connected to at least one other table with a join condition. The cross join and full outer join conditions are not supported.

The maximum number of table_name references allowed is the same as the system limit for SELECT requests.

A FROM clause can have any number of simple tables, but is limited to a single joined non-simple table expression. For example, you can use a simple table list or a single non-simple table expression:

FROM table_1, table_2, table_3,  table_4
FROM (table_1 INNER JOIN table_2 ON x1 = x2)
INNER JOIN (table_3 ON x1 = x3)

The following is invalid, because it has more than one nonsimple table expression:

 FROM (table_1 INNER JOIN table_2   ON table_1 x1 = x2),(table_3 
 INNER JOIN table_4 ON x3 = x4)

correlation_name

An alias for table_name.

The keyword AS preceding correlation_name is optional.

joined_table

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.

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.