Join strategies

Join types vs join strategies

The type is part of what your query means. The strategy is an execution choice Spark makes during physical planning: the same inner join can run as any of the five strategies depending on table sizes, the join condition, configs and hints. The PySpark join and left_semi / left_anti lessons cover the types; this lesson covers the strategies.

The five strategies

An equi-join has at least one equality between a column of each side in its condition (o.customer_id = c.customer_id, possibly ANDed with other conditions). Only equi-joins can use the hash and sort strategies, because those need a key to partition and match on. A condition with no such equality, like e.ts BETWEEN s.start AND s.end, a.x < b.y, or two equalities joined by OR, is a non-equi join and can only run as a nested loop.

How Spark picks one

For an equi-join, Spark walks this list and takes the first strategy that applies:

1. 1
   Hints. If you hinted a strategy and the join supports it, Spark uses it. When both sides carry different hints, the priority is BROADCAST, then MERGE, then SHUFFLE_HASH, then SHUFFLE_REPLICATE_NL.
2. 2
   Broadcast hash join if the join type allows broadcasting a side and that side's estimated size is below spark.sql.autoBroadcastJoinThreshold (10 MB). If both sides qualify, the smaller is broadcast.
3. 3
   Shuffle hash join if spark.sql.join.preferSortMergeJoin is false (it is true by default), one side is small enough to hash per partition (below the broadcast threshold × the number of shuffle partitions), and it is at least three times smaller than the other side.
4. 4
   Sort-merge join if the join keys are sortable. Most joins of two large tables end here.
5. 5
   Cartesian product if it is an inner or cross join.
6. 6
   Broadcast nested loop join as the last resort. It handles every case, even if that means broadcasting a large side.

For a non-equi join the list is shorter: a broadcast hint or a side below the threshold gives a broadcast nested loop join; otherwise an inner or cross join becomes a Cartesian product; otherwise Spark falls back to a broadcast nested loop join anyway.

Why sort-merge is the default

A shuffle hash join skips the sort, so on paper it is faster. But its hash table for a partition must fit in memory, and one oversized or skewed partition fails the tasktask: The work for one partition in one stage, run on one CPU core. Learn more →. A sort-merge join sorts with an external sorter that spillsspill: Writing data to local disk because it does not fit in memory. Slower, but the job keeps running. Learn more → to disk, and the merge holds only the rows of the current key, so it slows down under pressure instead of failing. That robustness is why Spark prefers it. Shuffle hash join is worth trying when one side is too big to broadcast but small per partition, and keys are not skewed.

Which strategies support which join types

The pattern: the side that must keep unmatched rows is streamed, so only the other side can be the broadcast or build side.

Hints

orders.join(customers.hint("broadcast"), "customer_id")      # BroadcastHashJoin
orders.join(customers.hint("merge"), "customer_id")          # SortMergeJoin
orders.join(customers.hint("shuffle_hash"), "customer_id")   # ShuffledHashJoin
events.join(sessions.hint("shuffle_replicate_nl"),
            events.ts.between(sessions.start, sessions.end))  # CartesianProduct

SELECT /*+ BROADCAST(c) */ * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
SELECT /*+ MERGE(c) */ * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
SELECT /*+ SHUFFLE_HASH(c) */ * FROM orders o JOIN customers c ON o.customer_id = c.customer_id;
SELECT /*+ SHUFFLE_REPLICATE_NL(s) */ * FROM events e JOIN sessions s ON e.ts BETWEEN s.start AND s.end;

A hint is a request, not an order. Spark ignores a hint it cannot honour, such as broadcasting the preserved side of an outer join or a shuffle hash join on a non-equi condition, and logs a warning. MERGEJOIN and SHUFFLE_MERGE are aliases of MERGE.

Making a slow non-equi join fast

• ✗OR across two keys — a.id = b.id OR a.email = b.email has no single equality, so it becomes a nested loop. Rewrite it as two equi-joins and union the results (deduplicating if a row can match both ways).
• ✗Range joins — Matching an IP to an IP range, or an event to a time window, has no equality. Add one the condition already implies: a coarse bucket (the /16 prefix of the IP, the hour of the timestamp). Explode each range into the buckets it covers, join on bucket equality, and keep the range test as an extra condition. The join becomes a sort-merge join on the bucket.
• ✗Functions on the key — A join on lower(a.code) = b.code is still an equi-join, but nothing about it can use bucketing or existing partitioning. Normalise the key once, before the join.

Check what Spark chose

Search the physical plan for the join node. SortMergeJoin with an Exchange and Sort under both sides is the expensive default; BroadcastHashJoin with a BroadcastExchange under one side avoids the big shuffle; BroadcastNestedLoopJoin or CartesianProduct on two large inputs is the red flag. With AQE, check the final plan in the SQL tab of the Spark UI rather than the plan before execution.

Common mistakes

• ✗Confusing join type with join strategy — A left join is not a strategy. It can run as a broadcast hash, shuffle hash, sort-merge or nested loop join.
• ✗Forcing shuffle hash joins on skewed data — One hot key builds a hash table that does not fit, and the task fails instead of spilling.
• ✗Not noticing a nested loop — A non-equi condition on two large tables quietly turns into rows × rows comparisons.
• ✗Expecting a hint to always win — Spark drops hints that the join type or condition cannot support.