SQL - UNION vs UNION ALL

SQL: UNION vs. UNION ALL

The Purpose of UNION Operators

UNION and UNION ALL are set operators in SQL used to combine the result sets of two or more SELECT statements into a single result set. This is useful when you have data of a similar structure in multiple tables and want to view it as one.

Key Requirements for UNION

For any UNION operation to succeed, the SELECT statements involved must be “union compatible,” which means they must meet two strict conditions:

• Same Number of Columns: Each SELECT statement must retrieve the same number of columns.
• Compatible Data Types: The data types of corresponding columns must be compatible (e.g., you can union a VARCHAR(50) with a VARCHAR(100), or an INT with a DECIMAL, but not a DATE with an INT).

The column names in the final result set are taken from the first SELECT statement.

UNION: Combining with Duplicate Removal

The UNION operator combines the result sets and then performs an additional step: it scans the combined results and removes any duplicate rows. The result is a set of only the unique rows from all queries.

Syntax

SELECT column1, column2 FROM TableA
UNION
SELECT columnA, columnB FROM TableB;

Example

Imagine a company stores its employees in two tables: FullTimeEmployees and Contractors. Both tables have a similar structure.

-- Table: FullTimeEmployees
CREATE TABLE FullTimeEmployees (ID INT, Name VARCHAR(50));
INSERT INTO FullTimeEmployees VALUES
(1, 'Alice'),
(2, 'Bob');

-- Table: Contractors
CREATE TABLE Contractors (EmpID INT, FullName VARCHAR(50));
INSERT INTO Contractors VALUES
(3, 'Charlie'),
(1, 'Alice'); -- Alice also does contract work

To get a unique list of all people working for the company, we use UNION. Note that the row for ‘Alice’ appears in both tables.

SELECT ID, Name FROM FullTimeEmployees
UNION
SELECT EmpID, FullName FROM Contractors;

Output

UNION removes the duplicate ‘Alice’ row.

ID	Name
1	Alice
2	Bob
3	Charlie

UNION ALL: Combining All Rows

The UNION ALL operator is simpler and faster. It combines the result sets from all queries but does not perform any check for duplicates. It simply appends the rows from the second query to the rows from the first.

Syntax

SELECT column1, column2 FROM TableA
UNION ALL
SELECT columnA, columnB FROM TableB;

Example

Using the same tables, let’s see what UNION ALL does. This query would be useful if we wanted to count the total number of work contracts, including duplicates.

SELECT ID, Name FROM FullTimeEmployees
UNION ALL
SELECT EmpID, FullName FROM Contractors;

Output

UNION ALL includes every row from both tables, including the duplicate ‘Alice’.

ID	Name
1	Alice
2	Bob
3	Charlie
1	Alice

Performance: The Critical Difference

The most important difference in a real-world application is performance. UNION ALL is significantly faster than UNION.

This is because UNION must perform extra work to find and eliminate duplicate rows. This typically involves sorting the entire combined result set or building a hash table of all rows—both of which are resource-intensive operations, especially with large datasets.

UNION ALL does none of this. It simply concatenates the results. It’s a much lighter operation.

Best Practice: Always use UNION ALL unless you specifically need to remove duplicate rows. If you know the rows from the combined queries are already distinct, using UNION ALL provides a free performance boost.

Summary: When to Use Which

Characteristic	UNION	UNION ALL
Duplicate Rows	Removes duplicates	Keeps all duplicates
Performance	Slower (due to sorting/hashing)	Faster (simple concatenation)
Use Case	Get a unique list of items from multiple sources.	Combine all records from multiple sources, especially for aggregations like COUNT or when you know duplicates are not possible.