PySpark DataFrame.join()

pyspark.sql.DataFrameJoinsSince Spark 1.3.0

join() combines two DataFrames by matching rows on one or more key columns and returns a new DataFrame. It is a method on pyspark.sql.DataFrame, so you call it as df.join(other, on, how), where on names the keys or gives a join expression and how picks the join type (inner by default).

Several examples below share two small DataFrames: emp with Alice (dept 10), Bob (20), Chitra (30) and Dev (null dept), and dept with Sales (10), Engineering (20) and HR (40). Every example was run on PySpark 3.5.5, and you can paste any of them into the PySpark online compiler and run it as is.

Quick answer

  • df.join(other, on, how) returns a new DataFrame. how defaults to "inner", so rows without a match on the other side are dropped unless you ask for "left", "right" or "full". "left_semi" returns only the matched left rows and "left_anti" only the unmatched ones.
  • When the key has the same name on both sides, pass it as a string or a list of strings (on=["region", "year"]); the result keeps one copy of each key column.
  • When the key names differ, pass an expression such as emp.dept_id == departments.id. Both key columns stay in the result, so drop or select the one you need.
  • Null keys never match with ==, because comparing null with anything gives null. Use eqNullSafe() when two nulls should join.
  • Alias both sides with alias() before a self join, and remove duplicate keys from a lookup table with dropDuplicates() so matching rows are not repeated.
from pyspark.sql import functions as F

DataFrame.join(other, on=None, how=None)
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

emp = spark.createDataFrame(
    [(1, "Alice", 10), (2, "Bob", 20), (3, "Chitra", 30), (4, "Dev", None)],
    ["emp_id", "name", "dept_id"],
)
dept = spark.createDataFrame(
    [(10, "Sales"), (20, "Engineering"), (40, "HR")],
    ["dept_id", "dept_name"],
)

# Inner join on a column that has the same name in both DataFrames
emp.join(dept, on="dept_id", how="inner").orderBy("emp_id").show()
Output
+-------+------+-----+-----------+
|dept_id|emp_id| name|  dept_name|
+-------+------+-----+-----------+
|     10|     1|Alice|      Sales|
|     20|     2|  Bob|Engineering|
+-------+------+-----+-----------+

Only Alice (dept 10, Sales) and Bob (dept 20, Engineering) have a match on both sides. Chitra's dept 30 does not exist in dept, Dev's dept_id is null, and no employee belongs to HR (40), so the inner join drops all three. Because on is a column name, dept_id appears once, as the first column.

Syntax and parameters

DataFrame.join(other, on=None, how=None)
ParameterTypeDefaultDescription
otherDataFramerequired

The right side of the join.

onstr, list of str, Column or list of ColumnNone

A column name, a list of column names, a join expression (Column), or a list of Columns. Names must exist on both sides.

howstrNone (inner)

The join type. Accepted values: inner, outer, full, fullouter, full_outer, leftouter, left, left_outer, rightouter, right, right_outer, leftsemi, left_semi, semi, leftanti, left_anti, anti, cross.

Returns: DataFrame, a new DataFrame joined using the given join expression.

join() was added in Spark 1.3.0. When how is not given, the join is an inner join. A misspelled value such as "lft" raises IllegalArgumentException with the message "Unsupported join type 'lft'", followed by the list of accepted values above.

The documentation links on this page point to the Spark 3.5.7 docs, because the 3.5.5 API reference pages returned 404 when this page was tested; the signature is the same there.

How you pass on changes the columns you get back. A name or a list of names keeps one copy of each key column, while an expression keeps the key columns from both sides. This page covers what join() returns; for how Spark executes joins, see the join strategies tutorial.

PySpark join types: left, right and full outer

Choose the join type by deciding which side must keep its unmatched rows: the left side, the right side, or both.

# Left: every employee, with dept_name where a match exists
emp.join(dept, on="dept_id", how="left").orderBy("emp_id").show()

# Right: every department, with employees where a match exists
emp.join(dept, on="dept_id", how="right").orderBy("dept_id").show()

# Full outer: every row from both sides
emp.join(dept, on="dept_id", how="full").orderBy("dept_id", "emp_id").show()
Output
+-------+------+------+-----------+
|dept_id|emp_id|  name|  dept_name|
+-------+------+------+-----------+
|     10|     1| Alice|      Sales|
|     20|     2|   Bob|Engineering|
|     30|     3|Chitra|       NULL|
|   NULL|     4|   Dev|       NULL|
+-------+------+------+-----------+

