15.00 - Using Outer Joins in Join Index Definitions - Teradata Database

Teradata Database Design

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

Using Outer Joins in Join Index Definitions

Join indexes should be defined using outer joins if they are intended to cover both inner and outer join queries. To understand how this is possible, an outer join can be thought of as producing a result consisting of two sets of rows. The first set corresponds to the set of matched rows obtained when a row from the outer table matches one or more rows from the inner table (this set corresponds to the set of rows defined by the inner join with the same join condition). The second corresponds to the set of unmatched rows: those rows from the outer table that do not match any rows from the inner table.

Except for the presence of the unmatched row set, an outer join is the same as an inner join, and produces the same result. Therefore if an inner join query can be completely satisfied by the matched set of rows from an outer join index, the Optimizer uses it.