Three Ways to Join
When two tables are joined, the engine picks a physical algorithm. The three classic choices are nested loop, hash, and merge joins.
How Each Works
- Nested loop join scans the outer table and probes the inner table for each row. It is great when one side is tiny or an index makes the probe cheap.
- Hash join builds a hash table on the smaller input keyed by the join column, then probes it with the larger input. It shines for large unsorted equality joins.
- Merge join requires both inputs sorted on the join key, then walks them together like a zipper. It excels when inputs are already sorted or indexed.
Picking One
The optimizer chooses based on input sizes, available indexes, sort order, and whether the join is an equality. Hash and merge handle big inputs; nested loop handles small or well indexed ones.
Key idea
Nested loop joins suit small or indexed inputs, hash joins suit large unsorted equality joins, and merge joins suit inputs already sorted on the join key.