Joins

Match rows on a key

df.join(other, "customer", "left") combines rows that share a key. Join types: inner (only matches), left/right/full outer (keep unmatched rows from one or both sides, filling the other side with null), left semi (rows of the left that have a match, without adding columns) and left anti (rows of the left with no match). Joins are the most expensive common operation because data may need to be shuffled so matching keys meet. Two traps: joining on a non-unique key multiplies rows (many-to-many), and joining on a column with nulls never matches them. Check row counts before and after.

A left join, run

I ran this on Apache Spark 4.0.0 (PySpark, local mode, in the official Docker image). Order 6 (Kiran) has no matching tier, so the left join keeps the row and shows NULL for tier.

cust = spark.createDataFrame([("asha", "gold"), ("ravi", "silver"), ("meera", "bronze")], ["customer", "tier"])
orders.join(cust, "customer", "left").select("order_id", "customer", "tier").orderBy("order_id").show()

Output:

+--------+--------+------+
|order_id|customer|  tier|
+--------+--------+------+
|       1|    asha|  gold|
|       2|    ravi|silver|
|       3|    asha|  gold|
|       4|   meera|bronze|
|       5|    ravi|silver|
|       6|   kiran|  NULL|
+--------+--------+------+

Join on explicit keys

Count rows before and after a join, and use df1.join(df2, ["a", "b"], "inner") with explicit column lists. Unexpected growth usually means the "unique" key was not unique.

Quick check: Which join keeps every row of the left DataFrame, with nulls where there is no match?

• left outer join
• inner join
• left anti join
• cross join without keys