+-------+------+-----+-----------+
|dept_id|emp_id| name|  dept_name|
+-------+------+-----+-----------+
|     10|     1|Alice|      Sales|
|     20|     2|  Bob|Engineering|
|     40|  NULL| NULL|         HR|
+-------+------+-----+-----------+

+-------+------+------+-----------+
|dept_id|emp_id|  name|  dept_name|
+-------+------+------+-----------+
|   NULL|     4|   Dev|       NULL|
|     10|     1| Alice|      Sales|
|     20|     2|   Bob|Engineering|
|     30|     3|Chitra|       NULL|
|     40|  NULL|  NULL|         HR|
+-------+------+------+-----------+

The left join keeps all four employees. Chitra gets a NULL dept_name because there is no dept 30, and Dev gets one because a null dept_id never matches. The right join keeps all three departments, so HR (40) appears with NULL emp_id and name. The full outer join returns five rows: the two matches plus Chitra, Dev and HR, each with NULLs for the side that had no match.

Because on is a column name, each result has a single dept_id column, filled from whichever side has the row: HR's row shows 40 in the right and full joins even though no employee belongs to it. The table also includes the semi and anti joins from the next section.

howUnmatched emp rowsUnmatched dept rowsColumns returnedRows returned with this data
inner (default)DroppedDroppedBoth sides2: Alice, Bob
leftKept, right side NULLDroppedBoth sides4: all employees
rightDroppedKept, left side NULLBoth sides3: all departments
fullKeptKeptBoth sides5: Alice, Bob, Chitra, Dev, HR
left_semiDroppedDroppedemp only2: Alice, Bob
left_antiOnly these are keptDroppedemp only2: Chitra, Dev

PySpark join on different column names

When the key is called dept_id on one side and id on the other, pass a join expression that compares the two columns.

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

emp = spark.createDataFrame(
    [(1, "Alice", 10), (2, "Bob", 20), (3, "Chitra", 30)],
    ["emp_id", "name", "dept_id"],
)
# The key is called "id" here, not "dept_id"
departments = spark.createDataFrame(
    [(10, "Sales"), (20, "Engineering"), (40, "HR")],
    ["id", "dept_name"],
)

joined = emp.join(departments, emp.dept_id == departments.id, "inner")
joined.orderBy("emp_id").show()

# Drop the duplicate key column from the right side
joined.drop(departments.id).orderBy("emp_id").show()
Output
+------+-----+-------+---+-----------+
|emp_id| name|dept_id| id|  dept_name|
+------+-----+-------+---+-----------+
|     1|Alice|     10| 10|      Sales|
|     2|  Bob|     20| 20|Engineering|
+------+-----+-------+---+-----------+

+------+-----+-------+-----------+
|emp_id| name|dept_id|  dept_name|
+------+-----+-------+-----------+
|     1|Alice|     10|      Sales|
|     2|  Bob|     20|Engineering|
+------+-----+-------+-----------+

The first result keeps both keys: dept_id from emp and id from departments, with the same value (10 or 20) on each row. Chitra is missing because this is an inner join and there is no department 30. Calling drop(departments.id) removes the right-side key and leaves one dept_id column.

PySpark join on multiple columns

When a row is identified by more than one column, such as region and year, pass a list of names or combine several comparisons with &.

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

sales = spark.createDataFrame(
    [("North", 2023, 120), ("North", 2024, 150), ("South", 2023, 90), ("South", 2024, 80)],
    ["region", "year", "revenue"],
)
targets = spark.createDataFrame(
    [("North", 2023, 100), ("North", 2024, 160), ("South", 2024, 75)],
    ["region", "year", "target"],
)

# A list of names joins on all of them and keeps one copy of each key
sales.join(targets, on=["region", "year"], how="inner").orderBy("region", "year").show()

# The same keys as an expression (useful when names differ), here as a left join
cond = (sales.region == targets.region) & (sales.year == targets.year)
sales.join(targets, cond, "left") \
    .select(sales.region, sales.year, "revenue", "target") \
    .orderBy("region", "year").show()
