15.00 - Join Index With Outer Join in Its Definition - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Join Index With Outer Join in Its Definition

Consider the following join index defined on the three tables t1, t2, and t3. Tables t1 and t2 are joined with an inner join, and the result is joined with table t3 using an outer join. The outer table is the result of joining tables t1 and t2.

     CREATE JOIN INDEX ji1 AS
     SELECT a1, a2, a3 
     FROM (t1 INNER JOIN t2 ON a1 = a2) 
     LEFT OUTER JOIN t3 ON a1 = a3;

Column a3 is the unique primary index for table t3. Column a3 might be specified in the table definition explicitly as a primary index, or simply as unique. This means that all of the rows from the join of t1 with t2 are in the join index exactly once, either in the matched set, or in the unmatched set. Therefore, the following query can be satisfied by the join index:

     SELECT a1, a2 
     FROM t1, t2 
     WHERE a1 = a2;