SQL SERVER – Union vs. Union All – Which is better for performance?

March 10, 2007 by pinaldave

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article.

UNION
The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type. With UNION, only distinct values are selected.

UNION ALL
The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table.

A UNION statement effectively does a SELECT DISTINCT on the results set. If you know that all the records returned are unique from your union, use UNION ALL instead, it gives faster results.

Example:
Table 1 : First,Second,Third,Fourth,Fifth
Table 2 : First,Second,Fifth,Sixth

Result Set:
UNION: First,Second,Third,Fourth,Fifth,Sixth (This will remove duplicate values)
UNION ALL: First,First,Second,Second,Third,Fourth,Fifth,Fifth,Sixth,Sixth (This will repeat values)

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Download, SQL Optimization, SQL Performance, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 78 Comments

78 Responses

on March 23, 2007 at 11:48 pm Sanjib Kumar Maji

Very good for understanding difference between UNION Vs UNION ALL
on April 18, 2007 at 6:59 am Sham

Union command does 2 things
1) It works like distinct clause ie it removes duplicate rows also it shows the rows which are unique in a particular table.
eg if Table A has rows with values 1,2,3 and Table B has rows with values 4,2,3
the Union table A and table B would give result
1,2,3,4
on April 18, 2007 at 7:01 am pinaldave

That is Correct Sham. Union works like Distinct. Union all DOES NOT do distinct.
on May 8, 2007 at 10:46 pm prashant

this is really a good article to understand but can u give this example with a example. This will be the good for all the people to understand the concept very clearly.

Thanks………..
on May 8, 2007 at 10:48 pm pinaldave

If you have two tables.
First table : 1,2,3,4,5
Second table : 1,2,5,6

Union All:
1,2,2,3,4,5,5,6

Union:
1,2,3,4,5,6

I hope that clears a bit. I will re-write the articles soon.
Thanks for your question.

Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on May 25, 2007 at 3:37 pm artesvida

Another example, which this article helped me troubleshoot today:

First Table: 1,2,2,3
Second Table: 4,5

Union All:
1,2,2,3,4,5

Union:
1,2,3,4,5

I suppose I understood that union would remove duplicates between the two tables, but I didn’t realize that it would also remove the EXISTING duplicates (which I wanted to keep!).
on May 25, 2007 at 3:54 pm pinaldave

Hello artesvida,

I am glad that it was useful.

Kind Regards,
Pinal Dave (http://www.SQLAuthority.com)
on May 31, 2007 at 5:36 am neeraj

always union all have the better performance.
on June 10, 2007 at 3:36 pm husseiny

Hi all,
I want to ask if i can use union ineasted of join,please show examples
on June 12, 2007 at 3:46 pm Wilson Gunanithi . J

Hi Pin ,

With the help of your statement about UNION and UNION ALL , I ve learnt interesting matters of difference between them .

Thanks a lot.

Regards,

Wilson Gunanithi . J
on July 2, 2007 at 9:48 pm vivek

Great post…. very easy to understand….keep up the work…
on July 19, 2007 at 2:57 am Michael

Hi all,

How to eliminate the duplicate rows using union all functions.
on September 16, 2007 at 5:17 am Randhir Singh

Hi,
Really it was help full for me.

Thnx
on September 17, 2007 at 11:14 pm Prashant Pandey

Its really good article to understand… the diff. between both UNION and UNION ALL..:)
on September 18, 2007 at 2:49 am Pavan

Hi,

Its realy helpful for me.

Thanks
on September 24, 2007 at 3:20 am Ravi Ranjan

Very good for understanding difference between UNION Vs UNION ALL.
I work as a software Engineer in CFCS. I develop more than 10 web site through ASP and SQL.
Ravi Ranjan
on September 26, 2007 at 12:25 am Mohammed Siddiq.K

hi pinaldave, this is very useful informaton for us
on October 10, 2007 at 11:09 pm Suresh

Good explanation….

I have got one problem… I would appreciate if any one help me out solve the problem…

Here is my requirement. I have to write a store procedure which returns a table like below.

create procedure temp
AS
BEGIN

if(condition1)
BEGIN
sql statement1
END

if(condition2)
BEGIN
sql statement2
END

END

Now i have to combined these two tables and have to return. How can i use UNION or UNION all here in this case???

I hope the requirement is clear…
on October 15, 2007 at 11:06 pm suresh

hi

super explaination i understand very clearly
on October 17, 2007 at 1:29 am sarah

EXCELLENT THANKS VERY MUCH
on October 23, 2007 at 8:02 am Andy

Hi Suresh,

For your example of having to effectively union two statements that are nested in different IF statements, you may want to use a temp table / table variable.

if(condition1)
BEGIN
INSERT INTO #MyTable
SELECT * FROM Table1
END

