Hash Join (Optimizer Strategy)

Hash Join (Optimizer Strategy) is a SQL statement in the JOIN Types category. Hash join builds an in-memory hash table on the smaller table, then probes with the larger table. O(n+m) complexity. The syntax is -- Optimizer selects hash join when joining large, unsorted tables on equality conditions.. It returns hash join concept. A typical example: -- Hash Join steps: -- 1. Read the smaller table (build phase) -- 2. Create hash table in memory using join key -- 3. Read the larger table (probe phase) -- 4. For each row, check hash table for matches SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id; -- If both tables are large with no indexes -- Optimizer likely chooses hash join -- Pros: --… A close relative is INNER JOIN, which returns only rows where there is a match in BOTH tables. The most common JOIN type.