UNION and UNION ALL Operators in SQL Server

Back to: SQL Server Tutorial

UNION and UNION ALL Operators in SQL Server with Examples

In this article, I am going to discuss UNION and UNION ALL Operators in SQL Server with Examples. Please read our previous article where we discussed the EXISTS Operator in detail. The UNION and UNION ALL Operator belongs to the category of SET Operators. As part of this article, we are going to discuss the following pointers in detail.

1. What are SET Operators in SQL Server?
2. What are UNION and UNION ALL operators in SQL Server?
3. Multiple examples to understand UNION and UNION ALL Operators.
4. Differences between UNION and UNION ALL Operators.
5. Difference between JOIN and UNION in SQL Server.

What are SET Operators in SQL Server?

The SET Operators in SQL Server are mainly used to combine the result of more than 1 select statement and return a single result set to the user. There are the following 4 set operators available in SQL Server. They are as follows:

1. UNION: Combine two or more result sets into a single set, without duplicates.
2. UNION ALL: Combine two or more result sets into a single set, including all duplicates.
3. INTERSECT: Takes the data from both result sets which are in common.
4. EXCEPT: Takes the data from the first result set, but not in the second result set (i.e. no matching to each other)

Points to Remember while working with Set Operations:

1. The result sets of all queries must have the same number of columns.
2. In every result set the data type of each column must be compatible (well-matched) to the data type of its corresponding column in other result sets.
3. In order to sort the result, an ORDER BY clause should be part of the last select statement. The column names or aliases must be found out by the first select statement.

Note: In this article, I am going to discuss Union and Union All operators, and the rest two set operators are going to be discussed in the upcoming articles.

UNION and UNION ALL operators in SQL Server

The UNION and UNION ALL operators in SQL Server are used to combine the result-set of two or more SELECT statements into a single result set. Let us understand UNION and UNION ALL Operators with some examples. We are going to use the following “EmployeeIndia” and “EmployeeUK” tables to understand these two operators.

Please use the below SQL Script to create Database EmployeeDB, tables EmployeeIndia and EmployeeUK, and populate these two tables with the required test data.

CREATE DATABASE EmployeeDB
GO

USE EmployeeDB
GO

CREATE TABLE EmployeeIndia
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeIndia VALUES(1, 'Pranaya', 'Male','IT')
INSERT INTO EmployeeIndia VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeIndia VALUES(3, 'Preety', 'Female','HR')
INSERT INTO EmployeeIndia VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeIndia VALUES(5, 'Anurag', 'Male','IT')
GO

CREATE TABLE EmployeeUK
(
  ID INT PRIMARY KEY,
  Name VARCHAR(50),
  Gender VARCHAR(10),
  Department VARCHAR(50)
)
GO

INSERT INTO EmployeeUK VALUES(1, 'James', 'Male','IT')
INSERT INTO EmployeeUK VALUES(2, 'Priyanka', 'Female','IT')
INSERT INTO EmployeeUK VALUES(3, 'Sara', 'Female','HR')
INSERT INTO EmployeeUK VALUES(4, 'Subrat', 'Male','HR')
INSERT INTO EmployeeUK VALUES(5, 'Pam', 'Female','HR')
GO

Combining the rows of EmployeeIndia and EmployeeUK using UNION ALL

Let us combine the two tables data using UNION ALL Operator and see the result.

SELECT ID, Name, Gender, Department FROM EmployeeIndia
UNION ALL
Select ID, Name, Gender, Department FROM EmployeeUK

Query Results of UNION ALL

Combining the rows of EmployeeIndia and EmployeeUK using UNION

Let us combine the two tables data using UNION Operator and see the result.

SELECT ID, Name, Gender, Department FROM EmployeeIndia
UNION
SELECT ID, Name, Gender, Department FROM EmployeeUK

Query Results of UNION Operator

Differences between UNION and UNION ALL Operator in SQL Server

From the output, it is very clear that UNION removes duplicate rows whereas UNION ALL does not remove the duplicate rows. When we use a UNION operator to remove the duplicate rows from the result set, the SQL server has to do a distinct operation which is time-consuming. For this reason, UNION ALL is much faster than UNION.

Note: If you want to see the cost of DISTINCT SORT, you can turn on the estimated query execution plan using CTRL + L.

For UNION and UNION ALL to work, the Number, Data types, and the order of the columns in the select statements should be the same. If you want to sort, the results of UNION or UNION ALL, the ORDER BY clause should be used on the last SELECT statement as shown below.

SELECT ID, Name, Gender, Department FROM EmployeeIndia
UNION
SELECT ID, Name, Gender, Department FROM EmployeeUK
ORDER BY Name

The result set of the above query

The following query raises a syntax error

SELECT ID, Name, Gender, Department FROM EmployeeIndia
ORDER BY Name
UNION
SELECT ID, Name, Gender, Department FROM EmployeeUK

Error: Incorrect syntax near the keyword ‘UNION’.

Difference between JOIN and UNION in SQL Server

JOINS and UNIONS are two different things. UNION combines the result-set of two or more select queries into a single result-set which includes all the rows from all the queries in the union, whereas JOINS, retrieves data from two or more tables based on logical relationships between the tables. In short, UNION combines rows from 2 or more tables, where JOINS combines columns from 2 or more tables.

If this is not clear at the moment then don’t worry we will discuss this with examples when we discuss JOINs in SQL Server.

In the next article, I am going to discuss the EXCEPT Operator in SQL Server with Examples. Here, In this article, I try to explain the UNION and UNION ALL Operators in SQL Server step by step with some examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this UNION and UNION ALL Operators in SQL Server with Examples article.