Relational Set Operators in DBMS

DBMS supports relational set operators as well. The major relational set operators are union, intersection and set difference. All of these can be implemented in DBMS using different queries.

The relational set operators in detail using given example are as follows as follows −

Student_Number	Student_Name	Student_Marks
1	John	95
2	Mary	80
3	Damon	57

Student_Number	Student_Name	Student_Marks
2	Mary	50
3	Damon	98
6	Matt	45

Union

Union combines two different results obtained by a query into a single result in the form of a table. However, the results should be similar if union is to be applied on them. Union removes all duplicates, if any from the data and only displays distinct values. If duplicate values are required in the resultant data, then UNION ALL is used.

An example of union is −

Select Student_Name from Art_Students
UNION
Select Student_Name from Dance_Students

This will display the names of all the students in the table Art_Students and Dance_Students i.e John, Mary, Damon and Matt.

Intersection

The intersection operator gives the common data values between the two data sets that are intersected. The two data sets that are intersected should be similar for the intersection operator to work. Intersection also removes all duplicates before displaying the result.

An example of intersection is −

Select Student_Name from Art_Students
INTERSECT
Select Student_Name from Dance_Students

This will display the names of the students in the table Art_Students and in the table Dance_Students i.e all the students that have taken both art and dance classes .Those are Mary and Damon in this example.

Set difference

The set difference operators takes the two sets and returns the values that are in the first set but not the second set.

An example of set difference is −

Select Student_Name from Art_Students
MINUS
Select Student_Name from Dance_Students

This will display the names of all the students in table Art_Students but not in table Dance_Students i.e the students who are taking art classes but not dance classes.

That is John in this example.

David Meador

Published on 27-Jul-2018 17:40:20

Previous Page Print Page