Join Elimination and Referential Integrity - Teradata Database

Join Elimination and Referential Integrity

Join elimination is a process undertaken by the Optimizer to eliminate redundant joins based on information from referential integrity constraints.

The following conditions eliminate a join.

A referential integrity relationship exists between the two tables.

Request conditions are conjunctive, meaning they are ANDed rather than ORed.

This means that if any single condition in an ANDed set fails, the entire condition fails.

The request does not contain reference columns from the PK table, other than the PK columns, including the SELECT, WHERE, GROUP BY, HAVING, ORDER BY columns.

PK columns in the WHERE clause appear only in PK-FK joins.

IF…	THEN…
the preceding conditions are met	the PK join is removed from the query. all references to the PK columns in the query are mapped to the corresponding foreign key columns.
foreign key columns are nullable	Teradata Database adds a NOT NULL condition to the request.