PySpark Joins: Broadcast, Anti, Multi-Column

PySpark Join on Multiple Columns

# Join on multiple columns
result = orders.join(
    customers,
    on=["customer_id", "region"],
    how="inner"
)

# Alternative syntax with expressions
result = orders.join(
    customers,
    (orders.customer_id == customers.customer_id) &
    (orders.region == customers.region),
    how="inner"
)

Pass a list of column names when both DataFrames share the same column names. Use the expression syntax when column names differ between tables. The list syntax automatically deduplicates the join columns in the output; the expression syntax keeps both copies (you need to drop one manually).

PySpark Broadcast Join (Small Table Optimization)

from pyspark.sql import functions as F

# Force broadcast of the smaller table
result = events.join(
    F.broadcast(users),
    on="user_id",
    how="inner"
)

# Spark sends the entire users table to every executor
# No shuffle of the large events table
# Default threshold: 10MB (spark.sql.autoBroadcastJoinThreshold)

Broadcasting eliminates the shuffle of the large table entirely. The small table is sent to every executor and held in memory. BroadcastHashJoin is O(n) on the large side. The tradeoff: if you broadcast a table that is too large, executors OOM. Reaching for broadcast when the table is 50GB is a common interview mistake. Verify the in-memory size, not just the on-disk size, because deserialization expands compressed data.

PySpark Left Anti Join (Find Missing Rows)

# Find orders with no matching customer
orphaned_orders = orders.join(
    customers,
    on="customer_id",
    how="left_anti"
)

# Equivalent SQL
spark.sql("""
    SELECT o.*
    FROM orders o
    LEFT ANTI JOIN customers c
    ON o.customer_id = c.customer_id
""")

Left anti join returns rows from the left table that have no match in the right table. It is the PySpark equivalent of WHERE NOT EXISTS or WHERE customer_id NOT IN (subquery). More efficient than a left join followed by a filter for IS NULL because Spark can short-circuit the comparison.

PySpark Left Semi Join (Exists Check)

# Find customers who have at least one order
active_customers = customers.join(
    orders,
    on="customer_id",
    how="left_semi"
)

# Returns only columns from the left table
# Does NOT duplicate rows if multiple orders exist

Left semi join returns rows from the left table where a match exists in the right table, but only returns left-table columns. It does not multiply rows. Use it as a performant WHERE EXISTS. In production, this is common for filtering dimension tables against fact tables before a heavier join.

Handle Duplicate Columns After PySpark Join

# Problem: both tables have 'name' column
# After join, you get ambiguous 'name' references

# Solution 1: Rename before joining
customers_renamed = customers.withColumnRenamed("name", "customer_name")
result = orders.join(customers_renamed, on="customer_id")

# Solution 2: Use aliases and select specific columns
result = orders.alias("o").join(
    customers.alias("c"),
    F.col("o.customer_id") == F.col("c.customer_id")
).select("o.*", F.col("c.name").alias("customer_name"))

# Solution 3: Drop after join
result = orders.join(customers, on="customer_id").drop(customers.name)

Duplicate column names after joins cause ambiguous reference errors downstream. The cleanest approach is to rename before the join. If you use the list syntax for the join condition (on=['customer_id']), the join key column is automatically deduplicated. Interviewers watch for whether you handle this proactively or discover it at runtime.

PySpark Cross Join (Cartesian Product)

# Every combination of date and product
date_product_grid = dates.crossJoin(products)

# Useful for generating scaffolds, then left-joining actuals
# WARNING: row count = left_rows * right_rows
# 1000 dates * 5000 products = 5 million rows

Cross joins produce the Cartesian product. Spark requires explicit .crossJoin() or setting spark.sql.crossJoin.enabled=true to prevent accidental Cartesian products from malformed join conditions. Use sparingly. In production, cross joins appear when building date/dimension scaffolds for gap-filling queries.

How Spark Chooses a Join Strategy

Spark evaluates table statistics, join type (inner, left, anti), and any broadcast hints you provide. AQE can override the initial choice at runtime if the actual data sizes differ from catalog estimates. Data skew triggers AQE skew join optimization when a partition exceeds 5x the median size AND 256MB.

Common PySpark Join Mistakes in Interviews

PySpark Joins FAQ

Related Guides