if(condition2)
INSERT INTO #MyTable
SELECT * FROM Table2
END

SELECT * FROM #MyTable

In the above example, you would need to explicitly decide if you wanted to do a simple SELECT (UNION ALL) or SELECT DISTINCT (UNION).

Also, you should carefully choose to use a temp table (#tmp) or table variable (@tmp). There are a number of differences, but basically, variable are better for small resultsets and temp tables better for large. When using a variable table, you lose all parallelism, so inserting into the table can take significantly longer.
on January 8, 2008 at 10:31 am hemant inamdar

Great!!!! Keep It Up!!!
on February 15, 2008 at 5:30 pm Vaibhav

I would like to work with u….

If u can recognize me i am vaibhav pathak…….

I like ur article for difference beteen Union and Union All

You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2005………
on February 15, 2008 at 6:25 pm Vaibhav

can u tell me what is ’sysobject’ in Sql Server 2005 ??

Regards Vaibhav
on February 15, 2008 at 6:38 pm Vaibhav

I would like to work with u…. If u allow !!!!!!!!

If u can recognize me i am vaibhav pathak……. I have acd interview taken by u.

I like ur article for difference beteen Union and Union All

You have selected me but the boss of ur company had no faith on me thats why i did not come to ur company on 15th jan 2008………
on March 17, 2008 at 3:29 pm adhi

dear all,
union all is always best performance
on March 18, 2008 at 11:10 am shailesh

thx its good
on March 18, 2008 at 4:11 pm adhi

Dear Pinal Dave,

Pls send me ms sqldba certification question & answere

Reagrds
Adhi
on March 24, 2008 at 11:20 pm SQL SERVER - Simple Puzzle Using Union and Union All Journey to SQL Authority with Pinal Dave

[...] Hint : This puzzle is based on my previous article SQL SERVER – Union vs. Union All – Which is better for performance? [...]
on March 25, 2008 at 12:55 pm Chella Ganesh

Nice article. Keep it up Pinal
on March 26, 2008 at 8:22 pm Virendra Bhati

I read many question from that site.
Every one was good and very near to the target.
So I really love that site and now always go through that.
on March 27, 2008 at 7:54 pm Nila F

Hello,

I have a challange here Im hoping someone could help… I got two tables with identical schema that need to be “glued” together, but the duplicate records must be truncated under certain condiction:

For example, if the PK field is identical in both tables, I would want the actDate to be evaluated and the one with higher value to be selected during the UNION process… how can I accomplish this?

Thanks a whole bunch in advance!!
on March 28, 2008 at 11:33 am sree hari p v

hi Pinal
Good article
i have a doubt
what if if the table contains a Null value
can u give example in this regard

With thanks
sree
on March 28, 2008 at 8:53 pm Mario Gordon

If I have use a UNION ALL with two selects why does the order change instead of returning the results in natural order? For example if I have SELECT 0 As SortOrder FROM XXX UNION ALL SELECT 1 As SortOrder FROM AAA the 1 will be the first record instead of the second (some times)? If I add a Sort Order to the entire SQL Statement then my query slows down to much since it is buried in a bunch of other queries.
on May 4, 2008 at 3:10 pm Amit Mhatre

Hi Pinal,

The article of Union v/s Union ALL was fantastic, it clears the doubts of difference between UNION AND UNION ALL.

My only qus is : Does Union ALL also shows repeated columns (duplicates) where the datatype is common, which is the case in UNION clause.
on May 14, 2008 at 7:41 am Zainab

Hi Pinal,

I am a beginer in SQL and have a query and finding difficult solving this. Can you please help me.

[Northwind] List the CompanyName, ContactName, City, Region, PostalCode, Country, and Phone of all Customers who have ordered product ‘Tofu’ and of the Supplier of that product. Use UNION. Show all output rows.

Thanks
Zainab
on June 11, 2008 at 11:08 am Amit Mhatre

Thanks Pinal,

Union and Union All descreption in your words is very straight-forward and easy to understand.
on July 1, 2008 at 4:34 pm Muhammad Wasim

pinaldave,

Is the any difference in UNION and UNION ALL sorting order?
As in case of UNION SQL Server build temporay indexes on all the columns.

Thanks
Muhammad Wasim
on July 1, 2008 at 4:35 pm Muhammad Wasim

correction!!
As in case of UNION SQL Server build temporay index (not indexes) on all the columns.
on July 2, 2008 at 1:46 pm Sanjay Sharma

Nice article to understand the difference b/w UNION and UNION ALL.

Keep it up :)
on July 7, 2008 at 7:27 pm soutik dutta

Very comprehensive stuff. Great.
on July 9, 2008 at 11:15 am amit

hi ,
its reakky a good article . well i wana know how can i import data from excel sheet into 2005 database.pls do reply

thanks
amit sharma
on July 9, 2008 at 3:01 pm Rupesh

