UNION and UNION ALL Operators

UNION and UNION ALL Operators in SQL Server

SET operators are mainly used to combine the result of more than 1 select statement and return a single result set to the user. In this article, I will discuss Union and Union All operators in SQL Server.

The set operators work on complete rows of the queries, so the results of the queries must have the same column name, same column order and the types of columns must be compatible. There are the following 4 set operators in SQL Server:

  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)
Rules on 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.

In this article, I am going to discuss Union and Union All operators and 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 queries into a single result set.

Use below script to create Database EmployeeDB, tables EmployeeIndia and EmployeeUK and populate these two tables with some 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
Fetch the Records:

SELECT * FROM EmployeeIndia

UNION and UNION ALL Operators in SQL Server

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
Fetch the records:

SELECT * FROM EmployeeUK

UNION and UNION ALL Operators in SQL Server

Combining the rows of EmployeeIndia and EmployeeUK using UNION ALL

SELECT ID, Name, Gender, Department FROM EmployeeIndia

UNION ALL

Select ID, Name, Gender, Department FROM EmployeeUK

Query Results of UNION ALL

UNION and UNION ALL Operators in SQL Server

Combining the rows of EmployeeIndia and EmployeeUK using UNION

SELECT ID, Name, Gender, Department FROM EmployeeIndia

UNION

SELECT ID, Name, Gender, Department FROM EmployeeUK

Query Results of UNION

UNION and UNION ALL Operators in SQL Server

Differences between UNION and UNION ALL

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

UNION and UNION ALL Operators in SQL Server

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

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, retrieve 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 combine columns from 2 or more table.

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 will discuss EXCEPT Operator in SQL Server with some examples.

SUMMARY

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 need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *