9.1 DataFrame Joins and Combining Datasets

Joining and combining distributed datasets are fundamental operations in data engineering pipelines. In Apache Spark, joins require aligning records from two distinct DataFrames based on one or more key columns. Because the records for each dataset are distributed across cluster nodes, executing a join typically necessitates network data movement (shuffling) or data duplication (broadcasting). This section covers join syntax, join types, column ambiguity resolution, and set concatenation operations.

---

1. Join Syntax and Supported Join Types

The DataFrame join API in PySpark is invoked using the join() method:

df_joined = df1.join(other=df2, on="customer_id", how="inner")

The how Parameter: Join Types

Spark SQL supports seven primary join types specified via the how argument. If how is omitted, Spark defaults to an "inner" join.

Semi and Anti Joins: Filtering Joins

Two of the most frequently tested join types on Databricks exams are "left_semi" and "left_anti":

# Left Semi Join: Keep customers who have placed at least one order
active_customers = customers_df.join(orders_df, on="customer_id", how="left_semi")

# Left Anti Join: Keep customers who have never placed an order
inactive_customers = customers_df.join(orders_df, on="customer_id", how="left_anti")

Key characteristics of semi and anti joins:

1. No Right Columns: The output schema contains only columns from the left DataFrame. No columns from the right DataFrame are appended.
2. No Row Multiplication: Even if a customer_id appears 50 times in orders_df, a left_semi join outputs that customer from customers_df exactly once (assuming customer_id was unique in customers_df). This eliminates the need to run an expensive distinct() or dropDuplicates() operation after an inner join.
3. Catalyst Optimization: Spark optimizes semi/anti joins using broadcast hash joins or sort-merge joins with early termination, stopping the search as soon as the first match or non-match is confirmed.

---

2. Duplicate Join Column Names and Ambiguity

A frequent source of bugs in Spark pipelines occurs when joining two DataFrames that share identical column names. The mechanism used to define the on parameter determines whether duplicate columns are preserved or deduplicated.

Scenario A: Joining on a Column Name String

When you pass a column name string (or a list of strings) to the on parameter, Spark recognizes that the join key exists in both DataFrames. It merges the keys and outputs exactly one instance of the column:

# Clean join: Exactly one 'id' column exists in df_clean
df_clean = df1.join(df2, on="id", how="inner")
df_clean.select("id", "name", "department").show()

Scenario B: Joining on a Boolean Column Expression

When you define the join condition using an equality expression (such as df1["id"] == df2["id"] or col("df1.id") == col("df2.id")), Spark retains both columns in the output DataFrame:

# Ambiguous join: Both df1.id and df2.id exist in the result
df_ambiguous = df1.join(df2, df1["id"] == df2["id"], how="inner")

# This call throws AnalysisException!
df_ambiguous.select("id").show()
# AnalysisException: Reference 'id' is ambiguous, could be: df1.id, df2.id.

Three Strategies to Resolve Column Ambiguity

1. Drop the duplicate right-hand column directly:

   df_resolved = df1.join(df2, df1["id"] == df2["id"]).drop(df2["id"])
   

2. Rename the join column in one DataFrame prior to the join:

   df2_renamed = df2.withColumnRenamed("id", "dept_id")
   df_resolved = df1.join(df2_renamed, df1["id"] == df2_renamed["dept_id"])
   

3. Use DataFrame aliases and reference qualified columns:

   df_resolved = df1.alias("a").join(
       df2.alias("b"),
       F.col("a.id") == F.col("b.id")
   ).select("a.id", "a.name", "b.department")
   

---

3. Cartesian Products and Cross Joins

A cross join computes the Cartesian product of two DataFrames, pairing every row of the left dataset with every row of the right dataset. If DataFrame $A$ contains 10,000 rows and DataFrame $B$ contains 50,000 rows, their cross join produces $10{,}000 \times 50{,}000 = 500{,}000{,}000$ rows.

# Explicit cross join syntax
df_cross = df1.crossJoin(df2)

# Or using join with how='cross'
df_cross = df1.join(df2, how="cross")

---

4. Combining Datasets: union(), unionAll(), and unionByName()

When combining datasets vertically by appending rows, Spark provides two primary approaches: positional concatenation and name-based concatenation.

union() vs. unionAll()

In PySpark, union() and unionAll() are identical functions. In early versions of Apache Spark, unionAll() was the original method name borrowed from SQL. Starting in Spark 2.0, union() was introduced as a cleaner alias, and unionAll() was retained for backwards compatibility.

# union() and unionAll() behave identically in PySpark
combined_df = df1.union(df2)

The Positional Schema Trap

df1.union(df2) binds columns strictly by ordinal position, completely ignoring column names:

# df1 schema: (name: string, age: integer)
# df2 schema: (age: integer, name: string)

# This produces corrupted or failing data!
corrupted_df = df1.union(df2)

Because Spark matches by column position, it attempts to insert df2.age into the name column and df2.name into the age column. If data types are incompatible, Spark throws an AnalysisException; if types are compatible (e.g., two string columns swapped), Spark silently swaps the data, resulting in silent corruption.

Resilient Combination: unionByName()

To safely combine DataFrames regardless of column order, use unionByName():

# Automatically matches columns by name
safe_df = df1.unionByName(df2)

Schema Evolution: allowMissingColumns=True

In modern Spark pipelines, schemas frequently evolve over time. If df1 contains columns [id, name, email] and df2 contains columns [id, name, phone], a standard unionByName() call fails with an AnalysisException stating that schemas do not match.

Setting allowMissingColumns=True enables graceful schema evolution:

# Handles disjoint schemas automatically
evolved_df = df1.unionByName(df2, allowMissingColumns=True)

The resulting DataFrame schema contains the union of all columns: [id, name, email, phone]. Rows originating from df1 have null in the phone column, and rows from df2 have null in the email column.

Joining on multiple keys

Use a list of shared key names for a multi-column equality join: orders.join(customers, ["customer_id", "region"], "left"). Both equality conditions must hold, and the named key columns appear once. For different names or range conditions, use parenthesized Column expressions combined with & and aliases. Normal equality does not match null keys; eqNullSafe() does when missing-to-missing matching is intended. Null-safe matching can create large matches when both sides contain many missing keys, so validate key cardinality before using it.