SQL SERVER – Difference Between Union vs. Union All – Optimal Performance Comparison

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.

Solarwinds

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.

SQL SERVER - Difference Between Union vs. Union All - Optimal Performance Comparison union1

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.

SQL SERVER - Difference Between Union vs. Union All - Optimal Performance Comparison union2

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.

SQL SERVER - Difference Between Union vs. Union All - Optimal Performance Comparison union3

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)

Solarwinds
Previous Post
SQL SERVER – Pad Ride Side of Number with 0 – Fixed Width Number Display
Next Post
SQL SERVER – What is Your Favorite Database? – Poll Continuous

Related Posts

67 Comments. Leave new

  • You Rock !

    Reply
  • good one..thanks

    Reply
  • Very nice and very clearly explane.

    Reply
  • 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.

    Reply
  • Your explanation is easily understandable!!

    Reply
  • exellent blog

    Reply
  • Nice article. Thanks

    Reply
  • Well explained!

    Reply
  • Great!
    Well explained article.
    Thank you.

    Reply
  • Useful and important article.

    Thank you

    Reply
  • Useful article.
    Thanks

    Reply
  • nice article, really helped me in my interview.

    Reply
  • Explained in a perfect way… with simple English… :) Thanks

    Reply
  • Thanks for the article. Very easy to understand.

    Reply
  • Useful article. Thank you

    Reply
  • this article is really helpfull. keep writing such helpfull blogs. thnx pinal dave!!!

    Reply
  • Very Useful article…. Thanks

    Reply
  • Krishna Mohan
    March 29, 2013 4:04 pm

    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

    Reply
  • Sagar Panchal
    April 17, 2013 4:34 pm

    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”;

    Reply
  • 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

    Reply
    • 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…..

      Reply

Leave a Reply

Menu