A nested join is a join for which the WHERE conditions specify an equijoin with a constant value for a unique index in one table and those conditions also match some column of that single row to a primary or secondary index of the second table. The nested join is one of the most high-performing joins available because it is the only join type that need not touch all AMPs in order to join the relations.
About Nested Joins
There are two types of nested join: local and remote:
- Local nested join is more commonly used than remote nested joins. Local nested join is described in Local Nested Join.
- Remote nested join is described in Remote Nested Join.
Teradata Database uses the following general process to perform a nested join:
- Retrieve the single row that satisfies the WHERE conditions from the first relation.
- Use that row to locate the AMP having the matching rows on which the join is to be made.
Nested join is very cost-effective because it is the only join type that does not always use all AMPs. Because of this, nested join is generally the best choice for OLTP applications.
The Optimizer can select a nested join only if both of the following conditions are true:
- There is an equality condition on a unique index of one table.
- There is a join on a column of the row specified by the first table to any primary index or USI of the second table. In rare cases, the index on the second table can be a NUSI.