UNION is used to combine the result sets of two or more SELECT statements into one result set.

Key points:

  • Each SELECT must return the same number of columns
  • Columns must have compatible data types
  • UNION removes duplicate rows by default

Step 1: Basic Syntax

SELECT column_list
FROM table1UNION

SELECT column_list
FROM table2;

Step 2: Example tables

Employees_2024

EmployeeID Name Department
1 John IT
2 Jane HR

Employees_2025

EmployeeID Name Department
2 Jane HR
3 Mike IT
4 Sara Finance

Step 3: Simple UNION example

Combine employees from both tables:

SELECT EmployeeID, Name, Department
FROM Employees_2024UNION

SELECT EmployeeID, Name, Department
FROM Employees_2025;

Result (duplicates removed):

EmployeeID Name Department
1 John IT
2 Jane HR
3 Mike IT
4 Sara Finance

Jane appears only once because UNION removes duplicates.

Step 4: UNION ALL (keeps duplicates)

If you want to keep all rows, use UNION ALL:

SELECT EmployeeID, Name, Department
FROM Employees_2024UNION ALL

SELECT EmployeeID, Name, Department
FROM Employees_2025;

Result:

EmployeeID Name Department
1 John IT
2 Jane HR
2 Jane HR
3 Mike IT
4 Sara Finance

Step 5: UNION with different column names

Column names don’t have to match, but positions must match:

SELECT Name, Department
FROM Employees_2024UNION

SELECT FullName, DeptName
FROM Employees_Archive;

Column names are taken from the first SELECT.

Step 6: UNION with WHERE clause

SELECT Name, Department
FROM Employees_2024
WHERE Department = 'IT'UNION

SELECT Name, Department
FROM Employees_2025
WHERE Department = 'IT';

Each SELECT can have its own WHERE condition.

Step 7: ORDER BY with UNION

ORDER BY is applied only once, at the end:

SELECT EmployeeID, Name, Department
FROM Employees_2024UNION

SELECT EmployeeID, Name, Department
FROM Employees_2025
ORDER BY Name ASC;

Categorized in:

SQL Server,