2

Difference between union and union all?

Abhinav Mittal
  • 21
  • 2

1 Answers1

2

UNION ALL keeps all of the records from each of the original data sets, UNION removes any duplicate records. Union is more expensive than Union All since Union needs to eliminate duplicates form the resultset.

In SQL Server UNION first performs a sorting operation and eliminates of the records that are duplicated across all columns before finally returning the combined data set.

If you are interested to know further please have a look a below link:

https://dataschool.com/learn-sql/what-is-the-difference-between-union-and-union-all/#:~:text=records%2C%20including%20duplicates-,UNION%20ALL%20Difference,returning%20the%20combined%20data%20set.

Kazi Mohammad Ali Nur Romel
  • 15,301
  • 2
  • 13
  • 26
  • UNION does not perform a sorting operation. – The Impaler Mar 12 '21 at 13:57
  • @TheImpaler Please check below two link: https://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/ https://social.msdn.microsoft.com/Forums/en-US/55b6647d-7637-4ab2-99ec-d97420b14c88/union-vs-union-all?forum=transactsql – Kazi Mohammad Ali Nur Romel Mar 12 '21 at 14:28
  • SQL is a declarative language. Each engine is free to use whichever algorithm/strategy it considers best to resolve the UNION operation. Sorting the result sets is just one of the strategies to perform a UNION; it's not part of its definition. An engine can also perform a UNION using hashing, merging, scanning, or seeking. It seems the article you mention is related to SQL Server; maybe SQL Server uses sorting as a primary strategy for UNION. – The Impaler Mar 12 '21 at 14:34
  • @TheImpaler Thanks a lot. I got your point. My answer were based on SQL server but the question is on general SQL. So I have modified my answer. Thanks again for the suggestion. – Kazi Mohammad Ali Nur Romel Mar 12 '21 at 14:45