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.