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 vs union all demonstration purposes, We are going to use two tables (Employees 2015 and Employees 2016) present in our Database.

From the below screenshot you can see that the table have seven rows

Difference Between UNION and UNION ALL in SQL Server 1

And this table has nine rows. Remember that, two rows whose EmpID is 6, and 7 repeated in both [Employees2015] and [Employees2016] tables

Difference Between UNION and UNION ALL in SQL Server 2

Difference Between UNION and UNION ALL in SQL Server

Both the 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 them in Server, and the result set:

  • UNION will select all the distinct records from all queries by removing any duplicates. The performance of this operator is slightly lower than the other operator because it has to check for the duplicates (which is a time-consuming process)
  • UNIONALL command will select all the rows (including duplicate records) from all queries.

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

To show you the comparison between these operators, we combine the same number of columns from the above table. The following query will combine the result set of Employees 2015, Employees2016 tables, and display the distinct result set.

SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2015]
UNION 
SELECT [EmpID]
      ,[FirstName]
      ,[LastName]
      ,[Education]
      ,[Occupation]
      ,[YearlyIncome]
      ,[Sales]
  FROM [Employees 2016]

From the below screenshot, you can observe that final result, it is returning 14 rows. Because, except for 2 records (EMPID 6, and 7), all the remaining records in Employees 2016 are distinct. It is because this operator selects only distinct records and removes duplicate rows.

Difference Between UNION and UNION ALL in SQL Server 3

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

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 recors. And both EMPID 6, and 7 are repeated twice (One record from Employees 2015, and another from Employees 2016). Hope, you understood the difference between UNION and UNION ALL in SQL Server.

Difference Between UNION and UNION ALL in SQL Server 4