Explain set operators in DBMS

Operators like union, intersect, minus and exist operate on relations. Corresponding to relational algebra U, ∩ and -. Relations participating in the operations must have the same set of attributes.

The syntax for the set operators is as follows −

<query1><set operator><query2>

Now, let us understand the set operators in the database management system (DBMS).

UNION − It returns a table which consists of all rows either appearing in the result of <query1> or in the result of <query2>

For example,

select ename from emp where job=’mamager’ UNION select ename from emp where job=’analyst’;

UNION ALL − It returns all rows selected by either query, including all duplicates.

For example,

select salary from emp where job=’manager’ UNION ALL select salary fro, emp where job=’analyst’);

INTERSECT − It returns all rows that appear in both results <query1> and <query2>

For example,

select * from orderList1 INTERSECT select * from orderList2;

INTERSECT ALL − It is same as INTERSECT, returns all distinct rows selected by both queries.

For example,

select * from orderList1 INTERSECT ALL select * from orderList2;

MINUS − It returns those rows which appear in result of <query1> but not in the result of <query2>

For example,

select * from(select salary from emp where job=’manager’ MINUS select salary from emp where job=’CEO’);

Example

Consider the step by step query given below −

Step 1

Create table T1(regno number(10), branch varchar2(10));

The output is given herewith: Table created.

Step 2

insert into T1 values(100,'CSE');
insert into T1 values(101,'CSE');
insert into T1 values(102,'CSE');
insert into T1 values(103,'CSE');
insert into T1 values(104,'CSE');

The output will be as follows: 5 rows inserted.

Step 3

create table T2 (regno number(10), branch varchar2(10));

The output is as follows: Table created.

Step 4

insert into T2 values(101,'CSE');
insert into T2 values(102,'CSE');
insert into T2 values(103,'CSE');

The output is given herewith: 3 rows inserted.

Step 5

select * from T1;

Output

You will get the following output −

100|CSE
101|CSE
102|CSE
103|CSE
104|CSE

Step 6

select * from T2;

Output

You will get the following output −

101|CSE
102|CSE
103|CSE

Application of set operators

Now apply the set operators on the two tables which are created above.

The syntax for use of set operators is as follows −

select coulmnname(s) from tablename1 operatorname select columnname(s) from table2;

Union

Given below is the command for usage of Union set operator −

select regno from T1 UNION select regno from T2;

Output

You will get the following output −

Intersect

Given below is the command for usage of Intersect set operator −

select regno from T1 INTERSECT select regno from T2;

Output

You will get the following output −

101
102
103

Minus

Given below is the command for usage of Minus set operator −

select regno from T1 MINUS select regno from T2;

Output

You will get the following output −

100
104

Bhanu Priya

Published on 03-Jul-2021 08:47:32

Previous Page Print Page