UNION or UNION ALL?

• VSSGeorge

  SSCrazy Eights

  Points: 8146

  More actions

  September 3, 2013 at 12:18 am

  #279213

  Is it better to use union or union all for combining two select statement queries..??

  I am checking a SP where some one has written UNION in it..I am not sure whether it will be a bottleneck or not.

• Neeraj Prasad Sharma

  Ten Centuries

  Points: 1295

  More actions

  September 3, 2013 at 12:33 am

  #1646864

  Junglee_George (9/3/2013)

  ---

  Is it better to use union or union all

  result set of Union and union all can be different based on data

  for example :

  select 1 union all

  select 2 union all

  select 2

  Result :

  1

  2

  2

  select 1 union

  select 2 union

  select 2

  Result :

  1

  2

  but for data

  select 1 union all

  select 2

  result :

  1

  2

  select 1 union

  select 2

  result :

  1

  2

  so if you are sure about you can replace union to union all in your query

  then go ahead and

  test using statistics io and query plan,

  union all is better then union as far a query performance is concern.

  Neeraj Prasad Sharma
  Sql Server Tutorials

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  September 3, 2013 at 12:57 am

  #1646876

  Depends which gives the expected results. The choice of the two is not a performance consideration, it's a correctness consideration.

  Union - combine the two resultsets and remove duplicates.

  Union all - combine the two resultsets and retain duplicates.

  So if duplicates between the two resultsets have to be removed, union is the one you need. If there's no chance of duplicates or they must be retained, then union all is the one you want.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass