Set Operations in Oracle

Set Operation:- To understand set operation we can compare table with sets in mathematics. We can use set operation on tables in database in same way as we are using mathematics on set.

To study these operations we consider “customer” and “supplier” table as example.

Selecting customer table



Selecting supplier table



They are divided as below:

1. UNION:- This clause merges output from both the query and it is used between two query.

Syntax
select column name from table name 1 UNION
select column name from table name 2;

In above syntax data from both the columns is displayed as one column and if same data is present in both column then it display only once.

Diagrammatical Representation



Use of UNION clause



In above query customer city is combined with supplier city but “gandhinagar” is display only once. It means that it never display duplicate data. Data display in output will be in alphabetic order.

2. UNION ALL:- This clause merges output from both the query and display duplicate data in output. It is used between two query.

Syntax
select column name from table name 1 UNION ALL
select column name from table name 2;

In above syntax data from both the columns is displayed as one column and it display as it is.

Use of UNION ALL clause



In above query customer city is combined with supplier city but “gandhinagar” is display thrice. It means that it display duplicate data.

3. INTERSECT:- This clause merges common data from both the query and it is used between two query.

Syntax
select column name from table name 1 INTERSECT
select column name from table name 2;

In above syntax data from both the columns is displayed as one column and it display as common data from both the query.

Diagrammatical Representation



Use of INTERSECT clause



In above query customer city is combined with supplier city and display common data.

4. MINUS:- This clause display data from first query but not from second. It is used between two query.

Syntax
select column name from table name 1 MINUS
select column name from table name 2;

In above syntax data from both the columns is displayed as one column and it display data from first query but not from second.

Diagrammatical Representation



Use of MINUS clause



In above query customer city from customer is displayed but city from supplier table is excluded.

Diagrammatical Representation



Use of MINUS clause



In above query supplier city from supplier is displayed but city from customer table is excluded.

I hope this article will help you