Output
+------+----+-------+------+
|region|year|revenue|target|
+------+----+-------+------+
| North|2023|    120|   100|
| North|2024|    150|   160|
| South|2024|     80|    75|
+------+----+-------+------+

+------+----+-------+------+
|region|year|revenue|target|
+------+----+-------+------+
| North|2023|    120|   100|
| North|2024|    150|   160|
| South|2023|     90|  NULL|
| South|2024|     80|    75|
+------+----+-------+------+

A row matches only when both region and year are equal. The inner join with a list returns three rows, each with one region and one year column; South 2023 is missing because targets has no South 2023 row. The left join with an expression keeps South 2023 with a NULL target. An expression keeps both sides' key columns, so the select() picks sales.region and sales.year through the sales DataFrame reference; a plain name such as "region" would be ambiguous.

Left semi and left anti joins

Use a left semi join to keep the left rows that have a match, and a left anti join to keep the left rows that do not. Neither adds columns from the right side.

# Left semi: employees whose dept_id exists in dept (only emp columns are kept)
emp.join(dept, on="dept_id", how="left_semi").orderBy("emp_id").show()

# Left anti: employees whose dept_id has no match in dept
emp.join(dept, on="dept_id", how="left_anti").orderBy("emp_id").show()
Output
+-------+------+-----+
|dept_id|emp_id| name|
+-------+------+-----+
|     10|     1|Alice|
|     20|     2|  Bob|
+-------+------+-----+

+-------+------+------+
|dept_id|emp_id|  name|
+-------+------+------+
|     30|     3|Chitra|
|   NULL|     4|   Dev|
+-------+------+------+

The left semi join returns Alice and Bob, the same employees as the inner join in the quick answer, but without the dept_name column. The left anti join returns the other two: Chitra, whose dept 30 does not exist, and Dev, whose null dept_id cannot match anything. HR does not appear in either result, because both join types return only left-side rows.

PySpark self join

A self join compares rows of a DataFrame with other rows of the same DataFrame, for example to look up each employee's manager. Give each copy its own alias and refer to columns through it.

from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

staff = spark.createDataFrame(
    [(1, "Alice", None), (2, "Bob", 1), (3, "Chitra", 1), (4, "Dev", 2)],
    ["id", "name", "manager_id"],
)

# Alias both sides so Spark can tell the two copies apart
e = staff.alias("e")
m = staff.alias("m")

e.join(m, F.col("e.manager_id") == F.col("m.id"), "left") \
    .select(F.col("e.name").alias("employee"), F.col("m.name").alias("manager")) \
    .orderBy("employee").show()
Output
+--------+-------+
|employee|manager|
+--------+-------+
|   Alice|   NULL|
|     Bob|  Alice|
|  Chitra|  Alice|
|     Dev|    Bob|
+--------+-------+

e plays the employee and m plays the manager. Bob and Chitra have manager_id 1, so their manager is Alice, and Dev's manager_id 2 points to Bob. Alice has no manager (manager_id is null), and she still appears with a NULL manager because this is a left join.

Common errors and fixes

AMBIGUOUS_REFERENCE: Reference dept_id is ambiguous after a join

Python
Run it
# Joining on an expression keeps both dept_id columns
emp.join(dept, emp.dept_id == dept.dept_id).select("dept_id").show()
Error
AnalysisException: [AMBIGUOUS_REFERENCE] Reference `dept_id` is ambiguous, could be: [`dept_id`, `dept_id`].

Why it happens: Joining on the expression emp.dept_id == dept.dept_id keeps the dept_id column from both DataFrames. The name "dept_id" then refers to two columns, and Spark refuses to guess which one you mean.

Fix: When the key has the same name on both sides, join on the column name instead, so the result has a single dept_id column.

Python
# Join on the column name instead: the result has one dept_id column
emp.join(dept, on="dept_id").select("dept_id", "name").orderBy("emp_id").show()
Output
+-------+-----+
|dept_id| name|
+-------+-----+
|     10|Alice|
|     20|  Bob|
+-------+-----+

"Column manager_id, id are ambiguous" in a self join

Python
Run it
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

staff = spark.createDataFrame(
    [(1, "Alice", None), (2, "Bob", 1), (3, "Chitra", 1), (4, "Dev", 2)],
    ["id", "name", "manager_id"],
)

