Union and Union All Operator in SQL Server 2012

Introduction

In this article I describe Union Operator, Union all Operator and the difference between the union and union all operators. Both of these operators are used to add the result set of two or more separate queries in a single table.

The difference between join and union is that join adds the columns horizontally while union adds the rows vertically. It is very important to inform you that if we use a union or union all operator then the structure of both tables should be the same.

First of all we create two tables named emp and emp1.

Creation of first table emp:

create table emp(empId int, empName varchar(15))

Insertion of data:

insert into emp values(1,'d')
insert into emp values(2,'e')
insert into emp values(3,'e')
insert into emp values(4,'p')
insert into emp values(5,'a')
insert into emp values(6,'k')

Output:

select * from emp



Creation of second table:

create table emp1(empId int, empName varchar(15))

Insertion of data:

insert into emp1 values(7,'a')
insert into emp1 values(8,'r')
insert into emp1 values(9,'o')
insert into emp1 values(10,'r')
insert into emp1 values(11,'a')
insert into emp1 values(1,'d')
insert into emp1 values(2,'e')

Output:

select * from emp



Union :

Union is used to combine the result set of two or more queries into a single result set. The Union Operator eliminates the duplicate rows in the result set. The number of columns and data types of columns of the tables should be the same when we use the Union Operator.

Syntax:

Select columns from table1
Union
Select columns from table2
union
Select columns from table3

Example:

select * from emp
union
select * from emp1

Output:



Union all:

Union all is also used to combine the result set of two or more queries into a single result set. But the Union all Operator also shows the common records in the final result set. The number of columns and data types of columns of the tables should be the same when we use the union all operator.

Syntax:

Select columns from table1
Union all
Select columns from table2
union all
Select columns from table3

Example:

select * from emp
union all
select * from emp1

Output:



Difference between Union and Union all operator:

The difference between union and union all operator is that the Union operator does not show the common records of the tables while the union in the final result set while Union all shows common records in the final result set.

Summary:

In this article I described Union and Union All operators in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.