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 Server 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 has seven rows.
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
Both 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 the working functionality of union and union all in the SQL 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 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 or difference between the SQL Server union and union all operators, we combine the same number of columns from the above table. The following query will combine the result set of Employees 2015, and 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 the final result 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.
The following query will return all the records (including duplicate records) from the Employees 2015 table and 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 records. And both EMPID 6 and 7 are repeated twice (One record from Employees 2015 and another from Employees 2016). I Hope, you understood the difference between UNION and UNION ALL in SQL Server.
