> [!NOTE] Hash Join > <table> > <tr> > <td width="25%"><img src="assets/ex_hashjoin.png"></td> > <td>A join strategy designed for large, unsorted datasets. The engine builds a hash table in memory (using <code>work_mem</code>) from the smaller 'inner' relation. It then scans the 'outer' relation, probing the hash table for matches. High memory efficiency for equijoins.</td> > </tr> > </table> > > ```sql > -- Joining two tables using a hash table > EXPLAIN (ANALYZE, COSTS, BUFFERS, VERBOSE) > SELECT * FROM animals a > JOIN species s ON a.species_id = s.id; > ``` > > ```text > Hash Join (cost=2.11..446.11 rows=20000 width=42) (actual time=1.350..4.468 rows=20000 loops=1) > Output: a.id, a.name, a.species_id, a.created_at, s.id, s.name, s.diet_type > Inner Unique: true > Hash Cond: (a.species_id = s.id) > Buffers: shared hit=148 read=1 > -> Seq Scan on public.animals a (...) > -> Hash (...) > ``` > > <table> > <tr> > <td rowspan="5" width="25%"><img src="assets/ex_hash_semi_join.svg"></td> > <td><b>Performance</b></td><td>High performance for large datasets; prefers cases where the inner relation fits in <code>work_mem</code>.</td> > </tr> > <tr><td><b>Factors</b></td><td>Size of join tables, hash table size, and hash collision rates.</td></tr> > <tr><td><b>Cost</b></td><td><code>build cost + probe cost + cpu_operator_cost * rows joined</code></td></tr> > <tr><td><b>Operates on</b></td><td><a href="Structures/Result Set">Result Set</a></td></tr> > <tr><td><b>Workloads</b></td><td><a href="Workloads/IO/BufFile/BufFileRead">IO: BufFileRead</a>, <a href="Workloads/IO/BufFile/BufFileWrite">IO: BufFileWrite</a>, <a href="Workloads/LWLock/Parallel/ParallelHashJoin">LWLock: ParallelHashJoin</a>, <a href="Workloads/LWLock/Buffers/BufferContent">LWLock: BufferContent</a>, <a href="Workloads/IPC/Hash/HashBuildElect">IPC: HashBuildElect</a>, <a href="Workloads/IPC/Hash/HashBuildHashInner">IPC: HashBuildHashInner</a>, <a href="Workloads/IPC/Hash/HashBuildHashOuter">IPC: HashBuildHashOuter</a>, <a href="Workloads/IPC/Hash/HashBatchLoad">IPC: HashBatchLoad</a></td></tr> > <tr><td colspan="3"><b>Description</b>: Joins tables using a hash table.</td></tr> > </table>