PySpark Join Types: When to Use Left vs Inner

Why inner join drops rows

df.join(other, on, how) defaults how to "inner". An inner join keeps only rows where the key exists in both DataFrames. Any row in the left DataFrame whose key is absent from the right DataFrame is silently discarded.

This creates a class of bug that is especially hard to catch: the job succeeds, the output file exists, but the row count is quietly wrong.

import duckdb.experimental.spark.all as F
from duckdb.experimental.spark.all import SparkSession

spark = SparkSession.builder.getOrCreate()
orders    = spark.read.csv("orders.csv",    header=True)
customers = spark.read.csv("customers.csv", header=True)

# BUG: drops orders whose customer_id is not in customers
enriched = orders.join(customers, "cust_id")         # how="inner" is the default
print(enriched.count())   # fewer rows than orders - silent data loss

Fix: switch to a left join

Pass "left" as the third argument. The left DataFrame (orders) is preserved in full; columns from the right side (customers) are NULL where there is no matching key.

# FIXED: every order row appears; deleted customers get NULL name/email
enriched = orders.join(customers, "cust_id", "left")
print(enriched.count())   # same count as orders - no rows lost

The fix is a single argument, but finding it requires knowing to check row counts before and after every join.

The four PySpark join types you need to know

Always check row counts around joins

The cheapest safety net is an assertion immediately after any join you expect to preserve cardinality:

before = orders.count()
enriched = orders.join(customers, "cust_id", "left")
after = enriched.count()
assert before == after, f"Row count changed: {before} -> {after}"

In production pipelines, dbt's relationships test and Great Expectations' expect_table_row_count_to_equal serve the same purpose - they catch orphaned foreign keys and unexpected row drops before dashboards go wrong.