---
title: "Mastering Union in Databricks – Combining Data Efficiently"
description: "Explained union in databricks. You will know how it is different from SQL."
url: https://srinimf.com/2025/03/05/mastering-union-in-databricks-combining-data-efficiently/
date: 2025-03-05
modified: 2026-10-10
author: "Srini"
image: https://i0.wp.com/srinimf.com/wp-content/uploads/2025/03/databricks-union.png?fit=2240%2C1260&ssl=1
categories: ["ai", "Bigdata"]
tags: ["Union in Databricks"]
type: post
lang: en
---

# Mastering Union in Databricks – Combining Data Efficiently

Here is a complete story of UNION [in Databricks](https://srinimf.com/2025/10/09/dlt-in-databricks-event-logs-pipelines-and-duplicate-handling-explained/). When working with large datasets in **Databricks**, you may need to merge data from different sources. The **UNION** operation helps you stack datasets consistently. In this post, we’ll look at **UNION in Databricks**, its variations, and tips to improve performance.

## **What is UNION in Databricks?**

The **UNION** operation in **Databricks (Apache Spark)** merges two or more DataFrames with the same structure. It works like SQL’s **UNION ALL** but is designed for distributed datasets in **PySpark**.

### **[Types of](https://srinimf.com/2024/09/15/different-types-of-joins-in-pandas-a-comprehensive-guide/) UNION Operations**

1. **UNION (Equivalent to UNION ALL in SQL)**
   - Stacks two DataFrames but **does not remove duplicates**.
   - More efficient since it avoids the costly deduplication step.
2. **UNION DISTINCT**
   - Removes duplicate records after stacking DataFrames.
   - Equivalent to SQL’s **UNION**.
   - More resource-intensive since it requires a **shuffle** operation.

![Databricks udemy course](https://i0.wp.com/srinimf.com/wp-content/uploads/2025/03/pexels-photo-9052312.jpeg?resize=1880%2C1253&ssl=1)

Azure Databricks & Spark For Data Engineers:Hands-on Project

## **Using UNION in Databricks (PySpark Examples)**

Let’s consider two employee DataFrames with the same schema.

### **1. UNION (Combine Without Deduplication)**

```
from pyspark.sql import SparkSession 
from pyspark.sql.types import StructType, StructField, IntegerType, StringType 

# Initialize Spark session spark = SparkSession.builder.appName("UnionExample").getOrCreate() 

# Define schema schema = StructType([ StructField("id", IntegerType(), True), StructField("name", StringType(), True), StructField("department", StringType(), True) ]) 

# Create two DataFrames 
df1 = spark.createDataFrame([(1, "Alice", "HR"), (2, "Bob", "IT")], schema) 
df2 = spark.createDataFrame([(2, "Bob", "IT"), (3, "Charlie", "Finance")], schema) 

# Perform UNION (equivalent to UNION ALL in SQL) 
union_df = df1.union(df2) union_df.show()
```

Related[Joining Two JSON Files Using a Common Key in PySpark (With Examples)](https://srinimf.com/2025/04/26/joining-two-json-files-using-a-common-key-in-pyspark-with-examples/)

### **Output:**

```
+---+-------+----------+ | id| name |department| +---+-------+----------+ | 1| Alice | HR | | 2| Bob | IT | | 2| Bob | IT | | 3|Charlie| Finance | +---+-------+----------+
```

Here, **duplicate rows** (Bob from IT) are retained.

### **2. UNION DISTINCT (Combine With Deduplication)**

```
union_distinct_df = df1.union(df2).distinct() 
union_distinct_df.show()
```

### **Output:**

```
+---+-------+----------+ | id| name |department| +---+-------+----------+ | 1| Alice | HR | | 2| Bob | IT | | 3|Charlie| Finance | +---+-------+----------+
```

This removes duplicate rows, mimicking SQL’s **UNION DISTINCT**.

> [Best selling Shoes and Bags](https://amzn.to/4mVq39C)

## **Key Considerations When Using UNION in Databricks**

### **1. Data Schema Must Match**

- **Column names, order, and [data types](https://srinimf.com/2012/06/17/db2-large-object-data-types-lob/) must be identical** in all DataFrames.
- If schemas differ, align them before applying UNION:

```
from pyspark.sql.functions import lit 
df1 = df1.withColumn("salary", lit(None).cast("int")) 
df2 = df2.withColumn("salary", lit(None).cast("int")) 
aligned_union_df = df1.union(df2)
```

### **2. Performance Considerations**

- **Prefer `union()` over `distinct()` unless necessary** to avoid expensive shuffling.
- If deduplication is required, consider **using `dropDuplicates()`** instead of `distinct()` for better control:

```
union_df.dropDuplicates(["id"]).show()
```

- **Optimize storage format** (e.g., Parquet) to improve performance.

## **When to Use UNION in Databricks**

In Databricks, you should use UNION when you need [to combine](https://srinimf.com/2023/06/19/how-to-merge-multiple-csv-files-top-pandas-program/) the results from multiple datasets while ensuring they have the same number of columns and compatible data types.

## **Conclusion**

Databricks provides powerful ways to **combine datasets** using UNION. By understanding the differences between `union()` and `union().distinct()`, you can optimize performance while maintaining data integrity.

**Avoid unnecessary deduplication** unless required for large datasets, and always ensure schema alignment for smooth UNION operations.

Have questions or a unique UNION use case? Drop them in the comments below!

### Share this:

-
- [Tweet](https://twitter.com/share)
- [![](https://i0.wp.com/assets.pinterest.com/images/pidgets/pinit_fg_en_rect_gray_20.png)](https://www.pinterest.com/pin/create/link/?url=https%3A%2F%2Fsrinimf.com%2F2025%2F03%2F05%2Fmastering-union-in-databricks-combining-data-efficiently%2F&media=https%3A%2F%2Fi0.wp.com%2Fsrinimf.com%2Fwp-content%2Fuploads%2F2025%2F03%2Fdatabricks-union.png%3Ffit%3D1200%252C675%26ssl%3D1&description=Mastering%20Union%20in%20Databricks%20-%20Combining%20Data%20Efficiently)
-
- [Share on Tumblr](https://www.tumblr.com/share)
- [Share on WhatsApp (Opens in new window) WhatsApp](https://srinimf.com/2025/03/05/mastering-union-in-databricks-combining-data-efficiently/?share=jetpack-whatsapp)
- [More](#)
-

- [Print (Opens in new window) Print](https://srinimf.com/2025/03/05/mastering-union-in-databricks-combining-data-efficiently/?share=print)
- [Share on Reddit (Opens in new window) Reddit](https://srinimf.com/2025/03/05/mastering-union-in-databricks-combining-data-efficiently/?share=reddit)
- [Share on Telegram (Opens in new window) Telegram](https://srinimf.com/2025/03/05/mastering-union-in-databricks-combining-data-efficiently/?share=telegram)
- [Email a link to a friend (Opens in new window) Email](mailto:?subject=%5BShared%20Post%5D%20Mastering%20Union%20in%20Databricks%20-%20Combining%20Data%20Efficiently&body=https%3A%2F%2Fsrinimf.com%2F2025%2F03%2F05%2Fmastering-union-in-databricks-combining-data-efficiently%2F&share=email)
-

### Like this:

Like Loading…