Set Operators (union, union all, minus, intersect)

Set Operators

Used to Combine output of more than one select statements on following conditions
1) All select statement should have equal number of columns
2) Datatype of each respective column must be same.

1) Union all
2) union
3) minus
4) Intersect

We will perform operation on below two tables

Table 1 emp1
empnoenamedeptno
101rohansales
102sohansales
103mohansales
104zohansales
105rammarketing
106rahimmarketing

Table 2 emp2

empnoenamedeptno
101karlhr
102voltairehr
103socrateshr
108emersonhr
109maohr
110leninhr
111marxhr


1) Union all

select emp1.empno from emp1;
union all
select emp2.empno from emp2;

empno
101
102
103
104
105
106
101
102
103
108
109
110
111

2) union 

select emp1.empno from emp1
union 
select emp2.EMPNO from emp2;


empno
101
102
103
104
105
106
108
109
110
111

3) intersect

select emp1.empno from emp1
intersect 
select emp2.EMPNO from emp2;


empno
101
102
103

4) minus

select emp1.empno from emp1
minus 
select emp2.EMPNO from emp2;

empno
104
105
106


Comments