

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- 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
- Related Questions & Answers
- Relational Set Operators in DBMS
- Explain the logical operators in DBMS
- Explain Select command in DBMS
- Explain join dependency in DBMS
- Explain the characteristics of DBMS?
- Explain the architecture of DBMS?
- Explain the components of DBMS?
- Explain about nested queries in DBMS
- Explain closure of attributes in DBMS
- Explain the Relational Model in DBMS?
- Explain the Network Model in DBMS?
- Explain the cardinality concept in DBMS?
- Explain 5NF with examples in DBMS
- Explain the relational algebra in DBMS?
- Explain about concurrent transactions in DBMS