Everyone knows 4 min read · Joins 15 practice problems ↓

join

Combine DataFrames on a key: inner, left, right and full joins, and the duplicate-key fan-out trap.

On this page

Show code in

Every code block on the page follows this.

You will learn

What each join type keeps: inner, left, right, full
How duplicate keys multiply rows
How to avoid ambiguous column names
How Spark executes a join: broadcast or sort-merge

Read first

select · 4 min read
filter / where · 4 min read

Comfortable with these? Read on.

TL;DR a.join(b, on, how) pairs rows from two DataFrames where the keys match. The join type decides what happens to rows without a match. Every match is a new row, so duplicate keys multiply rows.

What it does

For each row on the left, Spark finds the rows on the right whose keys match, and outputs one combined row per match. Rows that match nothing are dropped or kept with nulls, depending on how.

The four everyday join types

how	Keeps	Unmatched rows
inner (default)	Only rows with a match on both sides	Dropped from both sides
left	Every left row	Right columns are null
right	Every right row	Left columns are null
full (outer)	Every row from both sides	The missing side is null

Spark also has left_semi and left_anti (filter by existence) and cross. They get their own lesson.

Step by step

A left join of employees to departments on dept:

employees (left) + departments

name	dept
Asha	Engineering
Ben	Sales
Dev	HR
Farid	null

Output

name	dept	floor
Asha	Engineering	3
Ben	Sales	1
Dev	HR	null
Farid	null	null

HR has no row in departments, so Dev gets a null floor. Farid's null key matches nothing, because null never equals null. Finance has no employee and disappears: it is on the right.

Run the example

PySparkSpark SQL

result = (employees
    .join(departments, on="dept", how="left")
    .select("name", "dept", "floor")
    .orderBy("name"))

SELECT e.name, e.dept, d.floor
FROM employees e
LEFT JOIN departments d
  ON e.dept = d.dept
ORDER BY e.name

Switch to PySpark to edit and run this example in your browser.

Try how="inner", "right" and "full" and watch which rows appear.

The fan-out trap

A join outputs one row per matching pair. If a key appears 3 times on the left and 4 times on the right, that key produces 12 rows. This is the most common source of "my totals doubled after a join".

PySparkSpark SQL · Check key uniqueness before joining

departments.groupBy("dept").count().filter("count > 1").show()

SELECT dept, COUNT(*) FROM departments
GROUP BY dept HAVING COUNT(*) > 1

Ambiguous column names

on="dept" or on=["dept", "year"]: Spark joins on equality and keeps one copy of each key column. Use this whenever the names match.
on=a.dept == b.dept: both copies are kept, and select("dept") fails as ambiguous. Refer to a["dept"], or alias the DataFrames: a.alias("e") then F.col("e.dept").

Under the hood: how Spark joins

Broadcast hash join

The small side is sent to every executor and built into a hash table. The large side is not shuffled at all. Used automatically when one side is under spark.sql.autoBroadcastJoinThreshold (10 MB by default).

Sort-merge join

Both sides are shuffled by key and sorted, then merged. The default for two large tables. Two shuffles and two sorts: the expensive case.

Shuffle hash join

Both sides shuffled, the smaller partition side hashed instead of sorted. Chosen in some cases, for example by AQE when partitions are small enough.

You can force a broadcast with F.broadcast(small_df) or the SQL hint /*+ BROADCAST(d) */. The Broadcast joins lesson covers when that backfires.

Common mistakes

Joining on keys with duplicates you did not expect

Row counts explode and sums double. Check uniqueness of the key on at least one side.

Expecting null keys to match

In an equality join, null never equals null. Use eqNullSafe if nulls should match.

Filtering the right table in WHERE after a left join

WHERE d.floor > 1 removes the null rows the left join just kept, turning it into an inner join. Put the condition in the ON clause or filter the right side before joining.

Key takeaways

The join type only decides what happens to unmatched rows.
Each matching pair is a row, so duplicate keys multiply rows.
Join with on="col" to keep a single key column.
Small tables are broadcast; two large ones use a sort-merge join with two shuffles.

Check yourself

3 questions

1. Key K appears 2 times on the left and 3 times on the right. How many rows does an inner join output for K?

Show the answer

6. Every left row pairs with every matching right row: 2 × 3 = 6.

2. A left join keeps a left row whose key is null. What are its right-side columns?

Show the answer

null. Null never equals anything in an equality join, so the row has no match and the right columns are null.

3. Why does a.join(b, a.id == b.id).select("id") fail?

Show the answer

Both id columns are kept, so the reference is ambiguous. An expression join keeps both columns. Join with on="id" to keep one, or select a["id"].

Practice it

Interview problems that use join: write the PySpark, run it, and get graded on hidden tests.

Solve: Order Lines with Product Details →

See all 15 problems →

Go deeper

PySpark functions
left_semi / left_anti Spark internals
Broadcast joins PySpark functions
eqNullSafe (<=>)

Primary sources: DataFrame.join · JOIN (Spark SQL)

← Previous
groupBy + agg Next →
orderBy