Using Set Operators.

—- Using Set Operators.
—- Union, Union ALL, Intersect, Except

drop table #Temp1
drop table #Temp2

Create table #Temp1
(
ID int ,
name varchar(50)
)
Insert into #Temp1 Values(1,’AAA’)
Insert into #Temp1 Values(2,’BBB’)
Insert into #Temp1 Values(3,’CCC’)
Insert into #Temp1 Values(4,’GGG’)
Insert into #Temp1 Values(5,’MMM’)

Create table #Temp2
(
ID int ,
name varchar(50)
)
Insert into #Temp2 Values(1,’MMM’)
Insert into #Temp2 Values(2,’DDD’)
Insert into #Temp2 Values(3,’CCC’)
Insert into #Temp2 Values(4,’JJJ’)
Insert into #Temp2 Values(5,’MMM’)

—- Here Union Queries. It returns a result set of distinct only rows combined from two or more statements. It removes duplicates rows during the query processing.
Select * from #Temp1
union
Select * from #Temp2

ID name
———– ————————————————–
1 AAA
1 MMM
2 BBB
2 DDD
3 CCC
4 GGG
4 JJJ
5 MMM

(8 row(s) affected)

—- Here Union all Queries. It returns the result set, it combined all records even a duplicate result of two or more statements. It retains duplicate rows during Processing.
Select * from #Temp1
union ALL
Select * from #Temp2

ID name
———– ————————————————–
1 AAA
2 BBB
3 CCC
4 GGG
5 MMM
1 MMM
2 DDD
3 CCC
4 JJJ
5 MMM

(10 row(s) affected)

—- Here Intersect Queries. It returns only distinct rows that appear in both result sets.
Select * from #Temp1
intersect
Select * from #Temp2

ID name
———– ————————————————–
3 CCC
5 MMM

(2 row(s) affected)
—- Here Except Queries. It returns only distinct rows that appear in first teble result set but not second.
Select * from #Temp1
except
Select * from #Temp2

ID name
———– ————————————————–
1 AAA
2 BBB
4 GGG

(3 row(s) affected)