- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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 −
100 101 102 103 104
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