Developing Apache Spark DataFrame/DataSet API Applications · Topic 4 of 6
Joins, Unions and Broadcast Joins
Combine Spark DataFrames with inner, left, cross and multiple key joins, union and unionByName, and use broadcast joins to avoid shuffling the large side.
- 5 min
- Includes a diagram
- The default join type is
inner. left_semiandleft_antireturn only left columns.- A list of column names as
onkeeps one copy of each key column.
What it is
Joins combine DataFrames side by side by matching rows on keys. Unions stack DataFrames on top of each other. A broadcast join is a join strategy that copies a small table to every executor so the large table does not have to be shuffled.
The exam asks which join type returns which rows, how a union treats columns and duplicates, and when a broadcast join helps.
How it works
Join syntax and types
result = orders.join(customers, on="customer_id", how="left")The default how is inner.
| how | Rows returned |
|---|---|
inner | Only keys present on both sides |
left (left_outer) | All left rows, nulls where the right has no match |
right (right_outer) | All right rows, nulls where the left has no match |
full (outer) | All rows from both sides |
left_semi | Left rows that have a match, left columns only |
left_anti | Left rows with no match, left columns only |
cross | Every left row paired with every right row |
Multiple keys and duplicate columns
Pass a list of names to join on several keys at once, and Spark keeps a single copy of each key column.
a.join(b, on=["order_id", "line_no"], how="inner")A condition expression keeps both key columns, so later references can be ambiguous.
a.join(b, a.id == b.id) # two id columns in the resultDrop one of them, for example .drop(b.id), or rename before joining. Combine conditions with & and parentheses.
Cross join
a.crossJoin(b) returns every pairing, so the output has rows of a multiplied by rows of b. It is easy to explode the data size, so use it deliberately and write crossJoin rather than relying on a join with no condition.
Union and union all
| Call | Behavior |
|---|---|
a.union(b) | Stacks rows by column position, keeps duplicates |
a.unionAll(b) | Alias of union, also keeps duplicates |
a.unionByName(b) | Matches columns by name instead of position |
a.union(b).distinct() | Stacks and removes duplicate rows |
In the DataFrame API, union already behaves like SQL UNION ALL. It does not remove duplicates. In Spark SQL text, UNION removes duplicates and UNION ALL keeps them.
union ignores column names and matches by position, so a different column order silently mixes values. The two DataFrames must have the same number of columns, or the call raises an error. unionByName(b, allowMissingColumns=True) fills columns missing on one side with null.
Broadcast joins
A broadcast join sends a copy of the small side to every executor. Each executor joins its own partitions of the large side against that copy, so the large side is not shuffled. This removes a costly shuffle and sort.
from pyspark.sql.functions import broadcast
big.join(broadcast(small), "id")broadcast() is a hint. Spark also picks a broadcast join by itself when a side's estimated size is below spark.sql.autoBroadcastJoinThreshold, which defaults to 10 MB, and setting it to -1 turns automatic broadcasting off. With Adaptive Query Execution, Spark can switch a sort merge join to a broadcast join at run time once it sees the real size.
The broadcast table must fit in driver and executor memory. Broadcasting a table that is too large causes out of memory errors and slow joins. In explain() output a broadcast join shows as BroadcastHashJoin.
How the exam asks about this
- "Return customers that have never placed an order, with customer columns only." Use a
left_antijoin. - "One table is 5 MB and the other is 2 TB; avoid shuffling the large one." Use
broadcast()on the small table. - "Two DataFrames have the same columns in different order." Use
unionByName, becauseunionmatches by position.
What to remember for the exam
- The default join type is
inner. left_semiandleft_antireturn only left columns.- A list of column names as
onkeeps one copy of each key column. - DataFrame
unionandunionAllkeep duplicates; adddistinct()to remove them. unionmatches by position, andunionByNamematches by name.- Broadcast joins avoid shuffling the large table, and the broadcast side must be small.
A join condition written as a.id == b.id leaves two columns named id. Selecting id afterward raises an ambiguity error.
Common mistakes
Assuming union removes duplicates. In PySpark, union is UNION ALL. If unique rows are required, call distinct() afterward.
Mixing column order in a union. Because union goes by position, values land in the wrong columns without any error. Use unionByName when order may differ.
Broadcasting the wrong table. The broadcast hint belongs on the small side. Hinting the large side gains nothing and risks running out of memory.
Check yourself
Which join returns left rows that have no match on the right, with only left columns?
left_anti, which is the opposite ofleft_semi.What does a broadcast join avoid, and what must be true of the broadcast table?
It avoids shuffling the large table, and the broadcast table must be small enough to fit in memory on the driver and every executor.
What is the difference between
unionandunionByName?unionmatches columns by position, whileunionByNamematches them by name and can fill missing columns with null.
Topic 4 of 6 in Developing Apache Spark DataFrame/DataSet API Applications
Test yourself on Developing Apache Spark DataFrame/DataSet API Applications
The full Databricks Associate Developer for Apache Spark question bank has 600 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Databricks. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.