Difference Between Union & Union All In SQL Server 2017

Introduction

 
In this article, I am going to explain to you:
  1. When and how to use Union and Union All.
  2. The difference between Union and Union All.
  3. The difference between Join and Union.
Here, we will be using SQL Server 2017, or you can use SQL Server 2008 or above.
 
Read my previous Joins in SQL Server 2017 part of this article using the below links before reading this article:
Prerequisites
 
SQL Server 2017 or you can use SQL server 2008, or the above version.
 
First, we will create a Database and two tables to apply the UNION and UNION ALL for understanding.
 

Creating Database and Two Tables

 
Step 1 - Create a Database
 
Open your SQL Server and use the following script to create the “chittadb” Database.
  1. Create database chittadb  
Now, select the script query then press F5 or click on the Execute button to execute the above script.
 
You should see a message, “Command(s) completed successfully.” This means your new database has been created.
 
Step 2 - Create first table
 
Open your SQL Server and use the following script to create the table “tbl_Mcastudents”.
  1. create table tbl_Mcastudents  
  2. (  
  3.    Id int primary key not null identity(1,1),  
  4.    Name nvarchar(50),  
  5.    Location nvarchar(30),  
  6.    Gender varchar(10)  
  7. )   
Execute the above query to create “tbl_Mcastudents “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Mcastudents values ( 'Chitta''Chennai','Male')  
  2. Insert into tbl_Mcastudents values ( 'Saravanan''Chennai''Male')  
  3. Insert into tbl_Mcastudents values ( 'Chandin''BBSR''Female')  
  4. Insert into tbl_Mcastudents values ( 'Mama''Puri''Female')  
  5. Insert into tbl_Mcastudents values ( 'Ram''Pune''Male')  
  6. Insert into tbl_Mcastudents values ( 'Mitu''Delhi''Male')  
Execute the above query. You should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from the “tbl_Mcastudents” table.
  1. select * from tbl_Mcastudents  
Output
 
Difference Between Union and Union All in SQL Server 2017
 
Step 3 - Create the second table
 
Open your SQL Server and use the following script to create the table “tbl_Mbastudents”.
  1. create table tbl_Mbastudents  
  2. (  
  3.    Id int primary key not null identity(1,1),  
  4.    Name nvarchar(50),  
  5.    Location nvarchar(30),  
  6.    Gender varchar(10)  
  7. )  
Execute the above query to create “tbl_Mbastudents “.
 
You should see a message, “Command(s) completed successfully.”
 
Now, data is inserted into the table.
  1. Insert into tbl_Mbastudents values ( 'Chitta''Chennai','Male')  
  2. Insert into tbl_Mbastudents values ( 'Nabin''Puri''Male')  
  3. Insert into tbl_Mbastudents values ( 'Jeni''BBSR''Female')  
  4. Insert into tbl_Mbastudents values ( 'Mama''Puri''Female')  
  5. Insert into tbl_Mbastudents values ( 'Jitu''Berhampur''Male')  
  6. Insert into tbl_Mbastudents values ( 'Niru''Delhi''Female')  
Execute the above query, you should see a message, “Command(s) completed successfully.”
 
Now retrieve all data from the “tbl_Mbastudents” table.
  1. select * from tbl_Mbastudents  
Output
 
Difference Between Union and Union All in SQL Server 2017
 

The purpose or use of UNION in SQL Server

 
It contains the result-set of two or more select queries into a single result set. It removes duplicate rows in the result-set. The number of columns, Data types, and the order of the columns in the select statements should be the same when using the UNION operator.
 
Syntax
  1. Select ColumnList from Table1    
UNION
  1. Select ColumnList from Table2   
UNION Query
  1. Select Id, Name, Location, Gender from tbl_Mcastudents  
UNION
  1. Select Id, Name, Location, Gender from tbl_Mbastudents  
Output
 
Difference Between Union and Union All in SQL Server 2017
 

The purpose or use of UNION ALL in SQL Server

 
It contains the result-set of two or more select queries into a single result set. It includes all the rows/records in the result-set. The number of columns, Data types, and the order of the columns in the select statements should be the same when using the UNION ALL operator. 
 
Syntax
  1. Select ColumnList from Table1   
UNION ALL
  1. Select ColumnList from Table2  
UNION ALL Query
  1. Select Id, Name, Location, Gender from tbl_Mcastudents  
UNION ALL
  1. Select Id, Name, Location, Gender from tbl_Mbastudents  
Output
 
Difference Between Union and Union All in SQL Server 2017

Note
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. Otherwise, you should get a syntax error.
 

Sort in UNION ALL

 
The ORDER BY clause should be used on the last SELECT statement as shown below.
 
UNION ALL Query
  1. Select Id, Name, Location, Gender from tbl_Mcastudents  
UNION ALL
  1. Select Id, Name, Location, Gender from tbl_Mbastudents  
  2. Order by Name  
Output
 
Difference Between Union and Union All in SQL Server 2017
 

Sort in UNION

 
The ORDER BY clause should be used on the last SELECT statement, as shown below.
 
UNION Query
  1. Select Id, Name, Location, Gender from tbl_Mcastudents  
UNION
  1. Select Id, Name, Location, Gender from tbl_Mbastudents  
  2. Order by Name  
Output
 
Difference Between Union and Union All in SQL Server 2017

Syntax error
  1. Select Id, Name, Location, Gender from tbl_Mcastudents  
  2. ORDER BY Name  
UNION ALL
  1. Select Id, Name, Location, Gender from tbl_Mbastudents  
 
Output
 
Difference Between Union and Union All in SQL Server 2017
 

Difference between UNION and UNION ALL operators

 
UNION
  • It removes duplicate rows in the result-set.
  • It is slow because when using UNION, to remove the duplicate rows in SQL, the server has to do a distinct sort, which is time-consuming.
  • It uses a distinct sort.
  • It cannot work with a column that has a text data type.
UNION ALL
  • It does not remove duplicate rows in the result-set.
  • It is much faster than Union because it does not use distinct sorting.
  • It works with all data type columns.
Note
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.
 

Difference between JOIN and UNION


JOINS
 
It is used to retrieve data from two or more tables based on logical relationships between the tables. JOINS combine columns from 2 or more tables.
 
UNIONS
 
It combines 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. Or UNION combines rows from 2 or more tables.
 

Conclusion

 
In this article, I explained the UNION and UNION ALL operators in SQL Server with some examples. I hope this article has helped you to understand this topic. Post your valuable feedback in the comments section below!