Part 3 of 5 · 1 min read
Hash join
Build an in-memory hash table from one side, then probe it once per row of the other side.
Think of sorting one box of receipts into labeled bins by customer name first. Then walk through a second box of receipts just once, dropping each one straight into the matching bin.
In plain words: a hash join first builds an in-memory hash table from one of the two tables, called the build side, keyed by the join column. It then scans the other table, called the probe side, once, looking up each row's join column value directly in the hash table instead of scanning anything repeatedly. This avoids the repeated inner scans a nested loop join relies on, making a hash join a strong choice when neither table is small and no useful index exists on the join column.
A worked example. Joining two large tables with no index on the join column builds a hash table from the smaller of the two, then scans the larger table once, probing the hash table for each row.
The key lines. No new SQL; the hash join is chosen internally by the optimizer for the same kind of JOIN statement shown above, when conditions favor it.
Hash join
A hash join builds an in-memory hash table from one table, the build side, keyed by the join column, then scans the other table once, probing the hash table for matches, avoiding repeated scans.
Example: sorting one box of receipts into labeled bins, then walking through a second box just once, dropping each into its matching bin, mirrors a hash join.