Difference Between UNION and UNION ALL in SQL

The difference between UNION and UNION ALL in SQL Server is one of the questions that you might face in your interview. For the SQL union and union all demonstration purposes, 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

Difference Between UNION and UNION ALL in SQL Server 1

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

Difference Between UNION and UNION ALL in SQL Server 2

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 Server, 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 the frequent 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

-- Difference Between UNION and UNION ALL in SQL Server
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2015]
UNION 
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]
Difference Between UNION and UNION ALL in SQL Server 3

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

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

-- Difference Between UNION and UNION ALL in SQL Server
USE [SQL Tutorial]
GO
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2015]
UNION ALL
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]

From the below image, you can see that it is returning 16 records. And both EMPID 6, and 7 are repeated twice (One record from Employees 2015, and another from Employees 2016).

Difference Between UNION and UNION ALL in SQL Server 4