Join strategies
- SQL course
- Expert
- Lesson 16/45
- 5 min
Learn hash, merge and nested loop joins.
Join strategies
The optimizer uses three main join strategies: nested loop (small tables), hash join (large equality join), merge join (sorted data).
-- Nested Loop: küçük + küçük
-- Hash Join: büyük = büyük
-- Merge Join: sıralı + sıralıThree join strategies exist.
Join strategies — details
A hash join builds a hash table in memory from one side and scans the other. If RAM runs out it spills to disk and slows. A merge join is fast when both sides are sorted.
Practice Join strategies
Join orders and customers on customer_id and view the plan with EXPLAIN.
Starter code
-- join plan
EXPLAIN SELECT * FROM orders oSample solution
EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;Expected output
Hash Join on orders x customers
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Which strategy for a large equality join?
- Nested loop
- A hash join Correct answer
- Seq scan
- Index scan
Explanation: A hash join is used for a large equality join.
2 Join for small tables
Question
____ Join
Answer
Nested Loop JoinExplanation: Nested Loop is the right answer.
3 Join strategies: match the terms
| Term | Meaning |
|---|---|
nested loop |
nested |
hash join |
hash |
merge join |
merge |
small |
small |