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 (https://blog.sqlauthority.com)
67 Comments. Leave new
You Rock !
good one..thanks
Very nice and very clearly explane.
There is an error in the script at . The last query uses UNION ALL but should be UNION. Please correct it (the query in the article is correct).
P.S. – Your blog is very helpful. Nearly every time I look for an answer to a SQL Server problem (I’ve been forced to convert from Oracle), I see your face in the results. :) Keep up the good work.
Your explanation is easily understandable!!
exellent blog
Nice article. Thanks
Well explained!
Great!
Well explained article.
Thank you.
Useful and important article.
Thank you
Useful article.
Thanks
nice article, really helped me in my interview.
Explained in a perfect way… with simple English… :) Thanks
Thanks for the article. Very easy to understand.
Useful article. Thank you
this article is really helpfull. keep writing such helpfull blogs. thnx pinal dave!!!
Very Useful article…. Thanks
Best Example of Union Vs Union ALL
SELECT 1 AS A
UNION
SELECT 1 AS A
= one row
SELECT 1 AS A
UNION ALL
SELECT 1 AS A
= two rows
I have tried this and its work well,
$xque1 =
“SELECT DISTINCT * from s_optional_que WHERE std_id = ‘$std’ && sub_code = ‘$subcode’ && chap_code = ‘$chap_code’ && quetype_code = ‘$qt1code’ && opdif_code = ‘$dif’
UNION ALL
SELECT DISTINCT * from s_optional_que WHERE std_id = ‘$std’ && sub_code = ‘$subcode’ && chap_code = ‘$chap_code3’ && quetype_code = ‘$qt1code’ && opdif_code = ‘$dif’
UNION ALL
SELECT DISTINCT * from s_optional_que WHERE std_id = ‘$std’ && sub_code = ‘$subcode’ && chap_code = ‘$chap_code2’ && quetype_code = ‘$qt1code’ && opdif_code = ‘$dif’ ORDER BY RAND() LIMIT $qtn1”;
I have a question about the UNION statement. I realize that it pulls a distinct list.
If I have a union using two select statements and each table has a value, it returns only one of the two. My question is, which value is returned, the value from the first select or the value from the second select? Or, does it vary?
The reason I am asking is that I have a User table and a user history table. Each table has an email column. If my union returns results from both the user table and the user history table, I want the record in the user table returned, not the history table. Does the union, by default, pick the match in the first select when there are duplicates, and if not, is there any way to force it to do so?
My query would be something like:
SELECT Email FROM User
UNION
SELECT Email FROM UserHistory
Hm – why would you bother whether it’s from first or second table? The value (Email in your example) will be either indistinguishable (you won’t be able to tell where it came from) because the value in both tables is identical – OR – you would have two values anyway – from both tables – because the values are different…..