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