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.

• Joins In SQL Server 2017
• Self Join In SQL Server 2017
• Advanced Joins In SQL Server 2017
• Joining Three Or More Tables In SQL Server 2017

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 a Database and Two Tables

Step 1. Create a Database

Open your SQL Server and use the following script to create the “chittadb” Database.

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 the first table

Open your SQL Server and use the following script to create the table “tbl_Mcastudents”.

CREATE TABLE tbl_Mcastudents
(
    Id int primary key not null identity(1,1),
    Name nvarchar(50),
    Location nvarchar(30),
    Gender varchar(10)
)

Execute the above query to create “tbl_Mcastudents “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

Insert into tbl_Mcastudents values ('Chitta', 'Chennai','Male')
Insert into tbl_Mcastudents values ('Saravanan', 'Chennai', 'Male')
Insert into tbl_Mcastudents values ('Chandin', 'BBSR', 'Female')
Insert into tbl_Mcastudents values ('Mama', 'Puri', 'Female')
Insert into tbl_Mcastudents values ('Ram', 'Pune', 'Male')
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.

select * from tbl_Mcastudents

Output

Step 3. Create the second table.

Open your SQL Server and use the following script to create the table “tbl_Mbastudents”.

create table tbl_Mbastudents
(
   Id int primary key not null identity(1,1),
   Name nvarchar(50),
   Location nvarchar(30),
   Gender varchar(10)
)

Execute the above query to create “tbl_Mbastudents “.

You should see a message, “Command(s) completed successfully.”

Now, data is inserted into the table.

Insert into tbl_Mbastudents values ( 'Chitta', 'Chennai','Male')
Insert into tbl_Mbastudents values ( 'Nabin', 'Puri', 'Male')
Insert into tbl_Mbastudents values ( 'Jeni', 'BBSR', 'Female')
Insert into tbl_Mbastudents values ( 'Mama', 'Puri', 'Female')
Insert into tbl_Mbastudents values ( 'Jitu', 'Berhampur', 'Male')
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.

SELECT * FROM tbl_Mbastudents

Output

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

Select ColumnList from Table1

UNION

SELECT ColumnList FROM Table2

UNION Query

Select Id, Name, Location, Gender from tbl_Mcastudents

UNION

Select Id, Name, Location, Gender from tbl_Mbastudents

Output

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

Select ColumnList from Table1

UNION ALL

Select ColumnList from Table2

UNION ALL Query

Select Id, Name, Location, Gender from tbl_Mcastudents

UNION ALL

Select Id, Name, Location, Gender from tbl_Mbastudents

Output

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

SELECT Id, Name, Location, Gender FROM tbl_Mcastudents

UNION ALL

Select Id, Name, Location, Gender from tbl_Mbastudents
Order by Name

Output

Sort in UNION

The ORDER BY clause should be used on the last SELECT statement, as shown below.

UNION Query

Select Id, Name, Location, Gender from tbl_Mcastudents

UNION

Select Id, Name, Location, Gender from tbl_Mbastudents
Order by Name

Output

Syntax error

SELECT Id, Name, Location, Gender FROM tbl_Mcastudents
ORDER BY Name

UNION ALL

SELECT Id, Name, Location, Gender FROM tbl_Mbastudents

Output

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 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. 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!