Difference Between UNION and UNION All (SQL Server)?

Description

UNION merges and filters out duplicates from various SELECT queries, whereas UNION ALL combines them without eliminating duplicates, providing quicker results but retaining all rows, including duplicates.

Let's get into the example.

First, I have created two tables called Employee and Employee_One.

CREATE TABLE Employee
(
 employeeId INT IDENTITY(1,1) PRIMARY KEY,
 employeeName VARCHAR(50),
 City VARCHAR(100)
)

The Employee table has records as follows.

CREATE TABLE Employee_One
(
 employeeId INT IDENTITY(1,1) PRIMARY KEY,
 employeeName VARCHAR(50),
 City VARCHAR(100)
)

The Employee_One table has records as follows.

Let's explore how the UNION operation works.

SELECT City FROM Employee UNION SELECT City FROM Employee_One

Employee table records Employee_One table records

After using UNION between these two tables, we will get results as follows (removing duplicates).

In summary, the UNION operation in SQL Server combines results from multiple SELECT queries, creating a unified result set while automatically removing duplicate rows.

Let's see how the UNION ALL operation works.

SELECT City FROM Employee UNION ALL SELECT City Employee_One

Employee table records Employee_One table records

After using UNION ALL between these two tables, we will get results as follows (including all records - without removing duplicate records).

Conclusion

the UNION ALL operation in SQL Server serves to combine results from multiple SELECT queries without eliminating duplicate rows. Unlike UNION, UNION ALL includes all records from the combined queries, making it a suitable choice when preserving duplicate entries is necessary, offering a faster performance compared to UNION due to the absence of duplicate elimination processing.

I hope this article has provided valuable insights into how to utilize UNION and UNION ALL in SQL Server. Best wishes moving forward.