RSS Feed

SQL Difference between Union & Union All

SQL Difference between Union & Union All

The union and union all operators allow you to combine multiple data sets. The difference between the two is that union sorts the combined set and removes duplicates while union all does not.

With union all, the number of rows in the final data set will always equal the sum of the number of rows in the sets being combined.[Learning SQL By Alan Beaulieu]

When using the UNION command all selected columns need to be of the same data type.

For example :

XYUNIONUNION ALL
ABAA
ABBA
BA-B
---B
---B
---A

[Source of above example]

Union all is faster than union, union's duplicate elimination requires a sorting operation, which takes time.