In a nested join, the WHERE conditions specify an equijoin with a constant value for a unique index in one table, and those conditions match a column of that single row to a primary or secondary index of the second table. As the only join type that need not touch all AMPs in order to join the relations, the nested join is a high-performing join.
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.