Hey Geeks,

You might have the knowledge of it, but do you know that there’s a difference between this two of them. So I will try to give brief on this:

Union:

Union is a command which is being used to combine the results of two or more query. It can also be defined as the combination of selected rows from same or different table into a result set. The advantage of using Union is that they only select unique or distinct rows into the result set. Union can also be somewhat related to Join where there is a combination of different columns, but they return the results only for the two tables. While here you can query your results for more than two tables.

Here the most important thing is that all the selected columns need to have the similar data type and the attribute name should also be same, because duplicity is not allowed.

The syntax common for both the Union and Union All:

This will more clearly be understood by going through an example:

Here you can also see that it’s a Union are being used within the Single table itself:

So now if I want a Union of these two tables Students1 and Students2, the query will be like:

Then the result would be like:

You can see that the first row is being occuring in both the tables, but in Union the duplicacy is removed.

If you want to see the results in a separate table, in this case we have named Std, then the query will be:

Union All:

Union All is also a command which is being used to combine the results of two or more query. It can also be defined as the combination of selected rows from same or different table into a result set. Union All selects all the rows into the result set, i.e. there is no distinct behavior. This can be taken as an advantage or sometimes might be as a disadvantage. Union All also have advantage over Union is that they give faster results. So it’s better to use in the case where being known that all the rows are unique.

The Union All also have better Optimal Performance if were compared with the Union.

The Syntax is being common between them, just use Union All in place of Union.

This will more clearly be understood by going through the same example and its almost same just use the Union All in place of Union.

You can create the two tables using Union All also.

I will showcase directly to the step where there comes a difference between the Union and Union All.

If you want a Union All of these two tables Students1 and Students2, the query will be like:

Then the result would be like:

As you can see the two rows are being repeated, i.e. duplicity is allowed here.

Well this was all about Union and Union All in SQL Server.

Hope you got it understood well