Mastering PySpark Joins — semi, anti, null-safe, and the Pitfalls

Joins are half of data processing. Yet many people only ever use inner/left, unaware of the efficiency of semi/anti or the need for null-safe joins — and they end up with subtle bugs and performance problems. Stories like "I joined just to check existence and my row count doubled" or "NULL keys silently skewed my results" are all too common.

This post maps out every PySpark join type by use case and shows how to avoid the pitfalls you hit most often in practice — duplicate explosion, column ambiguity, and NULL keys.

1. The Full Map of Join Types

a.join(b, "key", how="inner")   # default

2. Semi Join — Existence Checks, Efficiently

When you want to filter by existence — like "only customers who have orders" — an inner join attaches the right-side columns and creates duplicates (a customer with 5 orders becomes 5 rows). left_semi returns only the left rows, with no duplicates, and only those that match.

# ❌ inner join: customers are duplicated once per order + unnecessary columns
customers.join(orders, "cust_id")   # customer rows multiply by order count
 
# ✅ left_semi: only customers with orders, no duplicates, customer columns only
customers.join(orders, "cust_id", "left_semi")

left_semi = "left rows that have a match on the right" (no right-side columns, no duplicates)

It is equivalent to SQL's WHERE EXISTS (...), and clearer and more efficient than an IN subquery.

3. Anti Join — Absence Checks (Set Difference)

"Customers with no orders", "new keys not in the master table" — for absence filters, left_anti is the right answer.

# customers with no order history
inactive = customers.join(orders, "cust_id", "left_anti")
 
# finding new keys (source rows not yet in the target) — useful for backfill and incremental loads
new_records = source.join(target, "id", "left_anti")

left_anti = "left rows with no match on the right" = set difference (left - right)

Anti joins are frequently used in backfill and incremental loading to "keep only what doesn't exist yet" (see the new-key detection in the separate post "Large-Scale Deduplication and SCD Type 2 in PySpark").

4. Pitfall #1: NULL Keys — Null-Safe Joins

In a regular join, NULL = NULL does not match (per the SQL standard). When NULL keys should match each other (or when you treat NULL as a key), bugs creep in.

# regular join: NULL keys do not match (may not be what you intended)
a.join(b, a.k == b.k)
 
# null-safe join: treats NULL = NULL as a match (the <=> operator)
a.join(b, a["k"].eqNullSafe(b["k"]))

eqNullSafe (<=>) matches NULLs against each other. That said, lots of NULL keys cause skew on their own (see the separate post "Conquering Data Skew in PySpark"), so it is usually safer to separate and handle NULL keys before the join.

5. Pitfall #2: Duplicate Explosion (Fan-out)

If the join key isn't unique on one side, rows multiply. If both sides have duplicate keys, the result explodes.

a: 3 rows with key=1,  b: 4 rows with key=1  →  join result has 12 rows with key=1 💥

# guarantee one row per key on one side before joining (unless fan-out is intended)
b_unique = b.dropDuplicates(["key"])   # or pick a representative row with a window
a.join(b_unique, "key")

A common incident: joining against a dimension table without realizing it has duplicates → facts get inflated and aggregates go wrong. Make it a habit to verify key uniqueness on both sides before joining.

6. Pitfall #3: Ambiguous Columns

If both sides have a column with the same name, you get ambiguity errors after the join.

# ❌ ambiguous: if both sides have amount, which one do you mean?
a.join(b, "key").select("amount")   # may raise AnalysisException
 
# ✅ option 1: joining on a string/list key merges the key column into one
a.join(b, ["key"])
 
# ✅ option 2: disambiguate with aliases
a.alias("a").join(b.alias("b"), "key").select("a.amount", "b.amount")
 
# ✅ option 3: rename before the join
b2 = b.withColumnRenamed("amount", "b_amount")

join(b, "key") (string/list keys) merges the key column into one and reduces ambiguity. a.k == b.k (an expression) keeps the key columns from both sides.

7. Multi-Key and Non-Equi Joins

# multiple keys
a.join(b, ["key1", "key2"])
 
# non-equi join (range) — beware shuffle and explosion
a.join(b, (a.ts >= b.valid_from) & (a.ts < b.valid_to))

Non-equi (range) joins are far more expensive than equi joins (close to a cross join unless broadcast is possible). For range joins like validity-period lookups, consider switching to the as-of pattern (see the separate post "PySpark As-of Join").

8. Cross Join — Only When Explicit

An accidental Cartesian product is a disaster. By default, Spark blocks unintended cross joins.

# make intentional cross joins explicit
a.crossJoin(b)
 
# safety setting (keeping the default false is recommended — blocks accidental crosses)
spark.conf.set("spark.sql.crossJoin.enabled", "false")

9. Join Performance — Distribution Strategy

Separate from the join type, how the data is distributed (broadcast vs sort-merge) determines performance.

Semi/anti joins also execute internally as broadcast/sort-merge, so the same optimizations apply.

10. Summary

The key to mastering joins is "picking the join type that matches your intent". Use semi for existence checks and anti for absence checks to avoid duplicate explosion and unnecessary columns, use null-safe joins to prevent NULL-key bugs, and verify key uniqueness before joining to prevent fan-out. Break the habit of reaching only for inner/left — once semi/anti are in your muscle memory, your join code becomes more correct and more efficient.

This article is based on Spark 3.5. If you need help designing complex join logic or performance optimizations, feel free to reach out.

— The Data Dynamics Engineering Team