Difference Between UNION and UNION ALL in SQL

In this article we will show you the difference Between UNION and UNION ALL in SQL Server. This is one of the SQL Server Interview Question that you might face in your interview.

For the sql union and union all demonstration purpose, We are going to use two tables (Employees 2015 and Employees 2016) present in our SQL Server Database.

From the below screenshot you can see that the [Employees 2015] table have seven records

And [Employees 2016] table have nine records. Remember that, two records whose EmpID is 6, and 7 are repeated in both [Employees 2015] and [Employees 2016] tables

Difference Between UNION and UNION ALL in SQL Server

Both the UNION and UNION ALL operators are used to combine the result set of two or more SELECT statements into a single result set. However, there is a slight difference in working functionality of union and union all in sql, and the result set:

Union will select all the distinct records from all queries.

Union ALL will select all the records (including duplicate records) from all queries.

Performance of the Union is slightly lower than the Union ALL operator because it has to check for the duplicates (which is a time-consuming process)

TIP: If you know that the tables had no duplicate records then go for the UNION ALL, otherwise use UNION operator.

SQL Server Union example

The following union query will combine the result set of Employees 2015, Employees 2016 tables, and display the result

From the above screenshot you can observe that, it is returning 14 records because, except 2 record (EMPID 6, and 7) all the remaining records in Employees 2016 are distinct records. This is because Union operator select only distinct records.

SQL Server Union All example

The following union all Query will return all the records (including duplicate records) from Employees 2015 table, Employees 2016 tables and shows the result