unionByName
Stack DataFrames by column name instead of position, and survive schema drift with allowMissingColumns.
On this page
Show code in
Every code block on the page follows this.
You will learn
- Why union matches columns by position, and how that corrupts data
- How unionByName matches by name instead
- How allowMissingColumns handles schema drift
- Why union does not remove duplicates
union stacks DataFrames by column position, so the same columns in a different order are silently mixed up. unionByName matches by name, and with allowMissingColumns=True fills absent columns with null.Position vs name
union (and unionAll, the same thing) puts the first column of one DataFrame under the first column of the other, regardless of names. If the types happen to be compatible, nothing fails: you just get names in the id column.
Step by step
January has id, name, city. February's extract has the columns in a different order and a new tier column.
jan + feb
| id | name | city |
|---|---|---|
| 1 | Asha | Pune |
| 2 | Ben | Delhi |
unionByName(allowMissingColumns=True)
| id | name | city | tier |
|---|---|---|---|
| 1 | Asha | Pune | null |
| 2 | Ben | Delhi | null |
| 3 | Chitra | Kochi | gold |
| 4 | Dev | Agra | null |
Columns are matched by name; January rows get null for the tier column they never had.
Run the example
result = jan.unionByName(feb, allowMissingColumns=True)
SELECT id, name, city, NULL AS tier FROM jan UNION ALL SELECT id, name, city, tier FROM feb
Switch to PySpark to edit and run this example in your browser.
SQL has no union-by-name in most Spark versions, so you list the columns in the same order on both sides, adding NULL AS col for missing ones. Some engines, such as Databricks SQL, accept UNION ALL BY NAME; check your engine before relying on it.
union is UNION ALL
SQL's UNION removes duplicates; UNION ALL keeps them. The DataFrame union and unionByName behave like UNION ALL: no deduplication. Add .distinct() if you need SQL UNION semantics, and remember that costs a shuffle.
Types
Columns with the same name must have compatible types. Spark widens where it can (int and long to long, int and double to double). Incompatible types, like a struct and a string, fail. With allowMissingColumns, the null column added for a missing field takes the type from the other side.
Under the hood
A union does not move data. It just concatenates the partitions of both inputs, so the result has the sum of their partition counts. Unioning hundreds of small DataFrames in a loop creates a very wide plan and many small partitions; prefer reading all files in one read, or reduce partitions before writing.
Common mistakes
Using union on DataFrames with different column order
Expecting duplicates to be removed
Long union loops
Key takeaways
- union matches columns by position; unionByName by name.
- allowMissingColumns=True fills columns one side lacks with null.
- Both behave like UNION ALL: duplicates are kept.
- A union concatenates partitions; it does not shuffle.
Check yourself
3 questions1. df1 has (id, name), df2 has (name, id), both strings. What does df1.union(df2) do?
Show the answer
Puts df2 names under id, silently. union is positional; compatible types mean no error, just wrong data.
2. Does unionByName remove duplicate rows?
Show the answer
No. It is UNION ALL semantics. Use distinct() to deduplicate.
3. With allowMissingColumns=True, what do rows from the side missing a column get?
Show the answer
null. Missing columns are filled with null.
Practice it
Interview problems that use unionByName: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: DataFrame.unionByName · Set operators (SQL)