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
Hi – thanks for this – very useful and concise – I haven’t come across a better comparisson that’s been this easy to follow
Hi- thanks – its very use full for me
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.
@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
Oh,… How great these less lines explains..
Really great work…
In the Result Set shown above for the UNION ALL result, the second Sixth will not come.
Hi Pinal,
I have 2 columns Project date and trench. I want to display the date as range if the trench value is same. For eg:
Project date have three values 10-12-2009,11-12-2009,12-12-2009 and all the three have same trench value 1.
Now my requirement is i need to show them as
10-12 Dec 2009.
I am trying with Union so far not successful.
Can someone tell me how to achieve this.
@Ranjani
To get the range, use a GROUP BY with MIN() and MAX()
The date would be interesting, because it may be multiple months or even years. Given the example provided (the three records) this should work:
WITH
Data([Project date], trench)
AS
(
SELECT ’10-12-2009′, 1 UNION ALL
SELECT ’11-12-2009′, 1 UNION ALL
SELECT ’12-12-2009′, 1
)
SELECT
CAST(DAY(MIN([Project date])) AS VARCHAR(2))
+ ‘-‘
+ CAST(DAY(MAX([Project date])) AS VARCHAR(2))
+ ‘ ‘
+ LEFT(DATENAME(m, MAX([Project date])), 3)
+ ‘ ‘
+ CAST(YEAR(MAX([Project date])) AS VARCHAR(4))
FROM
Data
GROUP BY
trench;
Note the date format will depend on what it is set to. For the US, all three dates are the 12th, but the month changes. Which is not what you intended.
Hi,
This have help me a lot.
Regards,
Pady.
Hi all,
Is it possible to use if …else condition with union all… its throwing an exception Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword ‘if’.
Sample query
if(@1stHearingdate is not NULL)
BEGIN
Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,’Remarks’
from
Hearing
where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@1stHearingdate,101)
group by convert(varchar(100),Hrgdatetime,101)
END
else
Select 1 as ‘Week No’,convert(varchar(100),@1stHearingdate,101),0,’Remarks’
union all
if(@2ndHearingdate is not NULL)
BEGIN
Select 1 as ‘Week No’, convert(varchar(100),Hrgdatetime,101) as ‘HearingDate’, count(Hrgid) ‘No of hearing’,’Remarks’
from
Hearing
where convert(varchar(100),Hrgdatetime,101) = convert(varchar(100),@2ndHearingdate,101)
group by convert(varchar(100),Hrgdatetime,101)
END
else
Select 1 as ‘Week No’,convert(varchar(100),@2ndHearingdate,101),0,’Remarks’
Thanks a lot
If you Can represent it with a nice graph people can understand easily
Hi,
Best answer thanx a lot man.
Regards,
Mr. Rajesh Mhaisne
Software Engineer | Pune
The word “IMPOSSIBLE” Says ‘I M’ “POSSIBLE”.
Hi!
i am using UNION in my program where i want that union shoud not auto arrange the order since its default order is imp for me.
ex table1
area 1.5
table 2
area 1.2
table 3
area 2.3
after union of these result comes like
area 1.2
area 1.5
area 2.3
i want i should come like
area 1.5
area 1.2
area 2.3
what should exact querry.
pls help me.
You should use union all
@Raj Kumar Rai
There is no guaranteed order without an ORDER BY clause in the statement. Though, you should be able to provide an ORDER BY that satisfies your requirements.
There is a behaviour change in this case
In 2000, it does order by whereas from version 2005 onwards it does happen
thanks to sugest me.I had hope for response but it will surly ,you have proved it.
Can we use Where condtion after Union or Union all statements, Like as below. Coz i need to query some thin like below. PLease help me in this.
create table table1
(
id1 int,
name1 varchar(50)
)
go
insert into table1 values(1,’venu’)
insert into table1 values(2,’gopal’)
insert into table1 values(3,’hari’)
insert into table1 values(4,’prasad’)
insert into table1 values(5,’balu’)
go
create table table2
(
id2 int,
name2 varchar(50)
)
go
insert into table1 values(1,’Gopal’)
insert into table1 values(20,’fadfl’)
insert into table1 values(3,’Prasad’)
insert into table1 values(40,’asdfasd’)
insert into table1 values(50,’dfsd’)
create view myview
as
select name1 from table1
union all
select name2 from table2
where table1.id1 = table2.id2
You need to use JOIN instead of UNION
hi!
pls can you show me how to query in two table that are not match with each other?
sampl data:
table 1—–1,2,4,6,7
table 2—–2,3,4,5,8
will return—-1,3,5,6,7,8
pls..pls..pls….
Hi,
Please find solution for your query:
DECLARE @T1 TABLE(i INT)
INSERT INTO @T1 VALUES(1)
INSERT INTO @T1 VALUES(2)
INSERT INTO @T1 VALUES(4)
INSERT INTO @T1 VALUES(6)
INSERT INTO @T1 VALUES(7)
DECLARE @T2 TABLE(j INT)
INSERT INTO @T2 VALUES(2)
INSERT INTO @T2 VALUES(3)
INSERT INTO @T2 VALUES(4)
INSERT INTO @T2 VALUES(5)
INSERT INTO @T2 VALUES(8)
;with CTE AS(
SELECT i
FROM @T1
UNION
SELECT j AS i
FROM @T2
)
SELECT *
FROM Cte
WHERE NOT EXISTS(
SELECT *
FROM @T1 t1
INNER JOIN @T2 t2 ON i=j
WHERE t1.i= CTE.i
)
ORDER BY i
Let me know if you any questions.
Thanks,
Tejas
we can also do this with
select distinct * from @T1 where i not in (select distinct * from @T2 ) union
select distinct * from @T2 where j not in (select distinct * from @T1 ) order by 1 desc
select * from @T1 where i not in (select distinct * from @T2 ) union
select * from @T2 where j not in (select distinct * from @T1 )
Hi Tejas,
thank you…works fine for me!
ID Username ModifiedDate
1 A.BEENA 2009-12-07 04:48:17.980
2 A.BEENA 2009-11-17 06:02:27.443
3 Abhilash 2009-12-07 04:48:17.980
4 abhilash.r 2009-12-07 04:48:17.980
5 AJI 2009-11-17 06:02:27.443
6 haris 2009-12-07 04:48:17.980
7 haris 2009-11-17 06:02:27.443
I want to select details of all distinct users order by ModifiedDate.
I need output like this
A.BEENA 2009-12-07 04:48:17.980
Abhilash 2009-12-07 04:48:17.980
abhilash.r 2009-12-07 04:48:17.980
AJI 2009-11-17 06:02:27.443
haris 2009-12-07 04:48:17.980
Please help me
Thanks,
Aneesh
@Aneesh Daniel
How’s this?
WITH
Data(ID, Username, ModifiedDate)
AS
(
SELECT 1, ‘A.BEENA’, ‘2009-12-07 04:48:17.980’ UNION ALL
SELECT 2, ‘A.BEENA’, ‘2009-11-17 06:02:27.443’ UNION ALL
SELECT 3, ‘Abhilash’, ‘2009-12-07 04:48:17.980’ UNION ALL
SELECT 4, ‘abhilash.r’, ‘2009-12-07 04:48:17.980’ UNION ALL
SELECT 5, ‘AJI’, ‘2009-11-17 06:02:27.443’ UNION ALL
SELECT 6, ‘haris’, ‘2009-12-07 04:48:17.980’ UNION ALL
SELECT 7, ‘haris’, ‘2009-11-17 06:02:27.443’
)
SELECT
Username,
MAX(ModifiedDate)
FROM
Data
GROUP BY
Username;