Difference Between Union and Union All in SQL Server

The key difference between union and union all in SQL server is that union gives the resulting dataset without duplicate rows while union all gives the resulting dataset with the duplicate rows.

DBMS is a software to create and manage databases. A database consists of many tables, and the tables are related to each other. DBMS helps to perform operations such as creating databases, creating tables, inserting and updating data and many more. Furthermore, it secures data and reduces data redundancy for data consistency. SQL server is one such DBMS. Structured Query Language (SQL) is the language to manage data in the DBMS. Union and union all are two commands in SQL that help to perform set operations in the table data.

What is Union in SQL Server?

Sometimes it is necessary to perform set operations in SQL. Union is one of them.

Union combines results of two or more select statements. Thereafter, it will return the result without any duplicate rows. To perform this operation, the tables should have the same number of columns and same data types. Refer the below two tables.

The first table is s1 and the second table is s2. The SQL statement to perform union is as follows.

select * from s1

union

select * from s2;

It will provide the result set as follows.

It gives a resulting table without the duplicate rows.

What is Union All in SQL Server?

The union all is another SQL command to perform set operations. Similar to Union, this will also combine results of two or more select statements. It is also necessary to have the same number of columns and same data types to the tables which the union all operation applies to.  Refer the below two tables.

Similar to before, the first table is s1 and the second table is s2. The statement to perform union all is as follows.

select * from s1

union all

select * from s2;

It will provide the result set as follows.

It gives the resulting table with duplicate rows.

What is the Difference Between Union and Union All in SQL Server?

Union is an SQL command that combines the results of two or more select statements without returning any duplicate rows. Union All is an SQL command that combines the result of two or more select statements including duplicate rows. This is the key difference between union and union all in SQL server. In other words, union gives the resulting dataset without duplicate rows. On the other hand, union all gives the resulting dataset with duplicate rows.

Summary – Union vs Union All in SQL Server

This article discussed two SQL command related to set operations, which are union and union all. The difference between union and union all SQL server is that union gives the resulting dataset without duplicate rows while union all gives the resulting dataset with the duplicate rows. SQL server executes the statements with these SQL commands.

Reference:

1.“SQL UNION.” First Normal Form (1NF) – Database Normalization. 

2.“SQL UNION ALL.” First Normal Form (1NF) – Database Normalization.

Source: differencebetween.com