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
nice example
Thanks.
hello Sir,
i have an problem with the union operator
i write a qry1
Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt)
as expirydt, max(tm.StrikePrice) as StrikePrice,
(tm.timeslot) as PutCall,sum(tc.FillQuantity) as BuyQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as BuyValue, 0 as SellQty,0 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 1 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot
and write a 2nd qry
Select tm.DBID,max(tm.Token) as token,max(tm.Symbol) as symbol,max(tm.Series) as series,max(tm.ExpiryDt) as expirydt,
0 as BuyQty,0 as BuyValue, max(tm.StrikePrice) as StrikePrice,tm.timeslot as PutCall,sum(tc.FillQuantity) as SellQty,
sum(tc.FillQuantity * tc.FillPrice)/100 as SellValue from Trade_Confirm tc
left join Orders o on tc.ResponseOrderNumber=o.OrderNumber left join tokenmast tm on o.DBID=tm.DBID
Where tc.BuySellIndicator = 2 and o.Userid=1 and o.StrategyCode=10 group by tm.DBID,tm.timeslot
i got result by individual run but when i join union then get a error this:
Error:Warning: Null value is eliminated by an aggregate or other SET operation.
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
if you have any solution then please send me in given mail id manishpandey2009@gmail.com
Hi …
Can you please tell me how to eliminate the duplicate rows using union all functions.
Thanks !!!!
Search for Delete duplicate in Google/Bing
Hi,
Can this be done
select col1,col2,col3…
from
{
Qry 1
union
qry 2
union
qry 3
}
order by col2,col5….
Thanks in advance.
select col1,col2,col3…
from
(
Qry 1
union
qry 2
union
qry 3
) as t
order by col2,col5….
Very much helped.Thanks.
The way it helped me is like this :
Since I want to get the stock of a given item , I had to use
UNION between tables like Purchase , Sales , Sales Return..
In case , which happens only rarely , if any 2 rows from different tables have duplicate values , UNION will remove 1 row , that would affect the stock.
In that case UNION ALL is the solution.
Input SQL query: select ‘1’ union select null union select 5
Output:
NULL
1
5
Any idea about the data type of ‘1’, because if I ‘ll give ‘a’, it throws error.
Also, why NULL comes first? What is the sort order? Even I am not able to put a column alias to imply the sort order.
Dear Arup,
I did not understand your question properly, you did not give complete information as how you want your output to be, good if you would have given an example.
Based on what I understood, this is what I would try,
Query1:
select 1 as Firstcolumn , null Secondcolumn , 5 ThirdColumn
Output:
FirstColumn SecondColumn ThirdColumn
1 NULL 5
Query2:
select 1 as OneColumn union all select null union all select 5
Output:
OneColumn
1
NULL
5
Hope this helps.
Imran.
Hi Imran,
Many thanks for the reply.
Actually in my sql query i.e. select ‘1′ union select null union select 5, the out put is :
NULL
1
5
So the first select returns 1, then the second select returns ‘NULL’ and third select returns 5. Since I have not used any order by clause, hence the output may be:
1
NULL
5
I have seen that u have solved the issue by using a columnname in your Query 2, then how is it happening?
My another question is, in this output column, the data types are different, like ‘1’ is char and 5 is int. How is it possible?
1 If you use version 2000, union will automatically sort the result by the column
2 Read about precedence [SQL Server], data types in SQL Server help file
i have to table exalple a1 and a2
a1 table return no rows
a2 table return 10 rows
when i union two table it returns 9 row why?
but i union all two table it returns 10 row. i would not understand what functioning going on.
please if u have any idea share with me
It is becuase one of the values returned from the second table has duplicate value
Good on you mate…. Its like nibbles:
CREATE VIEW asView (int) AS
SELECT name, age from person
UNION ALL
SELECT name, ageInYear from people
Hello Pinal ,
Can you provide me more difference between them lik 3 or 4 more difference bcoz in one interview i was asked to provide 5 differences between them.
Thanks
Dharm
I want to askabout the diference bettween union and join
and if there an example I will appretiate that
The Best way to understand the functionality of Union.
I came across your site the other day and i found it really useful. I am new to the world of sql and was bit confused about different join concepts. I hope you would post something regarding joins real soon or recommend some useful website.
Thanks!
KS.
Hi,
I beg you pardon, but I’ve got a similar problems on reporting services.
I usually write code using small queries collected into a union structure, such as
Select Something
Union
Select Something Else
Because of the necessity to display data in a proper order.
Now I’m in trouble because I have to use a condition statement like
IF First condition Select Statement 1.0
Else
Select Statement 2.0
Union
IF First condition Select Statement 1.1
Else
Select Statement 2.1
and so on
Each “Nucleo” If Else Select has the same structure.
Do you have some tips ti workaround or go throw this topics.
Finally, “Sono molto sorpreso dalla tua provessionalità ”…I’m Very surprise of your professional exprerience and skills.
Ciao
massimiliNO
What is the difference in processing speed of these two queries? Is one actually faster than the other???
————————————————
SELECT COL1, COL2, COL3 FROM TBL1
UNION
SELECT COL1, COL2, COL3 FROM TBL2
– — versus ———-
(notice using DISTINCT with UNION ALL)
SELECT DISTINCT COL1, COL2, COL3 FROM TBL1
UNION ALL
SELECT DISTINCT COL1, COL2, COL3 FROM TBL2
——————————————————
Note: TBL1 and TBL2 are very large and both have quite a few duplicates within each table… but each table TBL1 and TBL2 will not have anything in common. Creating a 3rd table is out of the question.
Thanks for your timely response.
Jim
Regardless of whether the data is actually unique, the DB still needs to check.
I am very Impressed with your example and statement.
I am very thankful to u.
I tried using the UNION ALL to combine the result of two separate queries where the columns / fields are of the same values.
The question is : Can I use th UNION all for more than two select statements wherethe columns / fields are of the same values.
Thank you
Yes. You can use
Your answer are satisfactory.