SQL SERVER – Union vs. Union All – Which is better for performance?

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)

SQL Download, SQL Scripts
Previous Post
SQL SERVER – Download 2005 SP2a
Next Post
SQL SERVER – DBCC command to RESEED Table Identity Value – Reset Table Identity

Related Posts

190 Comments. Leave new

  • 1,80,0,”
    1,0,0,’a’
    then how can i get it in one line..

    Reply
  • Reynaldo Castellanos
    September 13, 2012 10:43 pm

    Is there a limit on the number of UNIONS that you can do in a single query ?

    Reply
  • How sixth appears twice in the result of UNION ALL.
    Thanks for the blog. Keep up the work.
    thank u

    Reply
  • Hi All –
    thanks for the explanantion about the UNION and UNION ALL

    can any one help on this

    ———————————————————————
    SELECT ‘My 1 Row123’
    UNION
    SELECT ‘My 2 Row123’
    UNION
    SELECT ‘My 12 Row123’
    UNION
    SELECT ‘My 13 Row123’
    UNION
    SELECT ‘My 122 Row123’
    ———————————————————————
    SELECT ‘My 1 Row123’
    UNION ALL
    SELECT ‘My 2 Row123’
    UNION ALL
    SELECT ‘My 12 Row123’
    UNION ALL
    SELECT ‘My 13 Row123’
    UNION ALL
    SELECT ‘My 122 Row123’
    ——————————————————————–
    when I use UNION, the result set is sorted by the column and not with UNION ALL..
    Could anyone explain the reason for this?

    Reply
    • Myself I got the answer for this :).. when we use UNION, SQL CLR will performs the sorting on the rowset and then takes the distinct, where as UNION ALL won’t perform this operation. see below link for more information:

      Thanks,
      Buddha

      Reply
    • It is because with Union, Only distinct values are selected. Since the result set returns distinct values, Distinct Sort is performed in the back end. Vs. Union All does not eliminate duplicate rows and returns all the rows from all the tables. so you won’t see sorted result.

      Reply
  • pankaj pokhriyal
    December 7, 2012 2:11 pm

    hi sir ,

    i have a problem ,,i have one table field it has asset like(row wise car,computer) and other table field record is (id of these asset like 1,2,3)
    then how to join these tables,, please reply me soon

    Reply
  • i am pretty confused about primary key and foreign key i need a kindly reply about dat!

    Reply
  • I am beginer in sql.. i have a problem..i will try combine two queries in sql using UNION..the result is fine…but my problem like
    First table contain 2,5
    second table contain 1,6
    Final Result 1,2,5,6
    but i want 2,5,1,6..
    Anyway to get like that….?
    please help me
    Thanks advance

    Reply
    • select row_number() over (order by (select 0)) as sno, col from table1
      union
      select row_number() over (order by (select 0)) as sno, col from table2
      order by sno

      Reply
  • I am doing a union of 3 complex queries with multiple joins (inner and left outer). Strangely UNION takes 22 sec while UNION ALL takes 24 sec

    Reply
  • Hi,
    I would like to union the two tables which are stored at different databases.Suppose the table A is stored in Database A and the table B is stored in Database B.How to write the query to unioj n these tables?
    Can anyone help me?

    Reply
  • Faisal Syed
    May 23, 2013 7:52 pm

    Hi Pinal,
    Can you please explain how
    if that all the records returned are unique from your union, UNION ALL gives faster results?

    Thanks
    Faisal

    Reply
    • If you use UNION ALL, it does not have to search for duplicates, it just pastes he second table below the first.
      If you are sure there are no duplicates, using UNION is useless and therefor less efficient, since it will be searching for duplicates to delete that are not there. It will find nothing and ‘afterwards’ do exactly what UNION ALL would have done already.

      Kind regards,

      Daan

      Reply
  • Very simply, the UNION of two or more datasets/tables is a result set containing all of the rows from all tables with all duplicate rows , if any, removed.

    The UNION ALL of two or more datasets/tables is a result set containing all rows from all tables including ALL duplicates, if any.

    Reply
  • Thank you, perfect explanation !

    Reply
  • It should be noted that union and union all can not be used to combine sequence values. Refer this post for more information

    Reply
  • Hi Pinal,
    Is there any alternate wy to avoid Union or union all.
    I have 2 columns in table like below

    col1 col2
    1 2
    3 4
    5 6
    and i want result as below without union/ union all
    col1
    1
    2
    3
    4
    5
    6

    Please advice !! Thanks

    Reply
  • Discuss null value with join, union with example

    Reply
  • Hi kathan ,

    create table samp (no1 int , no2 int)

    insert into samp values (1,2)
    insert into samp values (3,4)
    insert into samp values (5,6)

    select * from samp

    — code :

    declare @a int
    declare @b int
    declare cur cursor
    static for select * from samp
    open cur
    if @@CURSOR_ROWS > 0
    begin
    declare @tab table (col int)
    fetch next from cur into @a , @b
    while (@@FETCH_STATUS = 0)
    BEGIN
    INSERT INTO @tab values(@a),(@b)
    fetch next from cur into @a , @b
    END
    end
    select * from @tab
    close cur
    deallocate cur

    Reply

Leave a Reply

Menu
Exit mobile version