Complete Guide: Union all vs Union operators in SQL Server

Complete Guide: Union all vs Union operators in SQL Server
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

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 <b>Union All</b> 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.

SQL Union

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.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on "ASP.NET Core, jQuery, EF Core, SEO, jQuery, HTML" and more.

Leave a Reply

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