# 3.2.2 The Plate Assemblers (Joins) Once two different sets of plates have been pulled from the depot by the Runners, they must be combined. The Query Algebra handles this with **Join Nodes**, implemented in the chaotic, high-energy **Plate Assembly Line**. Depending on how sorted the plates are, the elephant chooses a different assembly station. ## Nested Loop: The Hamster Wheel The **[[Operations/NestedLoop|Nested Loop]]** is the simplest and most brutal assembly method. Imagine one worker holding a plate from List A. For *every single plate in List A*, a second worker is forced to run through *every single plate in List B* to see if they match. If List A has 100 items and List B has 100 items, the second worker runs the loop 10,000 times! The elephant considers this acceptable only for tiny orders, otherwise the staff will simply collapse from exhaustion. ## Hash Join: The Jellybean Wall If the orders are large, the elephant summons the **[[Operations/HashJoin|Hash Join]]**. This worker takes List A and spends some time tossing all of its plates into specific, numbered buckets (a Hash Table) based on a matching ID. This requires a **Kitchen Counter** (`work_mem`) big enough to hold the entire wall of buckets. Once the wall is built, the worker casually strolls through List B. For each plate, he simply looks at its ID, reaches into the corresponding bucket, and pulls out the match. "Instantly! Like magic!" It is incredibly fast, provided the counter is large enough. ## Merge Join: The Parallel Walkers What if both lists are already perfectly alphabetized (sorted)? The elephant deploys the elegant **[[Operations/MergeJoin|Merge Join]]**. Two workers stand side-by-side with sorted lists. They simply walk down their paths in sync. Since both lists are sorted, if worker A is looking at "Apple" and worker B is looking at "Banana", worker A knows he just needs to catch up. They high-five whenever their IDs match. It requires almost no desk space, but it demands the incredible upfront cost of sorting the lists first! ### The Duel of the Chefs Let's see how the Head Chef chooses his assembly station based on the scale of the Cafe's data. #### 1. The Hamster Wheel (Nested Loop) When joining a single animal to its species, the orders are tiny. The elephant just spins the wheel. ```sql EXPLAIN SELECT animals.name, species.name FROM animals JOIN species ON animals.species_id = species.id WHERE animals.id = 5; -- Result: -- Nested Loop (cost=0.29..16.35 rows=1 width=64) -- -> Index Scan using animals_pkey on animals (cost=0.28..8.29 rows=1 width=12) -- -> Index Scan using species_pkey on species (cost=0.15..8.17 rows=1 width=12) ``` #### 2. The Strategy Shift (Hash Join vs. Nested Loop) When we join `animals` to `orders`, the Head Chef has a crucial choice to make. **State 1: The Buffet Wall (Before Index)** If we haven't hired a Clerk to map the `animal_id` column in the `orders` table, joining them for a few animals is slow. The Head Chef builds a Wall. ```sql EXPLAIN SELECT animals.name, orders.order_time FROM animals JOIN orders ON animals.id = orders.animal_id WHERE animals.id < 10; -- Results: -- Hash Join (cost=12.50..625008.00 rows=100 width=64) -- Hash Cond: (orders.animal_id = animals.id) -- -> Seq Scan on orders (cost=0.00..625000.00 rows=10000000) -- -> Hash (cost=12.45..12.45 rows=10) -- -> Seq Scan on animals (cost=0.00..12.45 rows=10) ``` **State 2: The Express Hand-off (After Index)** Now we hire a Clerk (Index) on `orders(animal_id)`. The Head Chef immediately abandons the wall and switches to a **Nested Loop**. For each animal, the Runner simply "leaps" to their orders! ```sql CREATE INDEX idx_orders_animal ON orders(animal_id); EXPLAIN SELECT animals.name, orders.order_time FROM animals JOIN orders ON animals.id = orders.animal_id WHERE animals.id < 10; -- Results: -- Nested Loop (cost=0.43..85.00 rows=100 width=64) -- -> Seq Scan on animals (cost=0.00..12.45 rows=10) -- -> Index Scan using idx_orders_animal on orders (cost=0.43..6.50 rows=10) ``` Directly following the menu is **thousands of times cheaper** than building a wall from scratch! #### 3. The Parallel Walkers (Merge Join) If the Head Chef knows both lists are already sorted (e.g., by ID), he lets the workers walk side-by-side. ```sql -- Force the Head Chef to think sorting is cheap SET enable_hashjoin = off; EXPLAIN SELECT animals.id, species.id FROM animals JOIN species ON animals.species_id = species.id ORDER BY animals.species_id; -- Result: -- Merge Join (cost=0.30..150.00 rows=1000 width=8) -- Merge Cond: (animals.species_id = species.id) -- -> Index Scan using idx_animals_species on animals (...) -- -> Index Scan using species_pkey on species (...) ``` --- [[Chapter 3/3.2.1 - The Food Runners (Scans)|← 3.2.1 - The Food Runners (Scans)]] | [[Chapter 3/3.2 - Query Algebra|↑ 3.2 - Query Algebra]] | [[Chapter 3/3.2.3 - The Sous Chefs (Aggregations)|3.2.3 - The Sous Chefs (Aggregations) →]]