Complete Guide: Union all vs Union operators in SQL Server

Last Updated: June 8, 2021

SQL Server has 2 operators called Union All and Union that combine the result-set of two or more SELECT statements. The difference between Union All and Union operators is that Union All will ‘not remove the duplicate rows’ from the combined result-set, on the other hand Union operator will ‘remove the duplicate rows’ from the combined result-set.

Demo Database

To understand the difference – Union All vs Union, I will take the help of a demo database. There are 2 tables in this database which are shown below:

1. “Harward” table:

Id	Name	Age	Course
1	John Travolta	20	Computer Graphics
2	Mary Jones	21	Advanced Maths
3	Jacobs	22	Motion Physics

2. “MIT” table:

Id	Name	Age	Course
1	Mena Suvari	25	MBA
2	John Travolta	30	PHD

Note: Note: Both tables have a duplicate student by the name of ‘John Travolta’.

Now let us select the data from these 2 tables using Union and Union All operators.

SQL Union All

Run the following SQL Union All query statement to combine the results from the 2 tables (Harward & MIT).

Select Name, Age From Harward 
Union All
Select Name, Age From MIT

You will see the result which is shown below:

Name	Age	Course
John Travolta	20	Computer Graphics
Mary Jones	21	Advanced Maths
Jacobs	22	Motion Physics
Mena Suvari	25	MBA
John Travolta	30	PHD

You can clearly see that Union All presented you with all the records from the 2 tables. That is, the student named ‘John Travolta’ is shown from each of these 2 tables.

When a user forgets his/her password then he/she should be able to reset the password. Check How this feature is created – Creating Password Reset feature in ASP.NET Core Identity

SQL Union

Now run the same query with SQL Union operator.

Select Name, Age From Harward 
Union
Select Name, Age From MIT

The result in this case is:

Name	Age	Course
John Travolta	20	Computer Graphics
Mary Jones	21	Advanced Maths
Jacobs	22	Motion Physics
Mena Suvari	25	MBA

See that the SQL Union has removed the duplicate entry of the student named ‘John Travolta’, from the result-set.

Points to note

1. The ‘Union’ operator effectively does a SELECT DISTINCT on the results set. If you know that all the records are unique then use Union All instead, since it gives faster results.

2. Union & Union All can be used in place of SQL Joins as they may be faster in certain situations.

3. Inner queries can be rewritten using Union and Union All.

An interesting tutorial – How to create Number Paging in ASP.NET Core using Tag Helpers