Part 2 of 5 · 1 min read
Nested loop join
For every row of one table, scan the other table looking for matches.
Think of checking every page of one small notebook against every page of a second notebook, one pairing at a time, to find matching entries.
In plain words: a nested loop join picks one table as the outer table and, for every single row in it, scans the inner table looking for matching rows, repeating this inner scan once per outer row. If the inner table has an index on the join column, this inner scan becomes an index lookup instead of a full scan, which is far cheaper. A nested loop join tends to work well when the outer table is small, since the expensive inner scan only repeats a small number of times.
A worked example. Joining a small customers table (outer) against a large orders table (inner) via an index on orders.customer_id performs one quick index lookup per customer, rather than one full scan of orders per customer.
The key lines.
SELECT * FROM customers JOIN orders ON customers.customer_id = orders.customer_id;
-- a nested loop join, with an index on orders.customer_id, does one lookup per customer row
Nested loop join
A nested loop join scans an inner table once for every row of an outer table, looking for matches; it works best when the outer table is small or the inner table's join column is indexed.
Example: checking every page of a small notebook against a second notebook, one pairing at a time, mirrors a nested loop join.