MS SQL Server 24 - Understanding UNION and UNION ALL in SQL Server

When managing data in SQL Server, you often need to combine results from multiple queries. SQL provides two primary operators for this purpose: UNION and UNION ALL. Knowing the differences between these operators and when to use each can significantly impact the performance and accuracy of your queries.

What is UNION?

The UNION operator combines the results of two or more SELECT queries into a single result set and automatically removes duplicate rows, ensuring that each row is unique.

Syntax:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

What is UNION ALL?

The UNION ALL operator also combines the results of two or more SELECT queries into a single result set but includes all rows, even duplicates. This means it returns all rows from the combined queries.

Syntax:

SELECT column1, column2, ...
FROM table1
UNION ALL
SELECT column1, column2, ...
FROM table2;

Key Differences:

Duplicates:

• UNION: Removes duplicate rows.
• UNION ALL: Includes duplicate rows.

Performance:

• UNION: Slower due to the need to sort and remove duplicates.
• UNION ALL: Faster as it does not require sorting to remove duplicates.

Step-by-Step Example

Let's explore a practical example to understand how UNION and UNION ALL work.

Step 1: Setting Up Sample Tables

First, create two sample tables named Employees2023 and Employees2024 to store employee data for different years.

CREATE TABLE Employees2023 (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

CREATE TABLE Employees2024 (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Department NVARCHAR(50)
);

INSERT INTO Employees2023 (EmployeeID, FirstName, LastName, Department)
VALUES 
(1, 'John', 'Doe', 'IT'),
(2, 'Jane', 'Smith', 'HR'),
(3, 'Sam', 'Williams', 'Finance');

INSERT INTO Employees2024 (EmployeeID, FirstName, LastName, Department)
VALUES 
(3, 'Sam', 'Williams', 'Finance'),
(4, 'Anna', 'Brown', 'IT'),
(5, 'Mike', 'Davis', 'HR');

Step 2: Using UNION

Combine the results from both tables using UNION to remove duplicate entries.

SELECT EmployeeID, FirstName, LastName, Department
FROM Employees2023
UNION
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees2024;

Output:

Step 3: Using UNION ALL

Combine the results from both tables using UNION ALL to include all rows, including duplicates.

SELECT EmployeeID, FirstName, LastName, Department
FROM Employees2023
UNION ALL
SELECT EmployeeID, FirstName, LastName, Department
FROM Employees2024;

Step 4: Observing Performance

To observe the performance differences, consider larger datasets. The UNION operator requires more processing power to eliminate duplicates, whereas UNION ALL simply concatenates the results.

Conclusion

Choosing between UNION and UNION ALL depends on your specific use case:

• Use UNION when you need to ensure that the result set contains only unique rows.
• Use UNION ALL when you need to include all rows, including duplicates, and aim for better performance.

Understanding these differences allows you to optimize your queries and achieve the desired results efficiently. By mastering these operators, you can handle complex data retrieval tasks and ensure your SQL Server database operations are both accurate and performant.