UNION in SQL Server: Combining Results from Multiple Queries

If you’re working with multiple datasets and wondering how to combine them into a single result, understanding how to use UNION in SQL Server is essential. The UNION operator allows you to join the results of two or more SELECT queries into one result set—perfect for situations where your data is split across different tables or queries but shares the same structure.

In this post, I’ll explain how UNION works in SQL Server, when to use it, and how it differs from UNION ALL. I’ll also provide examples to help you master combining queries efficiently.

What Does UNION Do in SQL Server?

The UNION operator in SQL Server is used to combine the result sets of two or more SELECT statements into a single result. It removes duplicate rows by default.

Basic UNION Syntax:

SELECT column1, column2
FROM table1

UNION

SELECT column1, column2
FROM table2

Important Note: All SELECT statements used with UNION must have the same number of columns, in the same order, and with compatible data types.

Example: Using UNION in SQL Server to Combine Data

Let’s first create two tables that contain salaries for 2024 and 2025. Run this query in SQL Server Management Studio:

-- Create tables
CREATE TABLE EmployeeSalary2024 (
    EmployeeID INT,
    Amount DECIMAL(10, 2)
);

CREATE TABLE EmployeeSalary2025 (
    EmployeeID INT,
    Amount DECIMAL(10, 2)
);

-- Insert salaries for 2024
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (1, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (2, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (3, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (4, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (5, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (6, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (7, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (8, 10000);
INSERT INTO EmployeeSalary2024 (EmployeeID, Amount) VALUES (9, 10000);

-- Insert salaries for 2025
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (1, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (2, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (3, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (4, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (5, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (6, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (7, 10000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (8, 12000);
INSERT INTO EmployeeSalary2025 (EmployeeID, Amount) VALUES (9, 15000);

This code will create two new tables (EmployeeSalary2024 and EmployeeSalary2024) with a $10,000 salary for each of the Employees that we currently have in our Employees table. Make sure you notice that the salary for employee number 8 increased to $12,000 in 2025, and the one for employee 9 increased to $15,000.

If you run the SELECT query on any of these tables, you should see the results that look like this:

Now, let’s try to create a UNION of these tables, since they already have the same columns (EmployeeID and Amount):

SELECT EmployeeID, Amount
FROM EmployeeSalary2024

UNION

SELECT EmployeeID, Amount
FROM EmployeeSalary2025

This is what you should see in SSMS:

This query returns a combined list of salaries for both years, removing any duplicate rows that appear in both tables. Notice how the records for employees number 8 and 9 appear twice, and there are no duplicate rows for the rest of the employees.

How to Use UNION ALL in SQL

Sometimes, you want to include all rows, even if they’re duplicates. That’s where UNION ALL comes in.

UNION ALL Syntax:

SELECT column1, column2
FROM table1

UNION ALL

SELECT column1, column2
FROM table2

The difference here is that UNION ALL does not remove duplicates – which makes it faster and often more suitable when performance matters or when duplicates are meaningful.

Example of UNION ALL:

SELECT EmployeeID, Amount
FROM EmployeeSalary2024

UNION ALL

SELECT EmployeeID, Amount
FROM EmployeeSalary2025

This version will include all records from both tables, even if some are identical. Here is what it looks like in SSMS:

When to Use UNION in SQL Server?

Use UNION when:

• You need a clean list without duplicates.
• You’re creating a summary or overview report.
• You expect potential overlaps between your data sources.

Use UNION ALL when:

• You want all results, including duplicates.
• Performance is a concern.
• You’re analyzing raw data where repetition matters.

Useful Tip: Use ORDER BY After UNION

You can sort the combined results by adding ORDER BY at the end of your query:

SELECT EmployeeID, Amount
FROM EmployeeSalary2024

UNION ALL

SELECT EmployeeID, Amount
FROM EmployeeSalary2025

ORDER BY Amount DESC

Conclusion

Understanding how to use UNION in SQL Server helps you merge multiple datasets into one clean output. Whether you need to eliminate duplicates with UNION or include everything with UNION ALL, knowing when and how to use each will make your SQL queries more powerful and flexible.

If you’re still unsure how to use UNION ALL in SQL, try running both versions of your query and compare the results side by side—you’ll quickly see which one fits your needs best.

Let me know if you’d like to dive deeper into more advanced union scenarios, and make sure you check my previous post on What Is Subquery in SQL.

If you would like to learn more about SQL queries, check out this book: T-SQL Fundamentals (Developer Reference)