Hi amit,

you can import the Excel Sheet data using the Sql Server Import and Export Command. Below is the steps for the same:
1. Right click on the Database Select Tasks-> Import Data…
2. Select the datasource as ‘Microfost Excel’
3. Browse the location of the Excel File.
4. if you want to Import into the already existing table then you can Map the columns otherwise you can import into the new tables too.

Its is very simple wizard.

Please do let me know if you still face any issue.

Rupesh
on July 10, 2008 at 9:51 am amit

hi Rupesh,

thanks for urs reply, well rupesh i am still getting error while importing the excel file.

error:
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Source – Assessment$” (1).
(SQL Server Import and Export Wizard)

may i know wht is this error and what i have to do for resolving this error

thanks
amit sharma
on July 13, 2008 at 2:43 pm Shihab

nice example
Thanks.
on August 6, 2008 at 5:23 pm manish

hello Sir,
i have an problem with the union operator

i write a qry1

Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt)
as expirydt, max(tm.StrikePrice) as StrikePrice,
(tm.timeslot) as PutCall,sum(tc.FillQuantity) as BuyQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as BuyValue, 0 as SellQty,0 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 1 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot

and write a 2nd qry

Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt) as expirydt,
0 as BuyQty,0 as BuyValue, max(tm.StrikePrice) as StrikePrice,tm.timeslot as PutCall,sum(tc.FillQuantity) as SellQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 2 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot

i got result by individual run but when i join union then get a error this:

Error:Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

if you have any solution then please send me in given mail id manishpandey2009@gmail.com
on August 7, 2008 at 1:12 pm Poornima

Hi …
Can you please tell me how to eliminate the duplicate rows using union all functions.

Thanks !!!!
on August 20, 2008 at 1:32 am Guru

Hi,

Can this be done

select col1,col2,col3…
from
{
Qry 1
union
qry 2
union
qry 3
}
order by col2,col5….

Thanks in advance.
on August 20, 2008 at 3:06 am Sulaiman

Very much helped.Thanks.

The way it helped me is like this :

Since I want to get the stock of a given item , I had to use
UNION between tables like Purchase , Sales , Sales Return..

In case , which happens only rarely , if any 2 rows from different tables have duplicate values , UNION will remove 1 row , that would affect the stock.

In that case UNION ALL is the solution.
on August 25, 2008 at 11:55 am Arup

Input SQL query: select ‘1′ union select null union select 5

Output:
NULL
1
5

Any idea about the data type of ‘1′, because if I ‘ll give ‘a’, it throws error.

Also, why NULL comes first? What is the sort order? Even I am not able to put a column alias to imply the sort order.
on August 25, 2008 at 8:04 pm Imran Mohammed

Dear Arup,

I did not understand your question properly, you did not give complete information as how you want your output to be, good if you would have given an example.

Based on what I understood, this is what I would try,

Query1:

select 1 as Firstcolumn , null Secondcolumn , 5 ThirdColumn

Output:

FirstColumn SecondColumn ThirdColumn
1 NULL 5

Query2:
select 1 as OneColumn union all select null union all select 5

Output:

OneColumn
1
NULL
5

Hope this helps.
Imran.
on August 27, 2008 at 2:20 pm Arup

Hi Imran,

Many thanks for the reply.

Actually in my sql query i.e. select ‘1′ union select null union select 5, the out put is :

NULL
1
5

So the first select returns 1, then the second select returns ‘NULL’ and third select returns 5. Since I have not used any order by clause, hence the output may be:

1
NULL
5

I have seen that u have solved the issue by using a columnname in your Query 2, then how is it happening?

My another question is, in this output column, the data types are different, like ‘1′ is char and 5 is int. How is it possible?
on September 26, 2008 at 4:53 pm SQL SERVER - Simple Puzzle Using Union and Union All - Answer Journey to SQL Authority with Pinal Dave

[...] You can read the explanation of why this is answer read my previous article SQL SERVER – Union vs. Union All – Which is better for performance? [...]
on October 10, 2008 at 1:56 pm udhayaganesh

i have to table exalple a1 and a2

a1 table return no rows
a2 table return 10 rows

when i union two table it returns 9 row why?
but i union all two table it returns 10 row. i would not understand what functioning going on.

please if u have any idea share with me
on October 15, 2008 at 7:00 am SQL SERVER - Introduction and Example of UNION and UNION ALL Journey to SQL Authority with Pinal Dave

[...] blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER – Union vs. Union All – Which is better for performance? with examples. I request you to read my previous article first to understand what is the concept [...]
on November 12, 2008 at 12:53 pm Peter Pan

Good on you mate…. Its like nibbles:

CREATE VIEW asView (int) AS
SELECT name, age from person
UNION ALL
SELECT name, ageInYear from people
on December 15, 2008 at 12:20 pm Dharmendra

