Nested loop vs hash join: how the database actually performs a JOIN
Your SQL never says HOW to do a join. The planner picks from three strategies based on table size and indexes. Knowing them tells you what a slow join is actually doing.
Writing JOIN ... ON tells the database what to match. It never says how to actually go find
the matches. Under the hood, the planner picks from three different strategies, depending on table
sizes and available indexes.
🎯 Explain Like I'm Hired Imagine matching two guest lists by name. A nested loop: for each name on the short list, scan the whole long list looking for a match, fine if the short list really is short. A hash join: build a quick lookup dictionary out of the smaller list, then walk the bigger list once, checking the dictionary, better when both lists are large. A merge join: if both lists are already alphabetized, walk them together in one pass, like merging two sorted decks of cards. Example: joining a 10-row lookup table to a 10-million-row table is a textbook nested loop (with an index); joining two 10-million-row tables with no supporting index is a textbook hash join.
Sign up to keep reading
Sign up free to unlock the worked examples, edge cases, and interview traps below.