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

  • Hi All,

    I need a help that can i join two table which dont have reference between both table.
    Suppose: table 1 having column:
    ID
    Name
    Table 2 having column:
    Phone
    Address

    How can we join these table so that data can show in below form:

    Table:
    ID NAme Phone Address

    Regrds,
    Sanjay

    Reply
  • you can do by
    select a.*, b.* from table1 a, table2 b

    but its full join and it will return you the result as

    total number of records in to table1*total numebr of records in table2

    as there is nothing to match

    Reply
  • ankireddy ambati
    August 4, 2010 4:42 pm

    how i can delete duplicate records from a table…….

    Reply
  • Can anyone explain why when I did a UNION ALL the duplicates were deleted, but when I did UNION the duplicates were still there. I have seen it explained both ways, but more often that UNION ALL retains the duplicates.

    Reply
  • this is really good soln

    Reply
  • I have two table with a lot of record. Now I want get a view
    So
    Case 1:
    Select …
    From table1
    where….
    UNION
    Select…
    From table2
    where…

    Case 2:
    Select …
    From table1
    where….
    UNION ALL
    Select…
    From table2
    where…

    Case3:
    declare @tmpTable table(…)
    Go
    Insert into @tmpTable sSelect … From table1 where….
    Go
    Insert into @tmpTable sSelect … From table2 where….
    Go
    select * from @tmpTable
    drop @tmpTable

    Please help me! Which case is the best performance?

    Reply
  • James Collett
    November 3, 2010 6:07 pm

    I must say thanks for this article, which confirms how I *thought* the UNION keyword was working. I feel guilty if I just leech knowledge from this page!

    Reply
  • palkalaiselvan
    February 17, 2011 6:51 am

    100 palkalai 30000
    101 raja 5000
    102 kumar 4900
    103 murali 21000
    104 sugumar 13000
    105 gugan 8940
    106 gobi 56000
    107 dhilip 34000

    I have records like this in my notepad or word. how can i insert all those records in SQL. send me the query..

    Reply
    • Use Bulk insert

      Reply
    • Yuvraj Gautam
      March 17, 2011 12:20 pm

      @palkalaiselvan
      use bulk insert to insert text file in sql

      CREATE TABLE [dbo].[empdata](
      [empid] [int] NOT NULL,
      [empname] [nvarchar](50) NULL,
      [empsalary] [int] NULL
      ) ON [PRIMARY]

      BULK INSERT empdata FROM ‘G:\notepad.txt’ WITH
      (
      FIELDTERMINATOR = ‘\t’, –Tab
      ROWTERMINATOR =’\n’–New Row
      )

      select * from empdata

      Reply
  • nithin bhaktha
    April 28, 2011 3:32 pm

    Really good article…Understood the difference between both

    Reply
  • rajivgandhi
    May 13, 2011 5:51 pm

    use of savepoint

    Reply
  • rajivgandhi
    May 13, 2011 5:52 pm

    can u explain savepoint in sq1 server 2008

    Reply
  • I CAN’T BELIEVE THAT USING “UNION ALL” BOOSTED MY QUERY AT A 500 % FASTER THAN “UNION”
    THANK YOU VERY MUCH BROTHER.

    Reply
  • Once again you’ve saved the day for me. Mr. Pinal Dave, you’re columns have taught me more about SQL than ten books on the subject. Thank you!

    Reply
  • Good example for Unions.I have one union with 2 select statements,Does SQL consider it as one statement /2 statements?

    Reply
  • Krishna

    Today i got different scenario where union is working better than union ALL.
    One of the point astonished me while using UNION it is returning results in 20 sec where as it is taking more than 10 min

    Reply
  • PBillno PurDate Partyname Tax SBillno SalDate Partyname Tax
    ————————————————————————————————-
    1 2011-09-30 Raji 14 1 2011-09-30 Test 0
    2 2011-09-30 Testing 55.57 4 2011-10-10 Rajesh 1.60
    4 2011-10-10 Kumar 0.00 5 2011-10-10 Guna 29.40
    5 2011-10-10 Suji 0.00 6 2011-09-10 Ragu 0.80
    —————————————————————————————————

    This is My Table …

    i Have PurDate Same to SalDate.. If Not Enough Then that SalDate Record Null Row..

    How do.. Please Help Me..

    Reply
  • shivendra mani tripathi
    November 22, 2011 4:42 pm

    hello everybody..
    please assist me how to get only duplicate records from a table1 into a new table2..

    thanks in advance sir..please guide me
    Shivendra

    Reply
  • shivendra mani tripathi
    November 23, 2011 6:09 pm

    Thanks dear madhivanan …

    Reply
  • hi

    dbo.T_Products

    ID Product name
    1 CAD
    2 PTC
    3 windchill
    4 desk

    dbo.T_Users

    FK_Products Region
    1 Asia
    1 Asia
    1 Europe
    1 America
    2 Asia
    2 Asia
    3 Europe
    3 Europe

    Output:

    Product Name Asia count Europe count America count
    CAD 2 1 1
    PTC 2 0 0
    Windchill 0 2 0

    ID and FK_Products are as primary and foreign key….

    plz give me a query

    Reply
  • consider 2 table

    1st table 2nd table
    ———— ——————
    sno rno sno rno
    1 – – 1
    – 2 2 –
    3 – – 3
    – 4 4 –

    output;
    sno rno

    1 1
    2 2
    3 3
    4 4

    how is this possible and what query they are using in sql server 2005?
    any body plz give me the solution

    Reply
    • select case when sno=’-‘ then rno else sno end as sno,case when rno=’-‘ then sno else rno end as rno from table1
      select case when sno=’-‘ then rno else sno end as sno,case when rno=’-‘ then sno else rno end as rno from table2

      Reply

Leave a ReplyCancel reply

Exit mobile version