Union vs Union All in Databricks: Stacking DataFrames by Position or by Name

1.union(): stacking rows by position

A join puts columns from two inputs side by side. A union stacks rows instead: it returns the rows of one DataFrame followed by the rows of another. In PySpark the method is df1.union(df2). It takes one argument, the other DataFrame, and returns a new DataFrame with the combined rows.

The method name suggests a set union, but union() does not remove duplicate rows. If you want each distinct row once, chain distinct() after it, as the reference example does.

df1 = spark.createDataFrame([(1, 'A'), (2, 'B'), (3, 'C')], ['id', 'value'])
df2 = spark.createDataFrame([(3, 'C'), (4, 'D')], ['id', 'value'])
df3 = df1.union(df2).distinct().sort("id")
df3.show()
# +---+-----+
# | id|value|
# +---+-----+
# |  1|    A|
# |  2|    B|
# |  3|    C|
# |  4|    D|
# +---+-----+

The second thing to know about union() is how it lines columns up. It matches them by position, not by name, which is the standard SQL behavior. If df2 had the same columns in the order value, id, Spark would not reorder them. Its first column would be stacked under id and its second under value.

Sources1

2.SQL UNION vs UNION ALL

Databricks SQL has three set operators: UNION, INTERSECT and EXCEPT. Each takes an optional ALL or DISTINCT qualifier. UNION returns the rows of the first subquery plus the rows of the second. The qualifier decides what happens to duplicates. ALL keeps them. DISTINCT removes them, and DISTINCT is the default.

> CREATE TEMPORARY VIEW number1(c) AS VALUES (3), (1), (2), (2), (3), (4);

> CREATE TEMPORARY VIEW number2(c) AS VALUES (5), (1), (1), (2);

> SELECT c FROM number1 UNION ALL (SELECT c FROM number2);
  3
  1
  2
  2
  3
  4
  5
  1
  1
  2

Notice that the PySpark union() method matches SQL UNION ALL, not SQL UNION. If you translate a SQL UNION into PySpark, you need union() followed by distinct(). The SQL operators also check the shape of their inputs. Both subqueries must have the same number of columns, and each pair of corresponding columns must share a least common type. A column-count mismatch raises NUM_COLUMNS_MISMATCH, and incompatible types raise INCOMPATIBLE_COLUMN_TYPE. Each result column takes the least common type of the two input columns.

Sources2

3.unionByName: matching columns by name

Position-based matching becomes a problem when two sources have the same columns in a different order, or when one source has gained a column the other lacks. unionByName(other, allowMissingColumns=False) lines columns up by name instead. In the example below, df2 lists its columns as col1, col2, col0, and each value still lands under the matching column of df1.

df1 = spark.createDataFrame([[1, 2, 3]], ["col0", "col1", "col2"])
df2 = spark.createDataFrame([[4, 5, 6]], ["col1", "col2", "col0"])
df1.unionByName(df2).show()
# +----+----+----+
# |col0|col1|col2|
# +----+----+----+
# |   1|   2|   3|
# |   6|   4|   5|
# +----+----+----+

By default, both DataFrames must have the same set of column names. Set allowMissingColumns=True and the result contains every column from either side, with null filling in where a column was missing from one input. In the reference example, df1 has col0 but no col3, and df2 has col3 but no col0. The result has four columns: df1's row gets NULL for col3, and df2's row gets NULL for col0.

df1 = spark.createDataFrame([[1, 2, 3]], ["col0", "col1", "col2"])
df2 = spark.createDataFrame([[4, 5, 6]], ["col1", "col2", "col3"])
df1.unionByName(df2,  ? =True).show()

Model answer: union() matches by position, so the two extracts do not have the same shape and the new column cannot be lined up by name. unionByName() lines columns up by name, but by default both inputs must have the same set of column names, so it does not fit extracts that differ by one column. unionByName(allowMissingColumns=True) is the fit here: the result keeps the new column, and the rows from the older extract get null in it.

Sources3

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.PySpark union() behaves like SQL UNION and removes duplicate rows.Why is that wrong?

   union() keeps every row, like SQL UNION ALL. You have to call distinct() to deduplicate.

   Covered in union(): stacking rows by position

2. 2.In Databricks SQL, UNION keeps duplicates unless you write UNION DISTINCT.Why is that wrong?

   DISTINCT is the default qualifier, so plain UNION removes duplicates. Only UNION ALL keeps them.

   Covered in SQL UNION vs UNION ALL

3. 3.union() lines up columns with the same name, so the column order of the two DataFrames does not matter.Why is that wrong?

   union() matches columns by position. Use unionByName to match them by name.

   Covered in unionByName: matching columns by name