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 −
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.
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.
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.