SQL Academy · Lesson

Hash Join vs Merge Join vs Nested Loop

Recognise the three main join strategies, their cost profiles, and when each is the planner's best choice.

Hash Join vs Merge Join vs Nested Loop is a free SQL Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Three Join Strategies

PostgreSQL has three physical join algorithms:

Nested Loop — for each outer row, scan inner
Hash Join — build hash of inner, probe with outer
Merge Join — both sides sorted, merge in lockstep

Nested Loop

Simplest: outer × inner. Fast when inner has a good index AND outer is small:

EXPLAIN ANALYZE
SELECT * FROM users u JOIN orders o ON o.user_id = u.id
WHERE u.id = 42;
-- Nested Loop
--   -> Index Scan on users where id = 42  (rows=1)
--   -> Index Scan on orders_user_id_idx    (rows=5)

When Nested Loop Wins

Outer side has few rows AND inner has an index on the join key — Nested Loop is extremely fast. Worst case: O(outer × inner).

Hash Join

Build a hash table on one side (usually the smaller), then probe with the other. Great for joining two large tables when no useful index exists on the join key:

EXPLAIN ANALYZE
SELECT * FROM big_a a JOIN big_b b ON a.key = b.key;
-- Hash Join (cost=10000..50000)
--   -> Seq Scan on big_a
--   -> Hash
--       -> Seq Scan on big_b

When Hash Join Wins

Two medium-to-large tables, no good index on the join key, or the planner needs many rows. Memory bound: hash table must fit in work_mem or it spills to disk.

Merge Join

Both sides sorted on the join key, walked together. Great when both sides are already sorted (e.g. by an index that matches):

EXPLAIN ANALYZE
SELECT * FROM big_a a JOIN big_b b ON a.key = b.key
ORDER BY a.key;
-- Merge Join
--   -> Index Scan on big_a (a.key ASC)
--   -> Index Scan on big_b (b.key ASC)

When Merge Join Wins

Two large pre-sorted inputs. Linear scan, low memory. The sort cost matters — if both sides need explicit sorting, hash usually wins.

Choosing Between Them

The planner picks based on:

Estimated row counts
Available indexes
Memory (work_mem)
Cost constants in postgresql.conf

Forcing a Strategy (Diagnostic Only)

For debugging, you can disable strategies:

SET enable_hashjoin = off;
SET enable_mergejoin = off;
SET enable_nestloop = off;
-- Re-run EXPLAIN to see what the planner picks instead.
-- NEVER persist these in production.

Spilling to Disk

If the hash table or sort exceeds work_mem, the operator spills temp files to disk — much slower. Either raise work_mem or rewrite the query.

Parallel Joins

PostgreSQL can parallelise Hash Join and Merge Join (and seq/index scans) — visible as Parallel Hash Join with Workers Planned in EXPLAIN.

Reading the Choice

In EXPLAIN ANALYZE, the join node name tells you the strategy. The choice is almost always right — when it isn't, focus on stats and indexes before forcing strategies.

Recap

Three join strategies serve different shapes.

Nested Loop: small outer + indexed inner
Hash: large tables, no useful index
Merge: pre-sorted inputs

Quick Check

You're joining two 10-million-row tables on an unindexed column. Which join algorithm is the planner likely to choose?

Frequently asked questions

Is the “Hash Join vs Merge Join vs Nested Loop” lesson free?

Yes — the full text of “Hash Join vs Merge Join vs Nested Loop” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “Hash Join vs Merge Join vs Nested Loop”?

Recognise the three main join strategies, their cost profiles, and when each is the planner's best choice. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Hash Join vs Merge Join vs Nested Loop” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Academy