PySpark join(): combine two DataFrames
PySpark `join()` combines two DataFrames on one or more key columns. You control the join type with the `how` argument — `inner` (default), `left`, `right`, `outer`, `left_semi`, `left_anti`. For a small lookup table, wrap it in `broadcast()` to avoid a shuffle and speed the join up.
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
When one DataFrame is small, `from pyspark.sql.functions import broadcast` and `large.join(broadcast(small), "id")` sends the small side to every executor and skips the expensive shuffle.
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
If one side of the join is small enough to fit in memory, broadcasting it removes the shuffle entirely: Spark ships the small table to every executor. Adaptive Query Execution does this automatically when it can, and F.broadcast(small_df) forces it. The other thing to watch is row count. A join is not a lookup — if the right side has several rows per key, the result multiplies, and a join that unexpectedly doubles your data is almost always duplicate keys on the right. Deduplicate the lookup side first. Finally, nulls never match nulls in a join key, so rows with a null key silently disappear from an inner join.
Example (PySpark)
from pyspark.sql import SparkSession
from pyspark.sql.functions import broadcast
spark = SparkSession.builder.getOrCreate()
orders = spark.createDataFrame([(1, 101), (2, 102), (3, 101)], ["order_id", "customer_id"])
customers = spark.createDataFrame([(101, "Alice"), (102, "Bob")], ["customer_id", "name"])
# inner join, broadcasting the small customers table
result = orders.join(broadcast(customers), "customer_id", "inner")
result.show()Joins each order to its customer name; broadcasting the small customers table avoids a shuffle.
Run this example in the free online PySpark compiler
Frequently 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?
Wrap the small DataFrame in broadcast(): large_df.join(broadcast(small_df), "key"). Spark sends the small side to every executor, avoiding a shuffle.
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