Multitable Joins - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

This topic describes some of the operations that are unique to joins of more than two tables.

Temporary Derived Table

When joining three or more tables, the join process logically creates a temporary derived table that is defined only for the duration of the SELECT operation. Depending on various factors, the Optimizer might rewrite a request in such a way that it generates a query plan that is very different from the 1:1 correspondence between the coded query and its actual implementation that this simplified description implies.

Such a temporary table is also called a joined table. Teradata refers to this kind of temporary table as a spool. Consider this example:

     table_r
     LEFT OUTER JOIN table_S ON join_condition
     RIGHT OUTER JOIN table_t ON join_condition

When table_r is left outer joined to table_s according to the first join condition, a derived table is created. It is that derived table, not a persistent base table, that is then right outer joined (as a new derived left table) to table_t according to the second join condition.

ON Clause Evaluation Order

The result of an inner join of two tables is not changed if rows from table_a are joined to rows from table_b, or if rows from table_b are joined to rows from table_a. In terms of set algebra, inner joins are both commutative and associative. This is true no matter how many tables are (inner) joined.

Because inner joins are both commutative and associative, the Optimizer can select the best join order for inner joins arbitrarily and the end result is always identical.

Outer joins, on the other hand, are rarely either commutative or associative. The Optimizer cannot select an arbitrary best join order for outer joins because neither commutativity nor associativity can be assumed, nor does it have any way to know what specific result you intended to produce with the query you presented to it.

To outer join three tables, you must specify their join order explicitly by placing the ON clause in an appropriate position within the FROM clause to ensure that the join is evaluated correctly.

The Optimizer follows these rules the to generate outer joins.

  • The first ON clause in the query (reading from left to right) is evaluated first.
  • Any ON clause applies to its immediately preceding join operation.

Example: Outer Join

The two following outer join examples evaluate differently because of the order in which their ON clauses are expressed.

For the first example, consider the following outer join.

     table_r
     LEFT OUTER JOIN table_s ON join_condition
     RIGHT OUTER JOIN table_t ON join_condition

This request is evaluated according to the following steps:

  1. table_r is left outer joined to table_s according to the first join condition.
  2. The derived table (the table resulting from the first join operation, a newly derived “left” table with respect to what follows) is right outer joined to table_t according to the next join condition.

Using parentheses to indicate order of evaluation, you could rewrite the example in this way:

     (
     table_r
     LEFT OUTER JOIN table_s ON join_condition
     )
     RIGHT OUTER JOIN table_t ON join_condition

You can use parentheses to write a SELECT statement that performs an outer join on three or more tables.

The second example places the ON clause in another position, providing different result.

     table_r
     LEFT OUTER JOIN table_s
     RIGHT JOIN table_t ON join_condition
     ON join_condition

This statement is evaluated according to the following steps:

  1. table_s is right outer joined to table_t according to the first join condition.
  2. The derived table (a newly derived “right” table) is left outer joined to table_r according to the next join condition.

Using parentheses to indicate order of evaluation, you could rewrite the example as in the following.

     table_r
     LEFT OUTER JOIN
     (
     table_s
     RIGHT JOIN table_t ON join_condition
     )
     ON join_condition

Example: Left Outer Join

Suppose you add a fourth table, table_u to the requests of Example: Outer Join, as in the following join operation:

     table_r
     LEFT OUTER JOIN table_s ON join_condition
     JOIN table_t
     LEFT OUTER JOIN table_u ON join_condition
     ON join_condition

This statement is evaluated as follows:

  1. table_r is left outer joined to table_s according to the first join condition.
  2. table_t is then left outer joined to table_u according to the second join condition.
  3. The two derived tables are inner joined according to the third join condition.

Using parentheses to indicate order of evaluation, you could rewrite the example as follows:

     (
      table_r
      LEFT OUTER JOIN table_s ON join_condition
     )
     JOIN
     (
      table_t
      LEFT OUTER JOIN table_u ON join_condition
     )
     ON join_condition

Notice that the join condition specified in the ON clause for the inner join (JOIN in the example) is separated from that operation by the join condition that specifies how the left outer join of table_t and table_u is to be performed.