Join Index with Outer Join in Its Definition - Teradata VantageCloud Lake

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 can be specified in the table definition explicitly as a primary index or as unique. Therefore, 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;