Join Type vs Join Strategy
The type is yours, the strategy is Spark's
Every join carries 2 independent decisions. The join type is yours, written as the third argument: "inner", "left", "right", "full", "left_semi" or "left_anti". It defines which rows the result contains. The join strategy is Spark's, chosen while planning: broadcast or sort-merge, with the rules of the previous 2 sections deciding which. It defines how the matching rows are brought together and how much data moves. Change the type and the result changes; change the strategy and the result stays the same while the stages and shuffles change.
| Type | Rows in the result | A question it answers |
|---|---|---|
| inner | Pairs that match on the key | Which orders have a product in the catalog? |
| left | Every left row, with right columns or nulls | Every order, with its product where the catalog has one |
| right | Every right row, with left columns or nulls | Every product, with its orders where any exist |
| full | Every row from both sides, matched where possible | Orders and products together, nothing dropped |
| left_semi | Left rows that have a match, left columns only | Which orders reference a catalog product? |
| left_anti | Left rows that have no match | Which orders reference a product missing from the catalog? |
Strategy sits on the other axis. The same orders.join(products, "product_id", "left") returns the same rows whether Spark broadcasts products or shuffles both sides; the row count, the nulls, and the column values do not depend on the strategy. What depends on it is the plan: 0 shuffles and a single stage, or 2 shuffles and 3 stages.
The one place the axes touch
A broadcast join can only broadcast the side whose unmatched rows the join type is allowed to drop. Take the left join above. Every orders row must appear, matched or not. An executor holding a partition of orders can produce that correctly only if it has the whole of products, because a product missing from a partial copy might exist on another executor. Broadcasting products gives every executor the whole copy, so it works. Broadcasting orders would not, since an executor holding part of products could not tell a true non-match from a match it cannot see. So a left join broadcasts only its right side, a right join only its left side, and a full outer join neither: full outer joins run as sort-merge.
If you hint the side the type forbids, Spark drops the hint and shuffles. A left join that stays slow after you added F.broadcast() deserves a second look at which side the hint is wrapped around.
Debug each axis on its own
When a join misbehaves, name the symptom first, because the 2 axes have different tools. Wrong rows in the result point at the type and the keys: an inner join where a left join was meant drops rows, a key column with nulls drops rows in an inner join because a null never equals a null, and a duplicated key multiplies rows, which the next section covers. A slow join points at the strategy. explain() names it on the join's own line of the physical plan, and each shuffle shows up as an Exchange hashpartitioning beneath the join.
| Line in the physical plan | What it tells you |
|---|---|
| SortMergeJoin or BroadcastHashJoin | The strategy Spark chose for this join |
| Exchange hashpartitioning on product_id | A shuffle of 1 side on the key: 2 beneath a sort-merge join, none beneath a broadcast join |
- Rows are wrong
- Check the join type argument
- Check for nulls in the key column
- Check for duplicate keys on either side
- Changing the strategy will not change a row
- Rows are right but slow
- Run explain() and find the join line
- Count the Exchange lines beneath it
- Ask whether a side is small enough to broadcast
- Changing the type would change the answer
events.join(users, "user_id", "left") returns the rows you expect but runs for 40 minutes, and explain() shows SortMergeJoin with 2 Exchange lines. users is around 30MB in memory. Which change speeds it up without changing a single result row?
- Change the type: events.join(users, "user_id", "inner") to skip the unmatched rows
- Wrap the left side: F.broadcast(events).join(users, "user_id", "left")
- Raise spark.sql.shuffle.partitions so the shuffle spreads across more tasks
- Wrap the right side: events.join(F.broadcast(users), "user_id", "left")
The cell runs a left join of users to user_profiles with the right side broadcast, which the type allows, and returns its row count: every user, with country and plan null where no profile exists. Note the number, change "left" to "inner", and run again. Each user without a profile drops out and the count falls by that many, while the strategy, a broadcast of user_profiles, stays put.
About this lesson
This section is part of the Joins: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.