Hello Pinal ,

Can you provide me more difference between them lik 3 or 4 more difference bcoz in one interview i was asked to provide 5 differences between them.

Thanks
Dharm
on December 16, 2008 at 5:32 pm Ali

I want to askabout the diference bettween union and join
and if there an example I will appretiate that
on January 16, 2009 at 5:43 pm Vishu

The Best way to understand the functionality of Union.
on January 20, 2009 at 6:00 pm kashif shah

I came across your site the other day and i found it really useful. I am new to the world of sql and was bit confused about different join concepts. I hope you would post something regarding joins real soon or recommend some useful website.

Thanks!
KS.
on February 18, 2009 at 3:42 pm Massimiliano

Hi,

I beg you pardon, but I’ve got a similar problems on reporting services.
I usually write code using small queries collected into a union structure, such as

Select Something
Union
Select Something Else

Because of the necessity to display data in a proper order.

Now I’m in trouble because I have to use a condition statement like

IF First condition Select Statement 1.0
Else
Select Statement 2.0
Union
IF First condition Select Statement 1.1
Else
Select Statement 2.1

and so on

Each “Nucleo” If Else Select has the same structure.

Do you have some tips ti workaround or go throw this topics.

Finally, “Sono molto sorpreso dalla tua provessionalità”…I’m Very surprise of your professional exprerience and skills.

Ciao
massimiliNO
on February 20, 2009 at 3:39 pm SQL SERVER - Three Rules to Use UNION Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Union vs. Union All – Which is better for performance? SQL SERVER – Insert Multiple Records Using One Insert Statement – Use of UNION ALL [...]
on February 24, 2009 at 5:36 pm SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Union vs. Union All – Which is better for performance? [...]
on February 26, 2009 at 5:38 pm SQL SERVER - 2008 - Interview Questions and Answers - Part 8 Journey to SQL Authority with Pinal Dave

[...] The difference between Union and Union all is that Union all will not eliminate duplicate rows, instead it just pulls all rows from all tables fitting your query specifics and combines them into a table. (Read More Here) [...]
on March 4, 2009 at 4:56 am Jim B

What is the difference in processing speed of these two queries? Is one actually faster than the other???

————————————————

SELECT COL1, COL2, COL3 FROM TBL1
UNION
SELECT COL1, COL2, COL3 FROM TBL2

- — versus ———-

(notice using DISTINCT with UNION ALL)
SELECT DISTINCT COL1, COL2, COL3 FROM TBL1
UNION ALL
SELECT DISTINCT COL1, COL2, COL3 FROM TBL2

——————————————————

Note: TBL1 and TBL2 are very large and both have quite a few duplicates within each table… but each table TBL1 and TBL2 will not have anything in common. Creating a 3rd table is out of the question.

Thanks for your timely response.
Jim
on March 4, 2009 at 7:16 pm Brian Tkatch

Regardless of whether the data is actually unique, the DB still needs to check.
on March 6, 2009 at 12:28 pm Mohammad Javed

I am very Impressed with your example and statement.

I am very thankful to u.
on March 12, 2009 at 1:18 am SQL SERVER - Difference Between Union vs. Union All - Optimal Performance Comparison Journey to SQL Authority with Pinal Dave

[...] than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so [...]
on March 12, 2009 at 1:28 am pinaldave

This article is completely re-written with better example SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison. I suggest all of my readers to go here for update article.
on March 24, 2009 at 4:25 pm willie santos

I tried using the UNION ALL to combine the result of two separate queries where the columns / fields are of the same values.

The question is : Can I use th UNION all for more than two select statements wherethe columns / fields are of the same values.

Thank you
on April 2, 2009 at 11:21 am Md.Shahbaz

Your answer are satisfactory.
on April 29, 2009 at 12:19 am David

Hi – thanks for this – very useful and concise – I haven’t come across a better comparisson that’s been this easy to follow
on April 29, 2009 at 12:31 pm Senthil Kumar.B

Hi- thanks – its very use full for me
on May 6, 2009 at 7:16 am Andres

Hello!!
I have used union all for two tables and worked very well. Now how I can do it for 5 or more. I am new. Please help me.
on May 6, 2009 at 4:59 pm Brian Tkatch

@Adres

What is the problem?

SELECT … FROM T1
UNION ALL
SELECT … FROM T2
UNION ALL
SELECT … FROM T3
UNION ALL
SELECT … FROM T4
UNION ALL
SELECT … FROM T5
on May 6, 2009 at 5:36 pm pinaldave

@Brian Tkatch,

Great!
on June 12, 2009 at 11:56 am Manish Buhecha

Oh,… How great these less lines explains..

Really great work…
on June 12, 2009 at 3:55 pm Ramana

In the Result Set shown above for the UNION ALL result, the second Sixth will not come.