PySpark join(): combine two DataFrames
PySpark join() combines DataFrames on one or more keys. Choose inner, left, right, outer, left_semi or left_anti according to which unmatched rows you need. A broadcast hint can help when one input is small enough for every executor.
Join types
inner keeps matching rows from both sides; left keeps all left rows; outer keeps everything; left_anti keeps left rows with no match. Choose based on whether you must retain unmatched rows.
Broadcast join for performance
Use broadcast(small_df) to suggest broadcasting a small input. Inspect the physical plan and executor memory before using the hint on real data; Spark does not support every strategy for every join type.
Join types and avoiding duplicate key columns
Spark supports inner, left, right, full outer, left semi, left anti and cross joins. Semi and anti joins are underused: left semi keeps rows from the left side that have a match without adding any right-hand columns, and left anti keeps the rows that do not match, which is the cleanest way to express 'find records missing from the other table'. When the join key has the same name on both sides, pass it as a string or list — df1.join(df2, 'customer_id') — and Spark returns one key column. Using a condition such as df1.id == df2.id instead returns both, and you then have to drop one to avoid ambiguity.
Skew, broadcast joins and row count surprises
Repeated keys can legitimately produce several matches. Check the expected relationship before deduplicating: dropping rows can discard valid information. Ordinary equality does not match null keys; use eqNullSafe() explicitly when matching nulls is required. Broadcast and adaptive execution may change the join plan, so inspect explain() instead of assuming every shuffle disappears.
| Join | Rows retained | Right-side columns |
|---|---|---|
| inner | Orders with a matching customer | Yes |
| left | All orders, including unknown customers | Yes; null when unmatched |
| left_semi | Orders with a matching customer | No |
| left_anti | Orders without a matching customer | No |
Example (PySpark)
from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
orders = spark.createDataFrame([(1, 101), (2, 102), (3, 999)], ["order_id", "customer_id"])
customers = spark.createDataFrame([(101, "Alice"), (102, "Bob")], ["customer_id", "name"])
result = orders.join(customers, "customer_id", "left").orderBy("order_id")
for row in result.select("order_id", "name").collect():
print(row.order_id, row.name)
A left join keeps the third order even though customer 999 is missing. Try inner, left_semi and left_anti next; semi/anti results contain only the original order columns. This example has three rows, so collecting its result is bounded.
Run this example in the free online PySpark compiler
Expected output
1 Alice
2 Bob
3 NoneFrequently asked questions
What is the default join type in PySpark?
inner. Rows are kept only when the join key exists in both DataFrames.
How do I do a broadcast join in PySpark?
Use large_df.join(broadcast(small_df), "key") for a supported join. Only broadcast an input that fits comfortably in executor memory; verify the resulting plan.
How do I join on multiple columns in PySpark?
Pass a list of column names, e.g. df1.join(df2, ["col1", "col2"], "inner").
How do I keep unmatched rows in a PySpark join?
Use how="left" to keep all left rows, or how="outer" to keep all rows from both DataFrames; unmatched columns become null.
Practice challenges
Open the free PySpark compiler · Data Engineering challenges · Data Engineering jobs