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
can u tell me what is ‘sysobject’ in Sql Server 2005 ??
Regards Vaibhav
It is a system table that has informations about all the objects like tables, procedures, etc
I would like to work with u…. If u allow !!!!!!!!
If u can recognize me i am vaibhav pathak……. I have acd interview taken by u.
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 2008………
dear all,
union all is always best performance
thx its good
Dear Pinal Dave,
Pls send me ms sqldba certification question & answere
Reagrds
Adhi
Nice article. Keep it up Pinal
I read many question from that site.
Every one was good and very near to the target.
So I really love that site and now always go through that.
Hello,
I have a challange here Im hoping someone could help… I got two tables with identical schema that need to be “glued” together, but the duplicate records must be truncated under certain condiction:
For example, if the PK field is identical in both tables, I would want the actDate to be evaluated and the one with higher value to be selected during the UNION process… how can I accomplish this?
Thanks a whole bunch in advance!!
hi Pinal
Good article
i have a doubt
what if if the table contains a Null value
can u give example in this regard
With thanks
sree
NULL will be omitted
If I have use a UNION ALL with two selects why does the order change instead of returning the results in natural order? For example if I have SELECT 0 As SortOrder FROM XXX UNION ALL SELECT 1 As SortOrder FROM AAA the 1 will be the first record instead of the second (some times)? If I add a Sort Order to the entire SQL Statement then my query slows down to much since it is buried in a bunch of other queries.
For versions prior to 2005 this happens
For version 2005 onwards natural order is garuanteed
Hi Pinal,
The article of Union v/s Union ALL was fantastic, it clears the doubts of difference between UNION AND UNION ALL.
My only qus is : Does Union ALL also shows repeated columns (duplicates) where the datatype is common, which is the case in UNION clause.
Hi Pinal,
I am a beginer in SQL and have a query and finding difficult solving this. Can you please help me.
[Northwind] List the CompanyName, ContactName, City, Region, PostalCode, Country, and Phone of all Customers who have ordered product ‘Tofu’ and of the Supplier of that product. Use UNION. Show all output rows.
Thanks
Zainab
Thanks Pinal,
Union and Union All descreption in your words is very straight-forward and easy to understand.
pinaldave,
Is the any difference in UNION and UNION ALL sorting order?
As in case of UNION SQL Server build temporay indexes on all the columns.
Thanks
Muhammad Wasim
correction!!
As in case of UNION SQL Server build temporay index (not indexes) on all the columns.
Nice article to understand the difference b/w UNION and UNION ALL.
Keep it up :)
Very comprehensive stuff. Great.
hi ,
its reakky a good article . well i wana know how can i import data from excel sheet into 2005 database.pls do reply
thanks
amit sharma
Hi amit,
you can import the Excel Sheet data using the Sql Server Import and Export Command. Below is the steps for the same:
1. Right click on the Database Select Tasks-> Import Data…
2. Select the datasource as ‘Microfost Excel’
3. Browse the location of the Excel File.
4. if you want to Import into the already existing table then you can Map the columns otherwise you can import into the new tables too.
Its is very simple wizard.
Please do let me know if you still face any issue.
Rupesh
hi Rupesh,
thanks for urs reply, well rupesh i am still getting error while importing the excel file.
error:
Error 0xc00470fe: Data Flow Task: The product level is insufficient for component “Source – Assessment$” (1).
(SQL Server Import and Export Wizard)
may i know wht is this error and what i have to do for resolving this error
thanks
amit sharma
You need to apply the latest service pack available for your SQL Server edition