Union in SQL Server

As you know this is basically to combine multiple result sets into single with or without duplicate. The union operation is different from a join, In Mathematical terms; a union is addition, whereas a join is multiplication. Instead of extending a row horizontally as a join would, the union combines multiple result sets into a single result set.

Let’s look at the example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

We are combining the results from Employee and Employee_HIST table. When you use UNION operator it will eliminate the duplicate records meaning that if the same record is in both the tables then it will pickup the record from only one table.

Let’s look at another example:

SELECT EmployeeID,Name,Salary,DOJ from Employee
UNION ALL
SELECT EmployeeID,Name,Salary,DOJ from Employee_HIST

Here also we are combining the results from Employee and Employee_HIST table. When you use UNION ALL operator it will not eliminate the duplicate records meaning if you have the same record in both tables then in the final output you will see both the records.

UNION is always creates the performance issue. So when ever you are using UNION use it very judiciously. If you are not sure of what kind of data you have then you can use UNION. If you know you don’t have any duplicate records for which you want to combine the results then use UNION ALL.