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
I am beginer in sql.. i have a problem..i will try combine two queries in sql using UNION..the result is fine…but my problem like
First table contain 2,5
second table contain 1,6
Final Result 1,2,5,6
but i want 2,5,1,6..
Anyway to get like that….?
please help me
Thanks advance
select row_number() over (order by (select 0)) as sno, col from table1
union
select row_number() over (order by (select 0)) as sno, col from table2
order by sno
I am doing a union of 3 complex queries with multiple joins (inner and left outer). Strangely UNION takes 22 sec while UNION ALL takes 24 sec
Hi,
I would like to union the two tables which are stored at different databases.Suppose the table A is stored in Database A and the table B is stored in Database B.How to write the query to unioj n these tables?
Can anyone help me?
Hi Pinal,
Can you please explain how
if that all the records returned are unique from your union, UNION ALL gives faster results?
Thanks
Faisal
If you use UNION ALL, it does not have to search for duplicates, it just pastes he second table below the first.
If you are sure there are no duplicates, using UNION is useless and therefor less efficient, since it will be searching for duplicates to delete that are not there. It will find nothing and ‘afterwards’ do exactly what UNION ALL would have done already.
Kind regards,
Daan
Very simply, the UNION of two or more datasets/tables is a result set containing all of the rows from all tables with all duplicate rows , if any, removed.
The UNION ALL of two or more datasets/tables is a result set containing all rows from all tables including ALL duplicates, if any.
Thank you, perfect explanation !
It should be noted that union and union all can not be used to combine sequence values. Refer this post for more information
Hi Pinal,
Is there any alternate wy to avoid Union or union all.
I have 2 columns in table like below
col1 col2
1 2
3 4
5 6
and i want result as below without union/ union all
col1
1
2
3
4
5
6
Please advice !! Thanks
Discuss null value with join, union with example
Hi kathan ,
create table samp (no1 int , no2 int)
insert into samp values (1,2)
insert into samp values (3,4)
insert into samp values (5,6)
select * from samp
— code :
declare @a int
declare @b int
declare cur cursor
static for select * from samp
open cur
if @@CURSOR_ROWS > 0
begin
declare @tab table (col int)
fetch next from cur into @a , @b
while (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @tab values(@a),(@b)
fetch next from cur into @a , @b
END
end
select * from @tab
close cur
deallocate cur