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
1,80,0,”
1,0,0,’a’
then how can i get it in one line..
What did you mean by one line? Did you mean single row with 8 columns?
Is there a limit on the number of UNIONS that you can do in a single query ?
How sixth appears twice in the result of UNION ALL.
Thanks for the blog. Keep up the work.
thank u
Hi All –
thanks for the explanantion about the UNION and UNION ALL
can any one help on this
———————————————————————
SELECT ‘My 1 Row123’
UNION
SELECT ‘My 2 Row123’
UNION
SELECT ‘My 12 Row123’
UNION
SELECT ‘My 13 Row123’
UNION
SELECT ‘My 122 Row123’
———————————————————————
SELECT ‘My 1 Row123’
UNION ALL
SELECT ‘My 2 Row123’
UNION ALL
SELECT ‘My 12 Row123’
UNION ALL
SELECT ‘My 13 Row123’
UNION ALL
SELECT ‘My 122 Row123’
——————————————————————–
when I use UNION, the result set is sorted by the column and not with UNION ALL..
Could anyone explain the reason for this?
Myself I got the answer for this :).. when we use UNION, SQL CLR will performs the sorting on the rowset and then takes the distinct, where as UNION ALL won’t perform this operation. see below link for more information:
Thanks,
Buddha
It is because with Union, Only distinct values are selected. Since the result set returns distinct values, Distinct Sort is performed in the back end. Vs. Union All does not eliminate duplicate rows and returns all the rows from all the tables. so you won’t see sorted result.
hi sir ,
i have a problem ,,i have one table field it has asset like(row wise car,computer) and other table field record is (id of these asset like 1,2,3)
then how to join these tables,, please reply me soon
i am pretty confused about primary key and foreign key i need a kindly reply about dat!
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