UNION And UNION ALL In SQL Server

The SQL UNION operator combines the result of two or more SELECT statements.

Both UNION and UNION ALL concatenate the result sets of two different SQLs. They differ in the way they handle duplicates.

UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

UNION ALL does not remove duplicates, and is therefore faster than UNION.

create Table Emp1(id int primary key,name varchar(50),email varchar(50))

insert into emp1 values(1,'sunil','sunil@gmail.com'),(2,'naveen','naveen@gmail.com'),(3,'shivam','shivam@gmail.com')

create Table Emp2(id int primary key,name varchar(50),email varchar(50))

insert into emp2 values(1,'sunil','sunil@gmail.com'),(2,'bharti','bharti@gmail.com'),(3,'mansi','mansi@gmail.com')

select * from emp1

select * from emp2

select * from emp1

union

select * from emp2

select * from emp1

union all

select * from emp2

Differences between JOIN and UNION

• UNION combines the result set of two or more select queries into a single result set which includes all the rows from all the queries in the UNION, where as JOIN retrieves data from two or more tables based on logical relationships between the tables.
• In short, UNION combines rows from 2 or more tables, where JOIN combines columns from 2 or more tables.