Difference between union and union all in sql server

Introduction

In this post I will explain the difference between union and union all in SQL server table. My previous post Bulk update in sql server table columns using joins , SQL Query to find Nth Highest or Lowest salary of an employee table.

Explanation

Usually Union means it will return the common unique things from two different entities.In SQL server Union and Union All key words are used to retrieve the similar data between two tables in SQL select query, the major thing the tables must have the same column names and same data type.
Here am going to create a two temporary tables Employee and EmployeeHistory.Once Employee relieved it will be saved in History table.If employee is going to relieved he will be available in two tables up to his relieving date.

Create table #tempEmployee
(
 EmployeeCode int,
 EmployeeName varchar(max),
 EmployeeAge int
)
GO

insert into #tempEmployee(EmployeeCode , EmployeeName , EmployeeAge )
 values
  (5,'Syed',38),
  (6,'Karthik',23),
  (7,'Ramesh',31),
  (8,'Ilango',30)

GO 

Create table #tempEmployeeHistory
(
 EmployeeCode int,
 EmployeeName varchar(max),
 EmployeeAge int
)
GO

insert into #tempEmployeeHistory(EmployeeCode , EmployeeName , EmployeeAge )
 values
  (1,'Anand Kannan',23),
  (2,'Arun',2,),    
  (3,'Siva',21),
  (4,'Hari',28),
  (5,'Syed',30),
  (6,'Karthik',30)

GO 

Union

Union will give the unique records in two different tables.Here Employee table will have live employees and going to relieved employees and in EmployeeHistory table having relieved employee as well as going to relieve employees.Now we are going to select the employees using UNION.

select EmployeeCode , EmployeeName , EmployeeAge from tempEmployee

UNION

select EmployeeCode , EmployeeName , EmployeeAge  from tempEmployeeHistory

Output

Union All

Union All will give the all the records including duplicates from two different tables.Now we are going to select the employees using UNION ALL.

select EmployeeCode , EmployeeName , EmployeeAge from tempEmployee

UNION ALL

select EmployeeCode , EmployeeName , EmployeeAge  from tempEmployeeHistory

Output

Now in the above output the duplicate columns are highlighted.This is the differences between UNION and UNION ALL in SQL.It depends upon the situation to use the UNION and UNION ALL.

Conclusion

        In this post i tried to explain the difference between union and union all in SQL server table. My previous post Bulk update in sql server table columns using joins , SQL Query to find Nth Highest or Lowest salary of an employee table.