Set Operators

Set Operators

Union, Intersect and Minus Operators

These operators are used to combine the results from one or more tables. These operators are union, intersect and minus
These operators join tables by column rather than by row.

Union Operators :

The union operator is used when you want to see the results of multiple queries together combining their output
Duplicate values are always eliminated from the resulting table of a union, unless the union all is specified
Syntax :
Select statement union [All] Select statement
Note :

1. Number of columns should be equal in both the tables
2. The data types of each column should be same in both tables
3. Column names can be different

Examples :

First Create the following Tables

1. create table emp1(empno number(3),ename varchar2(10),sal number(6));
2. create table emp2 as select * from emp1;

1. select * from emp1;

EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
101 Saloni 5400
101 Saloni 5400
102 Aruna 7600
103 Sound 5600
200 Mahesh 4500
204 Nandhini 3500

1. select * from emp2;

EMPNO ENAME SAL
--------- ---------- ---------
200 Mahesh 4500
201 Rani 8700
202 Harika 6500
202 Harika 6500
203 Pooja 6533
204 Nandhini 3500
100 Nithya 5600
103 Sound 5600
Example : 1 select * from emp1 union select * from emp2;
output
EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
101 Saloni 5400
102 Aruna 7600
103 Sound 5600
200 Mahesh 4500
201 Rani 8700
202 Harika 6500
203 Pooja 6533
204 Nandhini 3500
Example : 2 select * from emp1 union all select * from emp2;
output
EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
101 Saloni 5400
101 Saloni 5400
102 Aruna 7600
103 Sound 5600
100 Nithya 5600
103 Sound 5600
200 Mahesh 4500
201 Rani 8700
202 Harika 6500
202 Harika 6500
203 Pooja 6533
204 Nandhini 3500
200 Mahesh 4500
204 Nandhini 3500

Intersect Operator :

This operator returns all the rows in one table that also resides in other table. Ie only the common values in both the table
Syntax :
Select statement Intersect Select statement
Example : 1 select * from emp1 intersect select * from emp2;
EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
103 Sound 5600
200 Mahesh 4500
204 Nandhini 3500

Minus Operator :

This operator returns all the rows in the first table minus rows in the second table. In other words, it returns the rows present in the first table but not present in the second table
Syntax :
Select statement Minus Select statement
Example :
select * from emp1 minus select * from emp2;
EMPNO ENAME SAL
--------- ---------- ---------
100 Nithya 5600
101 Saloni 5400
102 Aruna 7600