More than a year ago I had written article SQL SERVER – Union vs. Union All – Which is better for performance? I have got many request to update this article. It is not fair to update already written article so I am rewriting it again with additional information.
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.
Run following script in SQL Server Management Studio to see the result between UNION ALL and UNION. Download complete script from here.
/* Declare First Table */
DECLARE @Table1 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table1
SELECT 'First'
UNION ALL
SELECT 'Second'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fourth'
UNION ALL
SELECT 'Fifth'
/* Declare Second Table */
DECLARE @Table2 TABLE (ColDetail VARCHAR(10))
INSERT INTO @Table2
SELECT 'First'
UNION ALL
SELECT 'Third'
UNION ALL
SELECT 'Fifth'
/* Check the data using SELECT */
SELECT *
FROM @Table1
SELECT *
FROM @Table2
/* UNION ALL */
SELECT *
FROM @Table1
UNION ALL
SELECT *
FROM @Table2
/* UNION */
SELECT *
FROM @Table1
UNION
SELECT *
FROM @Table2
GO
In our example we have two tables: @Table1 and @Table2.
Now let us run UNION ALL and UNION together and see the resultset as well as Execution Plan compared to complete set of query. You can always turn on actual execution plan using CTRL+M.
We can see from the resultset of UNION ALL that it returns everything from both the table but from UNION it is very clear that only DISTINCT rows from both the table is only retrieved.
Additionally, when comparing the execution plan of UNION ALL and UNION it is also quite clear that UNION ALL is way less expensive than UNION as it does not have DISTINCT SORT operation.
Let me know what do you think about this article. If you have any suggestion for improvement please let me know and I will update articles according to that.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
pinaldave
[...] 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 [...]
Very nice article :)
Just one question about the execution plans. I have observed that sometime the actual execution time is different from what is suggested. Like in a batch I see a query to occupy 40 % and query 60%.
But when I actually execute both of them, Query 1 takes more time than the query 2
Is it possible ?
Great article! thank you!
I have a question – on query cost diagram it shows 50% for table scan for UNION ALL and 18% for UNION. Why is it so different? Because of the number of records?
If yes then UNION should be faster if it saves so much more resources on that stage?
Thnk you in advance!
Great article, your use of DISTINCT spured me to write a blog about it.
http://www.pchenry.com/Home/tabid/36/EntryID/166/Default.aspx
I hope you don’t mind but I mentioned this blog, I hope that’s ok with you? Keep up the great work with your blog entries!
Good Writing PHenry.
Hi
how i can select data from two different tables and both have no relationship between them. how i can use only one select statement and obtain the data from two different tables
@Sajjad Ali,
For example,
Create table employees ( eid int, ename varchar(10))
insert into employees values ( 1, ‘imran’)
insert into employees values ( 2, ‘Taher’)
We inserted two employees information.
– Lets create another table that stores salary information
create table Salary ( eid int, salary money)
insert into Salary values ( 1, 1000)
insert into Salary values ( 2, 2000)
– We inserted two employees salary information.
–Now if I want to get the complete data out of these two tables, I will use below script
select A.eid
,A.ename
,B.salary
from employees A
join Salary B on A.eid = B.eid
You need to have one common column on which you can join multiple tables.
~ IM.
@Dasha Salo
the percentages are relative , which means that 2×18% of resource in table scan v/s 63% for distinct sort.
you cannot compare %values themselves for 2 different queries.
the point to note is distinct sort is using more resources compared to no distinct sort.
In simple words, read 50% as ‘50 % of overall CPU resources/time’ for that execution plan compared to 18%.
Nice article Pinal. Very true PHenry, as soon as I look at DISTINCT I know its worth looking closer at that query !!
Nice article . Thanks .