Once the join order has been established, each join node is analyzed to select from among the following algorithms:
Augmented nested loop (ANL) join
Merge join
Nested loop join
The optimizer generates, when possible, candidates for each algorithm. For each join node, candidates are generated by:
Checking whether the algorithm's requirements are satisfied. For example, the ANL join needs an index on one of the join columns.
Assuming the algorithm is usable, when multiple predicates reference the two tables being joined, choosing a predicate (or set of predicates) with the lowest cost.
Once a set of candidates exists, the optimizer selects the least costly candidate.
Augmented nested loop (ANL) join