Key difference: Union and union all are commands used in SQL to integrate values from two tables. An evident difference between the two is that Union returns a distinct set of values, whereas, Union all returns all the concerned values, even if they’re duplicates.

SQL stands for Structured Query Language, which is the instrument used in designing and creating database software systems. Union and Union all are nothing but SQL commands, which are used to combine values from two or more separate tables. It must be noted that the use of these commands entirely depends upon the query at hand.

The Union command can be simply understood to be one that is used to combine interlinked information from two tables. This command functions much like the Join command in SQL. The advantage of using the Union command is that it returns a totally unique set of values to the user, by eliminating all sorts of duplication and duplicated values. It makes the data more compact and sorted. It is best used in conditions which require that the displayed information be crisp and concise.

Union all is also an SQL command which is used to put together the selected values from two different tables. Unlike the Union command, the Union all does not filter the values to be returned by it. Union all, as its name suggests, returns all the values specific to the query, inclusive of the values that have been duplicated as well. However, Union all works fairly faster than the Union command. This is because there is no onus on the Union all command to eliminate duplication and present distinct values. So it works faster and displays all results as per the query.

Therefore, the use of Union all is recommended only in a condition where the user is confident about the displayed result not carrying any overlapping values. Otherwise, the Union command is the best option to perform an integration of values. It is a bit slower, but can be counted upon to provide an accurate result.

Comparison between Union and Union All:

Image Courtesy: somacon.com