In this tutorial, i am going to explain you about Union and Union All operator in SQL Server with an example. We will also discuss the difference between both of them.

Union and Union All operators in SQL Server are used to combine the result sets of two or more SELECT queries”.

Introduction:

Sometimes we need to combine data from two more Tables or Views into single results. Union and Union All are suitable for such situation. Let’s understand each term in details.

Union

Union:

UNION is similar to join command which selects related records from two tables. All selected columns should be of same data type while using Union command. We can select only distinct value with Union.

The syntax for Union Operator:

 

SELECT ColumnName FROM TableName

Union

SELECT ColumnName FROM TableName

 

Union All:

Union All command is similar to Union command except it selects all the values.

The Syntax for Union All Operator:

 

SELECT ColumnName FROM TableName

Union All

SELECT ColumnName FROM TableName

 

An example of Union and Union All in SQL Server:

I am going to create two tables DailyEmployee and MonthlyEmployee and combine the result sets of both tables using Union and Union All operator in SQL Server.

DailyEmployee Table:

 

CREATE TABLE [dbo].[DailyEmployee]
(
ID INT PRIMARY KEY,
Name VARCHAR(100),
Salary FLOAT
)

Insert few records into DailyEmployee table.

INSERT INTO [dbo].[DailyEmployee](ID,Name,Salary) VALUES(1,'Arun Singh',800000)
INSERT INTO [dbo].[DailyEmployee](ID,Name,Salary) VALUES(2,'Manish Singh',300000)

MonthlyEmployee Table:

CREATE TABLE [dbo].[MonthlyEmployee]
(
ID INT PRIMARY KEY,
Name VARCHAR(100),
Salary FLOAT
)

Insert few records into MonthlyEmployee table.

INSERT INTO [dbo].[MonthlyEmployee](ID,Name,Salary) VALUES(1,'Arun Singh',800000)
INSERT INTO [dbo].[MonthlyEmployee](ID,Name,Salary) VALUES(2,'Lakshya Joshi',300000)

Run the below script and verify the data that we have inserted.

SELECT * FROM [dbo].[DailyEmployee]

SELECT * FROM [dbo].[MonthlyEmployee]

The result of both tables should look like this.

 

Union

Now we want to combine both two result set. So while using Union All, we are combining the result sets from these two queries. Here in output, we will get 4 rows.

SELECT * FROM [dbo].[DailyEmployee]
UNION ALL
SELECT * FROM [dbo].[MonthlyEmployee]

The Result of above query should like this.

Union

Now use Union instead of Union All and notice the difference in result.

SELECT * FROM [dbo].[DailyEmployee]
UNION
SELECT * FROM [dbo].[MonthlyEmployee]

Union

In the case of Union Operator, we will get only 3 records. So Union operator performs distinct operation over the record.

The difference between Union and Union All in SQL Server:

Union removes duplicate rows, whereas Union All doesn’t.

 

Union have to perform the distinct sort to remove duplicates, which makes it less fast than Union All.

Hope you understand the Union and Union All Operator in SQL Server and when should we use Union and Union All.

Read More:

Thank You.

 

Leave a Comment