# Joining a DataFrame to itself without aliases
staff.join(staff, staff.manager_id == staff.id).show()
Error
AnalysisException: Column manager_id, id are ambiguous. It's probably because you joined several Datasets together, and some of these Datasets are the same. This column points to one of the Datasets but Spark is unable to figure out which one. Please alias the Datasets with different names via `Dataset.as` before joining them, and specify the column using qualified name, e.g. `df.as("a").join(df.as("b"), $"a.id" > $"b.id")`. You can also set spark.sql.analyzer.failAmbiguousSelfJoin to false to disable this check.

Why it happens: Both sides of the join are the same staff DataFrame, so staff.manager_id and staff.id could belong to either copy. The message itself suggests the fix: give the two sides different aliases and use qualified column names.

Fix: Call alias() on each side and refer to columns through the alias with F.col("e.manager_id") and F.col("m.id"). This fix uses an inner join, so Alice, who has no manager, is not in the output; use "left" as in the self join example above to keep her.

Python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

staff = spark.createDataFrame(
    [(1, "Alice", None), (2, "Bob", 1), (3, "Chitra", 1), (4, "Dev", 2)],
    ["id", "name", "manager_id"],
)

# Alias each side and refer to columns through the alias
e, m = staff.alias("e"), staff.alias("m")
e.join(m, F.col("e.manager_id") == F.col("m.id")) \
    .select(F.col("e.name").alias("employee"), F.col("m.name").alias("manager")) \
    .orderBy("employee").show()
Output
+--------+-------+
|employee|manager|
+--------+-------+
|     Bob|  Alice|
|  Chitra|  Alice|
|     Dev|    Bob|
+--------+-------+

UNRESOLVED_USING_COLUMN_FOR_JOIN: join key has a different name on the right side

Python
Run it
# A string key must exist on both sides with the same name
departments = dept.withColumnRenamed("dept_id", "id")
emp.join(departments, "dept_id").show()
Error
AnalysisException: [UNRESOLVED_USING_COLUMN_FOR_JOIN] USING column `dept_id` cannot be resolved on the right side of the join. The right-side columns: [`dept_name`, `id`].

Why it happens: A string (or list of strings) in on must name a column that exists on both sides. Here the right side calls the key id, and the message lists the right-side columns it found: dept_name and id.

Fix: Use a join expression that compares the two differently named columns.

Python
# Use an expression when the key names differ
departments = dept.withColumnRenamed("dept_id", "id")
emp.join(departments, emp.dept_id == departments.id).orderBy("emp_id").show()
Output
+------+-----+-------+---+-----------+
|emp_id| name|dept_id| id|  dept_name|
+------+-----+-------+---+-----------+
|     1|Alice|     10| 10|      Sales|
|     2|  Bob|     20| 20|Engineering|
+------+-----+-------+---+-----------+

Rows with null join keys silently disappear

Python
Run it
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

orders = spark.createDataFrame(
    [(1, "A"), (2, "B"), (3, None)],
    ["order_id", "region"],
)
managers = spark.createDataFrame(
    [("A", "Asha"), ("B", "Ben"), (None, "Unassigned desk")],
    ["region", "manager"],
)

# Order 3 and the "Unassigned desk" row both have a null region,
# but null == null is not true, so they do not join
orders.join(managers, on="region", how="inner").orderBy("order_id").show()
Output (no error, but wrong)
+------+--------+-------+
|region|order_id|manager|
+------+--------+-------+
|     A|       1|   Asha|
|     B|       2|    Ben|
+------+--------+-------+

Why it happens: Order 3 and the "Unassigned desk" row both have a null region, but in Spark SQL comparing null with anything, even another null, gives null rather than true. The join treats that as no match, so order 3 is dropped without any error.

Fix: Join with eqNullSafe(), which treats two nulls as equal. It is an expression, so both region columns are kept and the select() picks orders.region.

Python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

orders = spark.createDataFrame(
    [(1, "A"), (2, "B"), (3, None)],
    ["order_id", "region"],
)
managers = spark.createDataFrame(
    [("A", "Asha"), ("B", "Ben"), (None, "Unassigned desk")],
    ["region", "manager"],
)

# eqNullSafe treats two nulls as equal
orders.join(managers, orders.region.eqNullSafe(managers.region), "inner") \
    .select("order_id", orders.region, "manager").orderBy("order_id").show()
