Join Execution Algorithms (Nested Loop, Hash Join, Merge Join):

In Simple Words:

• Nested Loop: For each row in outer table, scans inner table (great when inner table has an index).
• Hash Join: Builds an in-memory hash table of smaller table, then scans larger table probing the hash table (best for large unindexed joins).
• Merge Join: Both datasets must be pre-sorted on join keys; scans both in lockstep.

-- Force query planner to use Hash Join for testing:
SET enable_nestloop = off;
SET enable_mergejoin = off;
EXPLAIN SELECT * FROM large_table_a a JOIN large_table_b b ON a.key = b.key;
-- Plan displays: Hash Join (Hash Cond: (a.key = b.key))