While UNION ALL combines them without removing duplicates, yielding faster results but keeping all rows, including duplicates, UNION merges and filters out duplicates from different SELECT queries.

Now let's look at the example.

I have started by making two tables, 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 conclusion, the UNION function in SQL Server automatically eliminates duplicate rows while combining the output of several SELECT queries to create a single, cohesive result set. Let's examine the operation of UNION ALL.

SELECT City

FROM Employee

UNION ALL

SELECT City

Employee_One

Employee table records

Employee_One 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).
UNION ALL

In summary
In SQL Server, the UNION ALL method is used to aggregate the output of several SELECT queries without removing duplicate rows. Because duplicate elimination processing is not present in UNION, UNION ALL offers faster performance than UNION and contains all records from the combined queries, making it a good option when maintaining duplicate entries is required.

I hope this post has given you useful knowledge on using UNION and UNION ALL in SQL Server. Best wishes for the future.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.