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 have two table with a lot of record. Now I want get a view
So
Case 1:
Select …
From table1
where….
UNION
Select…
From table2
where…
Case 2:
Select …
From table1
where….
UNION ALL
Select…
From table2
where…
Case3:
declare @tmpTable table(…)
Go
Insert into @tmpTable sSelect … From table1 where….
Go
Insert into @tmpTable sSelect … From table2 where….
Go
select * from @tmpTable
drop @tmpTable
Please help me! Which case is the best performance?
I must say thanks for this article, which confirms how I *thought* the UNION keyword was working. I feel guilty if I just leech knowledge from this page!
100 palkalai 30000
101 raja 5000
102 kumar 4900
103 murali 21000
104 sugumar 13000
105 gugan 8940
106 gobi 56000
107 dhilip 34000
I have records like this in my notepad or word. how can i insert all those records in SQL. send me the query..
Use Bulk insert
@palkalaiselvan
use bulk insert to insert text file in sql
CREATE TABLE [dbo].[empdata](
[empid] [int] NOT NULL,
[empname] [nvarchar](50) NULL,
[empsalary] [int] NULL
) ON [PRIMARY]
BULK INSERT empdata FROM ‘G:\notepad.txt’ WITH
(
FIELDTERMINATOR = ‘\t’, –Tab
ROWTERMINATOR =’\n’–New Row
)
select * from empdata
Really good article…Understood the difference between both
use of savepoint
can u explain savepoint in sq1 server 2008
I CAN’T BELIEVE THAT USING “UNION ALL” BOOSTED MY QUERY AT A 500 % FASTER THAN “UNION”
THANK YOU VERY MUCH BROTHER.
Once again you’ve saved the day for me. Mr. Pinal Dave, you’re columns have taught me more about SQL than ten books on the subject. Thank you!
Good example for Unions.I have one union with 2 select statements,Does SQL consider it as one statement /2 statements?
Krishna
Today i got different scenario where union is working better than union ALL.
One of the point astonished me while using UNION it is returning results in 20 sec where as it is taking more than 10 min
PBillno PurDate Partyname Tax SBillno SalDate Partyname Tax
————————————————————————————————-
1 2011-09-30 Raji 14 1 2011-09-30 Test 0
2 2011-09-30 Testing 55.57 4 2011-10-10 Rajesh 1.60
4 2011-10-10 Kumar 0.00 5 2011-10-10 Guna 29.40
5 2011-10-10 Suji 0.00 6 2011-09-10 Ragu 0.80
—————————————————————————————————
This is My Table …
i Have PurDate Same to SalDate.. If Not Enough Then that SalDate Record Null Row..
How do.. Please Help Me..
You need to post expected result from the above sample data. Also explain the logic
hello everybody..
please assist me how to get only duplicate records from a table1 into a new table2..
thanks in advance sir..please guide me
Shivendra
This will get you to find out duplicates
Select col from table
group by col
having count(*)>!
Thanks dear madhivanan …
hi
dbo.T_Products
ID Product name
1 CAD
2 PTC
3 windchill
4 desk
dbo.T_Users
FK_Products Region
1 Asia
1 Asia
1 Europe
1 America
2 Asia
2 Asia
3 Europe
3 Europe
Output:
Product Name Asia count Europe count America count
CAD 2 1 1
PTC 2 0 0
Windchill 0 2 0
ID and FK_Products are as primary and foreign key….
plz give me a query
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!