Home / Tutorials / Left joins

PYSPARK JOINS

Keep unmatched sales with a left join

Example tested with PySpark 4.2.0

A missing customer lookup should not make a real transaction disappear. A left join keeps every sale and fills unmatched customer columns with null.

Create the two DataFrames

sales = spark.createDataFrame(
    [(101, "Alice"), (102, "David")],
    ["transaction_id", "customer_name"],
)

customers = spark.createDataFrame(
    [("Alice", "London", "gold")],
    ["customer_name", "city", "loyalty_level"],
)

Join on the shared column

enriched = sales.join(
    customers,
    on="customer_name",
    how="left",
)

enriched.orderBy("transaction_id").show()

+-------------+--------------+------+-------------+
|customer_name|transaction_id|  city|loyalty_level|
+-------------+--------------+------+-------------+
|        Alice|           101|London|         gold|
|        David|           102|  NULL|         NULL|
+-------------+--------------+------+-------------+

David's transaction remains. An inner join would remove it because no customer row matches.

Measure unmatched records

from pyspark.sql import functions as F

unmatched = enriched.filter(F.col("city").isNull())
print("unmatched customers:", unmatched.count())

This count is worth monitoring. A sudden rise usually means the lookup data is late, incomplete or using a different key format.