Join Strategies: Nested Loop, Hash, Merge
You'll learn: How databases choose and execute different algorithms to physically combine tables during joins.
The Core Idea
When you write a join query, the database doesn't just magically merge tables—it picks one of several execution strategies based on table size, indexes, and available memory. Think of these as different recipes for combining two ingredient lists: some work better when one list is tiny, others when both are huge.
Nested Loop Join
Imagine comparing two phone contact lists by checking every person in List A against every person in List B. That's a nested loop—the database scans through all rows of one table (outer loop), and for each row, scans the other table (inner loop). This works well when the inner table is small or has an index on the join key.
Hash Join
Picture creating a hash table (like a dictionary) from one table, then quickly looking up matches in that dictionary for each row of the second table. Hash joins excel with large tables that fit in memory—they're fast but require space to build the hash structure.
Merge Join
If both tables are already sorted by the join key, you can walk through them simultaneously, like merging two sorted decks of cards. Merge joins are efficient for pre-sorted data or when the database can sort cheaply, advancing through both tables in lockstep.
Reading Execution Plans
Use EXPLAIN or EXPLAIN ANALYZE before your query to see which strategy the database chose. You'll see terms like "Nested Loop," "Hash Join," or "Merge Join" in the output—this reveals the database's decision-making.
Key Takeaway: Databases use nested loop (good for small tables), hash (fast for large unsorted tables), or merge (efficient for sorted data) join strategies, which you can inspect via execution plans to understand and optimize query performance.