Definition
- union() – stacks two DataFrames by matching columns strictly by position; the first column of one is combined with the first of the other regardless of names.
- unionByName() – matches columns by name, so it lines up correctly even if the order differs.
- allowMissingColumns – Spark 3.1+ feature:
unionByName(allowMissingColumns=True)fills missing columns with null instead of failing. - Preference – always use
unionByNamebecause names are stable; positions break when schemas evolve.
Imagine the National Bank of India merging two stacks of account forms. union() is a careless clerk who assumes both stacks have fields in the exact same order, copying the first box of form A into the first box of form B. If one stack lists balance before name while the other lists name before balance, the clerk writes customer names into the balance field and rupee amounts into the name field. unionByName() reads the field labels and puts balance under balance and name under name, regardless of order.
Understanding the Concepts
What is the difference between union and unionByName?
Both operations combine two DataFrames into one with all the rows of both, like a SQL UNION ALL (they do not remove duplicates). The difference is how columns are aligned.
from pyspark.sql import SparkSession
import pyspark.sql.functions as F
spark = SparkSession.builder.appName("NBI-Union").getOrCreate()
# Two DataFrames with same columns in DIFFERENT order
df1 = spark.createDataFrame([
("SAV2001", "Rajesh Kumar", 50000.00)
], ["account_id", "holder_name", "balance"])
df2 = spark.createDataFrame([
("Priya Sharma", "SAV2002", 12000.00)
], ["holder_name", "account_id", "balance"]) # different column order!
print("df1 columns:", df1.columns)
print("df2 columns:", df2.columns)
df1 columns: ['account_id', 'holder_name', 'balance']
df2 columns: ['holder_name', 'account_id', 'balance']
How does union() match columns?
union() aligns purely by position: column 1 of df1 goes with column 1 of df2. It only checks that both have the same number of columns, not that the names match. This silently corrupts data.
# DANGEROUS: union by position
unioned_bad = df1.union(df2)
unioned_bad.show()
+------------+------------+--------+ | account_id| holder_name| balance| +------------+------------+--------+ | SAV2001|Rajesh Kumar| 50000.0| |Priya Sharma| SAV2002| 12000.0| ← WRONG! Names and IDs swapped! +------------+------------+--------+
How does unionByName() match columns?
unionByName() aligns by column name. It finds the column named account_id in both and stacks them together, regardless of the physical order.
# SAFE: union by name
unioned_good = df1.unionByName(df2)
unioned_good.show()
+----------+------------+--------+ |account_id| holder_name| balance| +----------+------------+--------+ | SAV2001|Rajesh Kumar| 50000.0| | SAV2002|Priya Sharma| 12000.0| ← CORRECT! Aligned by name +----------+------------+--------+
How does allowMissingColumns work?
In Spark 3.1+, unionByName(allowMissingColumns=True) lets the two DataFrames have different column sets. Missing columns are filled with null.
# Legacy feed: no settlement_date
feed_old = spark.createDataFrame([
("SAV2001", 50000.00)
], ["account_id", "amount"])
# New feed: has settlement_date
feed_new = spark.createDataFrame([
("SAV2002", 12000.00, "2026-01-20")
], ["account_id", "amount", "settlement_date"])
# Without allowMissingColumns: fails
try:
feed_old.unionByName(feed_new).show()
except Exception as e:
print(f"Error: {e}")
# With allowMissingColumns: fills with null
combined = feed_old.unionByName(feed_new, allowMissingColumns=True)
combined.show()
Error: Cannot resolve column name "settlement_date" among (account_id, amount)
+----------+--------+---------------+
|account_id| amount|settlement_date|
+----------+--------+---------------+
| SAV2001| 50000.0| null|
| SAV2002| 12000.0| 2026-01-20|
+----------+--------+---------------+
How do union and unionByName compare?
| Feature | union() | unionByName() |
|---|---|---|
| Column matching | By position | By name |
| Column order matters | Yes (silently corrupts) | No (safe) |
| Column count must match | Yes | Yes (unless allowMissingColumns) |
| Missing columns | Fails | allowMissingColumns fills with null |
| Type compatibility | Positional types must match | Named types must match |
| Safety | Dangerous | Safe |
# Side-by-side comparison
df_a = spark.createDataFrame([(1, "x")], ["id", "name"])
df_b = spark.createDataFrame([("y", 2)], ["name", "id"]) # reversed!
print("union (by position):")
df_a.union(df_b).show() # WRONG: 'y' under id, 2 under name
print("unionByName (by name):")
df_a.unionByName(df_b).show() # CORRECT: matched by name
union (by position): +---+----+ | id|name| +---+----+ | 1| x| | y| 2| ← CORRUPTED +---+----+ unionByName (by name): +---+----+ | id|name| +---+----+ | 1| x| | 2| y| ← CORRECT +---+----+
Why should you always prefer unionByName?
Pipelines evolve: upstream teams add columns, reorder selects, or build DataFrames through different code paths. Any of these can change column order without changing names. With union(), such a harmless change silently misaligns data. With unionByName(), names are stable identifiers.
# Real-world scenario: upstream team changes select order
# Before: SELECT account_id, holder_name, balance FROM accounts
# After: SELECT holder_name, account_id, balance FROM accounts
# With union(): silent data corruption
# With unionByName(): works correctly, no change needed
# Rule: ALWAYS use unionByName
combined = feed_old.unionByName(feed_new, allowMissingColumns=True)
What are the type compatibility rules?
unionByName still requires matched columns to have compatible types. Without allowMissingColumns, it fails on differing column sets, which is desirable as a safety check.
# Type mismatch: fails
df_str = spark.createDataFrame([("SAV2001", "50000")], ["account_id", "amount"])
df_num = spark.createDataFrame([("SAV2002", 12000.0)], ["account_id", "amount"])
try:
df_str.unionByName(df_num).show() # amount: string vs double
except Exception as e:
print(f"Type error: {e}")
# Fix: cast to compatible types before union
df_str_fixed = df_str.withColumn("amount", F.col("amount").cast("double"))
df_str_fixed.unionByName(df_num).show()
+----------+--------+
|account_id| amount|
+----------+--------+
| SAV2001| 50000.0|
| SAV2002| 12000.0|
+----------+--------+
What are common mistakes?
The most common mistake is using union() when column orders may differ. This produces silently corrupted data that passes type checks if the types happen to line up.
# WRONG: using union when teams build DataFrames differently
# team_a: [account_id, amount, branch]
# team_b: [branch, account_id, amount] (different order!)
# union() silently puts branch values in account_id column
# CORRECT: always unionByName
result = team_a.unionByName(team_b)
# ALSO: select canonical order for readability
result = team_a.select("account_id", "amount", "branch") \
.unionByName(team_b.select("account_id", "amount", "branch"))
What should a senior engineer know?
A senior engineer always unions by name, often selects a canonical column order first for readability, and treats plain positional union as something to avoid. They use allowMissingColumns when schemas legitimately differ.
# Senior pattern: canonical select + unionByName
canonical_cols = ["account_id", "holder_name", "amount", "branch", "txn_date"]
def safe_union(df1, df2, cols):
"""Union by name with canonical column order."""
return df1.select(*[F.col(c) if c in df1.columns else F.lit(None).cast("string").alias(c) for c in cols]) \
.unionByName(
df2.select(*[F.col(c) if c in df2.columns else F.lit(None).cast("string").alias(c) for c in cols])
)
result = safe_union(feed_old, feed_new, ["account_id", "amount", "settlement_date"])
result.show()
Visual Overview
union() vs unionByName():
df1: [account_id, holder_name, balance]
df2: [holder_name, account_id, balance] (columns reordered!)
union() – matches by POSITION:
df1 col 0 (account_id) + df2 col 0 (holder_name) → WRONG!
df1 col 1 (holder_name) + df2 col 1 (account_id) → WRONG!
df1 col 2 (balance) + df2 col 2 (balance) → OK
Result: names in ID column, IDs in name column → CORRUPTED
unionByName() – matches by NAME:
df1.account_id + df2.account_id → CORRECT
df1.holder_name + df2.holder_name → CORRECT
df1.balance + df2.balance → CORRECT
Result: columns aligned correctly → SAFE
allowMissingColumns=True (Spark 3.1+):
df_old: [account_id, amount]
df_new: [account_id, amount, settlement_date]
Result: [account_id, amount, settlement_date]
old rows have null settlement_date
Key Takeaway
Both union and unionByName stack two DataFrames into one with all the rows of both, like a SQL UNION ALL, and the difference is purely how columns are aligned. Plain union aligns by position, so it combines the first column of one with the first column of the other regardless of names. If the two DataFrames have the same columns in a different order, union silently puts values under the wrong columns and throws no error, which is one of the nastiest silent data-corruption bugs in Spark.
unionByName aligns by column name, so it stacks account_id on account_id and amount on amount no matter the physical order. In Spark 3.1+, unionByName also accepts allowMissingColumns, which lets the two sides have slightly different column sets: the result is the union of all columns and any side that lacks a column gets null. This is exactly what you want when schemas evolve.
The reason I always prefer unionByName is correctness and resilience. Pipelines change, upstream teams add columns or reorder their selects, and names are stable identifiers while positions are not. At the National Bank of India this bit us when we consolidated an equities feed and a derivatives feed that had the same logical columns but in different order. A positional union produced a report with amounts in the symbol column and symbols in the amount column, with no error. Switching to unionByName fixed it instantly.
The rule is to always union by name, often selecting a canonical column order first so the intent is visible, and to treat plain positional union as something to avoid.
Remember one sentence: union matches columns by position and silently corrupts data when orders differ, while unionByName matches by name and is the safe default.
---