Output
+--------+------+---------------+
|order_id|region|        manager|
+--------+------+---------------+
|       1|     A|           Asha|
|       2|     B|            Ben|
|       3|  NULL|Unassigned desk|
+--------+------+---------------+

Join returns duplicate rows: repeated keys on one side

Python
Run it
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

emp = spark.createDataFrame(
    [(1, "Alice", 10), (2, "Bob", 20)],
    ["emp_id", "name", "dept_id"],
)
# dept_id 10 appears twice in the lookup table
dept = spark.createDataFrame(
    [(10, "Sales"), (10, "Sales"), (20, "Engineering")],
    ["dept_id", "dept_name"],
)

# Alice matches both dept_id 10 rows, so she appears twice
emp.join(dept, on="dept_id").orderBy("emp_id").show()
Output (no error, but wrong)
+-------+------+-----+-----------+
|dept_id|emp_id| name|  dept_name|
+-------+------+-----+-----------+
|     10|     1|Alice|      Sales|
|     10|     1|Alice|      Sales|
|     20|     2|  Bob|Engineering|
+-------+------+-----+-----------+

Why it happens: A join produces one output row for every pair of matching rows. dept has two rows with dept_id 10, so Alice matches both and appears twice, while Bob matches the single dept 20 row once.

Fix: Remove duplicate keys from the lookup side with dropDuplicates(["dept_id"]) before joining. In this data both dept 10 rows are identical, so nothing is lost.

Python
from pyspark.sql import SparkSession
from pyspark.sql import functions as F

spark = SparkSession.builder.appName("join-example").getOrCreate()

emp = spark.createDataFrame(
    [(1, "Alice", 10), (2, "Bob", 20)],
    ["emp_id", "name", "dept_id"],
)
# dept_id 10 appears twice in the lookup table
dept = spark.createDataFrame(
    [(10, "Sales"), (10, "Sales"), (20, "Engineering")],
    ["dept_id", "dept_name"],
)

# Remove duplicate keys from the lookup side before joining
emp.join(dept.dropDuplicates(["dept_id"]), on="dept_id").orderBy("emp_id").show()
Output
+-------+------+-----+-----------+
|dept_id|emp_id| name|  dept_name|
+-------+------+-----+-----------+
|     10|     1|Alice|      Sales|
|     20|     2|  Bob|Engineering|
+-------+------+-----+-----------+
  • alias()

    gives a DataFrame a name so you can refer to its columns as "e.name", which a self join needs.

  • eqNullSafe()

    an equality test that treats two nulls as equal, for joins where null keys should match.

  • dropDuplicates()

    removes rows with repeated keys from a lookup table before a join, so matching rows are not multiplied.

  • drop()

    removes the extra key column, such as departments.id, that an expression join leaves behind.

  • crossJoin()

    returns the Cartesian product of two DataFrames, pairing every left row with every right row.

  • broadcast()

    marks a DataFrame as small enough for use in broadcast joins.

Practice problems

Read the full walkthrough in Joining Data in PySpark, then practise with these interview questions in the browser.

Frequently asked questions

What is the default join type in PySpark?

The default is an inner join. If you leave out how, join() keeps only rows that match on both sides. The fix for the AMBIGUOUS_REFERENCE error above calls emp.join(dept, on="dept_id") with no how, and it returns only Alice and Bob, the same rows as the explicit inner join in the quick answer.

How do I avoid duplicate columns after a join in PySpark?

If the key has the same name on both sides, pass it as a string or list of strings in on, and the result keeps one copy of each key. If the names differ, you have to join with an expression, which keeps both keys; remove the one you do not need with drop(), as in joined.drop(departments.id), or pick columns with select().

What is the difference between a left anti join and NOT IN in PySpark?

A left anti join returns the left rows that have no match in another DataFrame, while filter(~F.col("dept_id").isin(values)) checks each row against a Python list. They also treat nulls differently. In the left anti example above, Dev, whose dept_id is null, is returned. With NOT IN, comparing a null value gives null, so filter() drops that row; the handling nulls tutorial covers this in more depth.

How do I join more than two DataFrames in PySpark?

Chain the calls. join() returns a DataFrame, so you can call .join() again on the result, for example a.join(b, on="id").join(c, on="id"). Each call has its own on and how, so you can mix join types, and the key rules above (names versus expressions, nulls, duplicates) apply to every step.