Join strategies

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.

Exercise Join strategies

Join orders and customers on customer_id and view the plan with EXPLAIN.

Show example solution

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id;

Test yourself

Which strategy for a large equality join?
- Nested loop
- A hash join
- Seq scan
- Index scan
Show answer

A hash join — A hash join is used for a large equality join.
Join for small tables
```
____ Join
```
Show answer
```
Nested Loop Join
```
Join strategies: match the terms

nested loop

nested
hash join

hash
merge join

merge
small

small

Next lesson: Plan cache

We Are Enhancing Your Experience

Cookie Preferences

Strictly Necessary Cookies

Analytical Cookies