Join types (inner/left/right/full/semi/anti/cross)
Apache Spark · Aggregations & Joins
Theory
df.join(other, on, how) combines two DataFrames on matching keys. inner keeps only rows with a match on both sides. left (left_outer) keeps every left row and fills right-side columns with NULL when there is no match; right does the opposite; full (outer) keeps all rows from both sides. left_semi keeps left rows that have at least one match but returns only left columns, like WHERE EXISTS, and left_anti keeps left rows with no match, like NOT EXISTS. crossJoin pairs every left row with every right row.
Pass on='customer_id' (or a list of names) when the key has the same name on both sides, so it appears once in the result; use an expression such as orders.customer_id == customers.customer_id for different names or extra conditions. For example, joining orders to customers: inner drops order 109 from unknown customer 7, left keeps it with name NULL, and left_anti on customers finds Vikram, who never ordered.
Why we use it
Real data is spread across tables: orders, customers, products. Joins bring them together, and choosing the wrong type silently loses rows (inner instead of left) or duplicates them (many-to-many keys).
When to use it
Use inner for matched data, left to keep everything from the main table, full for reconciliations, semi/anti for 'has / has no match' filters without adding columns, and cross only for small tables such as a date x product grid. Check key uniqueness first to avoid row explosions.
How it works
- Two DataFrames and a join key
- Need rows without a match? Yes: Choose left, right or full. No: Use inner.
- Only need to filter by existence? Yes: Use left_semi or left_anti. No: Use a normal join to add columns.
- Check that keys are unique enough to avoid row explosion
- Joined DataFrame
Syntax
a.join(b, "key", "inner") # how: inner | left | right | full | left_semi | left_anti
a.join(b, ["k1", "k2"], "left")
a.join(b, a.id == b.cust_id, "left")
a.crossJoin(b)Example
from pyspark.sql import SparkSession, functions as F
spark = (SparkSession.builder.master("local[1]")
.config("spark.ui.enabled", "false")
.getOrCreate())
orders = spark.createDataFrame([
(101, 1, "North", "Laptop", 1, 55000.0, "2026-01-05"),
(102, 2, "West", "Phone", 2, 30000.0, "2026-01-06"),
(103, 1, "North", "Mouse", 3, 1500.0, "2026-01-06"),
(104, 3, "South", "Laptop", 1, 52000.0, "2026-01-07"),
(105, 4, "West", "Tablet", 1, 25000.0, "2026-01-08"),
(106, 2, "East", "Phone", 1, 15000.0, "2026-01-08"),
(107, 5, "North", "Tablet", 2, 48000.0, "2026-01-09"),
(108, 3, "South", "Mouse", 4, 2000.0, "2026-01-10"),
], "order_id INT, customer_id INT, region STRING, product STRING, "
"qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))
customers = spark.createDataFrame([
(1, "Asha", "Jaipur", "Retail"),
(2, "Ravi", "Pune", "Corporate"),
(3, "Meera", "Delhi", "Retail"),
(4, "Karan", "Jaipur", "SMB"),
(5, "Priya", "Mumbai", "Corporate"),
(6, "Vikram", "Bengaluru", "SMB"),
], "customer_id INT, name STRING, city STRING, segment STRING")
# one extra order from customer 7, who is not in customers
orders = orders.unionByName(spark.createDataFrame(
[(109, 7, "East", "Mouse", 1, 800.0, None)], orders.schema))
for how in ["inner", "left", "right", "full", "left_semi", "left_anti"]:
print(f"{how:10} -> {orders.join(customers, 'customer_id', how).count()} rows")
# left join keeps order 109 with NULL customer columns
(orders.join(customers, "customer_id", "left")
.select("order_id", "customer_id", "name", "city")
.orderBy("order_id").show())
# customers who never ordered (anti) and who did (semi)
customers.join(orders, "customer_id", "left_anti").show()
print(sorted(r.name for r in customers.join(orders, "customer_id", "left_semi").collect()))
print("cross join rows:", customers.crossJoin(orders.select("region").distinct()).count())
# Output:
# inner -> 8 rows
# left -> 9 rows
# right -> 9 rows
# full -> 10 rows
# left_semi -> 8 rows
# left_anti -> 1 rows
# +--------+-----------+-----+------+
# |order_id|customer_id| name| city|
# +--------+-----------+-----+------+
# | 101| 1| Asha|Jaipur|
# | 102| 2| Ravi| Pune|
# | 103| 1| Asha|Jaipur|
# | 104| 3|Meera| Delhi|
# | 105| 4|Karan|Jaipur|
# | 106| 2| Ravi| Pune|
# | 107| 5|Priya|Mumbai|
# | 108| 3|Meera| Delhi|
# | 109| 7| NULL| NULL|
# +--------+-----------+-----+------+
#
# +-----------+------+---------+-------+
# |customer_id| name| city|segment|
# +-----------+------+---------+-------+
# | 6|Vikram|Bengaluru| SMB|
# +-----------+------+---------+-------+
#
# ['Asha', 'Karan', 'Meera', 'Priya', 'Ravi']
# cross join rows: 24
Key points
- inner drops unmatched rows; left/right/full keep them with NULLs
- left_semi = EXISTS filter, left_anti = NOT EXISTS filter; both return only left columns
- on='key' keeps one key column; an expression keeps both, so select or drop one
- Duplicate keys on both sides multiply rows (many-to-many)
- NULL keys never match in a normal join; use eqNullSafe if NULLs should match