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 (https://blog.sqlauthority.com)
190 Comments. Leave new
Very good for understanding difference between UNION Vs UNION ALL
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
Union:1,2,3,4…
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………..
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!).
declare @t1 table(i int)
insert into @t1 select 1 union all select 2 union all select 2 union all select 3
declare @t2 table(i int)
insert into @t2 select 3 union select 1 union select 4 union select 5
select * from @t1 as t
where exists(
select i from @t1
where i=t.i
group by i having count(*)>1
)
union all
(
select * from @t1 as t
where exists(
select i from @t1
where i=t.i
group by i having count(*)=1
)
union
select i from @t2
)
always union all have the better performance.
Hi all,
I want to ask if i can use union ineasted of join,please show examples
No. Both have different goals
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
Great post…. very easy to understand….keep up the work…
Hi all,
How to eliminate the duplicate rows using union all functions.
Why are you using union all when union does what you need?
Hi,
Really it was help full for me.
Thnx
Its really good article to understand… the diff. between both UNION and UNION ALL..:)
Hi,
Its realy helpful for me.
Thanks
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
hi pinaldave, this is very useful informaton for us
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…
You can write this
create procedure temp
AS
BEGIN
sql statement1 where condition1
union all
sql statement2 where condition2
END
hi
super explaination i understand very clearly
EXCELLENT THANKS VERY MUCH
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.
create procedure temp
AS
BEGIN
sql statement1 where condition1
union all
sql statement2 where condition2
END
Great!!!! Keep It Up!!!
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………