Nested Loop Join
A nested loop join scans the inner table for matches for each outer-table row; suited to small tables or indexed inner tables.
Purpose
To understand the most basic join algorithm and its applicable scenarios (small tables or index acceleration).
Mechanism
for each outer row: scan the inner table for matches. With an index on the inner join column it becomes an Index Nested Loop at O(N*log M); without one it is an O(N*M) full scan.
Use cases
- Join algorithm selection
- Indexed join optimization
- Small table joins