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 :

X	Y	UNION	UNION ALL
A	B	A	A
A	B	B	A
B	A	-	B
-	-	-	B
-	-	-	B
-	-	-	A

 [Source of  example]

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