Click here to Skip to main content
11,637,662 members (77,743 online)
Click here to Skip to main content

Tagged as

SQL Server – What exactly are UNION and UNION All?

, 26 Jan 2014 CPOL 50.9K 4
Rate this:
Please Sign up or sign in to vote.
CodeProject In the last blog post, we have discussed about COALESCE function in SQL Server. You can read that article here. In this article we will go over UNION and UNION ALL  Operators in SQL Server. In this article, we will discuss about following: Purpose of UNION and UNION ALL. Differences betw

In the last blog post, we have discussed about COALESCE function in SQL Server. You can read that article here. In this article we will go over UNION and UNION ALL  Operators in SQL Server.

In this article, we will discuss about following:

  • Purpose of UNION and UNION ALL.
  • Differences between UNION and UNION ALL.
  • Differences between JOIN and UNION.

UNION and UNION ALL operators in SQL Server are used to combine the result sets of two or more SELECT queries.

Let’s understand what is meant by this with an example. We have 2 tables here, tblIndiaCustomers and tblUSCustomers. Both of these tables have identical columns – Id, Name and Email.

0.Customers

Now if we issue a select query against these 2 tables and execute them, we will get 2 separate result sets. We will get 2 rows each from tblIndiaCustomers and tblUSCustomers.

1

Now we want to combine both of these result sets into one result set. How do we do that? We can use UNION or UNION ALL Operators. Let’s first use UNION ALL and see what will happen. When we execute the query, it is going to combine the rows from tblndiaCustomers with rows from tbUSCustomers. So while using UNION ALL, we are combining the result sets from these two queries. Here in output, we will get 4 rows.

2

Then instead of using UNION ALL, just use UNION and see what will happen. Now we will get only 3 rows. While looking at the output, we can see that the the duplicate record – Name: Thomas, Email: T@T.Com is eliminated. While using UNION ALL, we will get all the rows including duplicates. While using UNION Operator, the output is sorted as well.

3

What are the differences between UNION and UNION ALL Operators?

  1. UNION removes duplicate rows, whereas UNION ALL doesn’t.
  2. UNION have to perform distinct sort to remove duplicates, which makes it less faster than UNION ALL.

The distinct sort operation happening in UNION is time consuming. If you want to see that, you can turn on the Estimated Execution Plan in SQL Server Management Studio(SSMS). To do that, you can either press CTRL+L or you can click on Display Estimated Execution Plan icon in SSMS.

4

When we use UNION and click on this icon, we can see that the distinct sort is actually taking 63% of time.

5

On the other hand, when we use UNION ALL and click on Display Estimated Execution Plan, there is no distinct sort at all.

6

So for this reason, UNION is a little bit slower than UNION ALL

Other important point to ponder is that for UNION and UNION ALL to work, the Number, Data types and the Order of the columns in the SELECT statements should be same. This makes sense as well. For example, write the query like below.

SELECT Id, Name From tblIndiaCustomers

UNION

SELECT Id, Name, Email From tblUSCustomers

In the above query, the first SELECT statement is giving 2 columns and the second SELECT statement is giving 3 columns. How can we combine them?! We can’t do that. So while executing the query, we will get an error.

7

It is not enough to have equal number of columns, but they should be in the right order as well. If we write query like below, it doesn’t make any sense.

SELECT Name, Email, Id From tblIndiaCustomers

UNION

SELECT Id, Name, Email From tblUSCustomers

How can we combine Id with Email?! So while executing the query, it is trying to convert Name to integer and fails.

8

So while using UNION and UNION ALL Operators, the number of columns have to be same, data types have to be same and they have to be in same order.

Sorting the results of UNION and UNION ALL

  • ORDER BY clause should be used only on the last SELECT statement in the UNION query.

10

While executing the above query, we will get the results sorted by Name. If we use ORDER BY clause in the first query, let’s see what is going to happen. While executing the query, it will give an error stating Incorrect syntax near the keyword ‘UNION’.

11

This makes sense as well. How does UNION ALL work? It will take the rows from first table and combine them with the rows from second table. It doesn’t make sense to take sorted rows from first table and combine. When they are combined, the result will again become unsorted. That’s why, after getting all the results, sort them using ORDER BY clause. So ORDER BY should be with the last SELECT statement.

Differences between JOIN and UNION

  • UNION 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, where as JOIN retrieves data from two or more tables based on logical relationships between the tables.
  • In short, UNION combines rows from 2 or more tables, where JOIN combines columns from 2 or more tables.

Reference: Arun Ramachandran (http://BestTEchnologyBlog.Com)


License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Arun Ramachandran India
Software Developer
India India
Arun Ramachandran is a Software Engineer having hands on experience in different Microsoft Technologies who is presently working in Experion Technologies, India. He has written over 95 articles on the subject on his blog at http://BestTEchnologyBlog.com. Along with 3 years of hands on experience he holds a Master of Computer Applications degree from Cochin University of Science & Technology (CUSAT).

You may also be interested in...

Comments and Discussions

 
QuestionThanks lot Pin
anil waditake21-Dec-14 2:29
memberanil waditake21-Dec-14 2:29 
QuestionThanks! Pin
karthik_bv216-Nov-14 21:06
memberkarthik_bv216-Nov-14 21:06 
GeneralMy vote of 5 Pin
Animesh Datta7-Aug-14 23:36
memberAnimesh Datta7-Aug-14 23:36 
GeneralMy vote of 5 Pin
Sibeesh Venu4-Aug-14 0:41
professionalSibeesh Venu4-Aug-14 0:41 
Questionvery good explanation with screens Pin
binabic27-Jan-14 4:20
memberbinabic27-Jan-14 4:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.150728.1 | Last Updated 27 Jan 2014
Article Copyright 2014 by Arun Ramachandran India
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid