There are four possible execution methods for merge joins:
Full-merge join – the two tables being joined have useful indexes on the join columns. The tables do not need to be sorted, but can be merged using the indexes.
Left-merge join – sort the inner table in the join order, then merge with the left, outer table.
Right-merge join – sort the outer table in the join order, then merge with the right, inner table.
Sort-merge join – sort both tables, then merge.
Merge joins always operate on stored tables – either user tables or worktables created for the merge join. When a worktable is required for a merge join, it is sorted into order on the join key, then the merge step is performed. The costing for any merge joins that involve sorting includes the estimated I/O cost of creating and sorting a worktable. For full-merge joins, the only cost involved is scanning the tables.
Figure 6-2 provides diagrams of the merge join types.
