Home > Differences, Optimization Performance > Why UNION ALL is faster than UNION?

Why UNION ALL is faster than UNION?

December 30, 2010 Leave a comment Go to comments

UNION and UNION ALL are used to combine data or record sets from two different tables. One could combine more than 2 tables. Just like JOINS, UNION combines data into a single record-set but vertically by adding rows from another table. JOINS combine data horizontally by adding columns from another table.

UNION insures you get DISTINCT records from both the tables.
UNION ALL pulls out all records from both the tables with duplicates.

SQL Scripts below proves why UNION ALL is faster than UNION:

-- Create First table: #tempTable1
select FirstName, LastName into #tempTable1
from Person.Contact
where ContactID <= 100 -- Create Second table: #tempTable2 select FirstName, LastName into #tempTable2 from Person.Contact where ContactID > 100 and ContactID <= 200

Comparison:

SET STATISTICS PROFILE ON

-- Using UNION - Query Cost: 35%
select * from #tempTable1
UNION
select * from #tempTable2
Query Plan for UNION:
  |--Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
       |--Concatenation
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
-- Using UNION ALL - Query Cost: 13%
select * from #tempTable1
UNION ALL
select * from #tempTable2
Query Plan for UNION ALL:
  |--Concatenation
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
-- Using UNION ALL with DISTINCT - Query Cost: 52%
select DISTINCT * from #tempTable1
UNION ALL
select DISTINCT * from #tempTable2
Query Plan for UNION ALL with DISTINCT:
  |--Concatenation
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable1].[FirstName] ASC, [tempdb].[dbo].[#tempTable1].[LastName] ASC))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
       |--Sort(DISTINCT ORDER BY:([tempdb].[dbo].[#tempTable2].[FirstName] ASC, [tempdb].[dbo].[#tempTable2].[LastName] ASC))
            |--Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))
SET STATISTICS PROFILE OFF

-- Final Cleanup
drop table #tempTable1
drop table #tempTable2

The Query plan for:
– UNION ALL SELECT above shows it just does a concatenation.
– UNION SELECT above shows it does a SORT on final concatenation.
– UNION ALL with DISTINCT SELECT above shows it does a SORT of individual tables than does the final concatenation.

The Query cost with:

– UNION ALL = 13%
– UNION = 35%
– UNION ALL with DISTINCT = 52%

This proves that:

  • UNION ALL is faster and more optimized than UNION. But this does not mean you use UNION ALL in every scenario.
  • UNION is not equivalent to “UNION ALL with DISTINCT”.
  1. April 13, 2011 at 4:02 pm

    Good Explanation

  2. ammar
    January 18, 2012 at 8:55 am

    Good one…

  3. April 13, 2013 at 1:40 am

    Reblogged this on Database (SQL Server , Oracle) World and commented:
    Why UNION ALL is faster than UNION?

  4. April 13, 2013 at 1:53 am

    can you expaline more what the meaning of query query plan:
    Query Plan for UNION:
    |–Sort(DISTINCT ORDER BY:([Union1008] ASC, [Union1009] ASC))
    |–Concatenation
    |–Table Scan(OBJECT:([tempdb].[dbo].[#tempTable1]))
    |–Table Scan(OBJECT:([tempdb].[dbo].[#tempTable2]))

    Also how you can determind the The Query cost with:

    – UNION ALL = 13%
    – UNION = 35%
    – UNION ALL with DISTINCT = 52%

    can you tell me from were you get this result ?
    Thanks

  5. bari
    August 7, 2013 at 10:32 pm

    Mostafa, you need to use the following command above your query.

    SET SHOWPLAN_ALL ON
    Go

  6. November 20, 2019 at 3:03 pm

    Why is UNION ALL faster? Because UNION must do a sort to remove the duplicates. If you do not need to remove duplicates then UNION ALL is the better option, however UNION does have a purpose and should be used when appropriate.

  1. September 27, 2011 at 2:45 pm
  2. June 14, 2012 at 8:36 pm
  3. November 19, 2012 at 12:37 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: