UNION vs UNION ALL: The Key SQL Difference for Merging Datasets

A simple keyword that can dramatically affect your query's performance.

Welcome! When you need to combine the results of two or more `SELECT` statements into a single result set, you'll use a `UNION` clause. But if you've ever had a query that runs slowly, the culprit might be a single, seemingly minor word. Understanding the crucial difference between **UNION** and **UNION ALL** is essential for writing efficient and correct SQL queries.

The UNION Operator

The **`UNION`** operator is used to combine the result sets of two or more `SELECT` statements. The key characteristic of `UNION` is that it automatically **removes duplicate rows** from the final result set. It's like a combination of a `SELECT` and a `DISTINCT` clause.

Key Characteristics:

• Removes Duplicates: If a row exists in both result sets, it will only appear once in the final output.
• Slower Performance: Because the database has to perform a scan to identify and remove duplicate rows, this operation can be computationally expensive and slow down your query, especially with large datasets.

Example: Let's combine a list of customers from two regions.

SELECT email FROM customers_east
UNION
SELECT email FROM customers_west;
            

If a customer's email exists in both tables, it will only be shown once in the final result.

---

The UNION ALL Operator

The **`UNION ALL`** operator also combines the result sets of two or more `SELECT` statements, but it **does not remove duplicate rows**. It simply appends the second result set to the end of the first, regardless of whether duplicates exist.

Key Characteristics:

• Keeps Duplicates: All rows from both result sets are returned, including any duplicates.
• Faster Performance: Because the database does not have to perform the time-consuming duplicate-checking step, `UNION ALL` is significantly faster and more efficient than `UNION`.

Example: Let's combine a list of all orders from two tables to get a complete history.

SELECT order_id FROM orders_2023
UNION ALL
SELECT order_id FROM orders_2024;
            

Even if an order ID appears in both tables (which is unlikely but possible with bad data), both instances would be returned.

The Golden Rule

Always use `UNION ALL` unless you specifically need to remove duplicates. In most scenarios, you're looking for a complete list of all records, and `UNION ALL` will give you a much faster and more performant query. Use `UNION` only when you are certain that you need a unique, de-duplicated result set.

Master SQL with more of our coding tutorials!