# 3.2.2 The Matchmakers (Joins) ![The Join Factory](assets/arch_plan_joins_retry.png) Once two different sets of suitcases have been pulled from the warehouse by the Scouts, they must be combined. The Query Algebra handles this with **Join Nodes**, implemented in the chaotic, high-energy **Suitcase High-Five Factory**. Depending on how sorted the suitcases are, the elephant chooses a different sorting station. ## Nested Loop: The Hamster Wheel The **[[Operations/NestedLoop|Nested Loop]]** is the simplest and most brutal matchmaker. Imagine one worker holding a suitcase from List A. For *every single suitcase in List A*, a second worker is forced to run through *every single suitcase 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 lists, otherwise the workers will simply collapse from exhaustion. ## Hash Join: The Jellybean Wall If the lists are large, the elephant summons the **[[Operations/HashJoin|Hash Join]]**. This worker takes List A and spends some time tossing all of its suitcases into specific, numbered buckets (a Hash Table) based on a matching ID. This requires a **Desk** (`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 suitcase, 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 desk 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! --- [[Chapter 3/3.2.1 - The Scouts (Scans)|← 3.2.1 - The Scouts (Scans)]] | [[Chapter 3/3.2 - Query Algebra|↑ 3.2 - Query Algebra]] | [[Chapter 3/3.2.3 - The Organizers (Aggregations)|3.2.3 - The Organizers (Aggregations) →]]