Set operations integrate the outcome of two entirely independent SQL queries. There are three set operations in SQL Union, Intersect, Except. These set operations are based on the principles of mathematical set theory. The set operations are applicable to the type compatible relations only, that means relations involved in operation must have an ‘equal number of attributes’ and they must have the ‘same attribute domains’. This is also called union compatibility.
The queries that are integrated using set operators are termed as compound queries. If we consider variations, there are two variations of the set operations. First variation; the set operations Union, Intersection & Except are applied to the traditional sets and its outcome do not have any duplicate records in it. Second variation; the set operation Union All, Intersection All & Except All are applied to the multisets and its outcome have duplicates record in it.
The two set operations ‘Union’ and ‘Intersection’ are commutative that means they exhibit the same result irrespective of the order of the operands. While the ‘Except’ set operation is not commutative. The Union, Intersection and Except are the binary operations so, they operate only two operands for evaluating the result.
Set Operations in DBMS:
1. Union Set Operations
Union set operation is the binary operation that unites the results of two independent select queries. The outcome of Union operation has the records that are either present in its first operand or in the second operand. The outcome of the Union operation does not have any duplicates. The union operation is commutative in nature so, the order of the input to union operator does not vary the result.
Let us take an example to understand the Union set operation. Suppose, we have a ‘Section’ relation as you can see below:
And we want to discover the set of all the courses that were taught either in Fall ‘2018’ or in Spring ‘2019’. The query for this would be:
(select Course-id
from Section
where Semester=‘Fall’ and year=2018)
union
(select Course-id
from Section
where Semester=‘Spring’ and year=2019);
Observe the result above, it has a set of course-ids that were taught either in ‘Fall’ 2018 or in ‘Spring’ 2019. The result has eliminated the duplicate record such as course-id CS-101 has been taught in ‘Fall’ 2018 as well as ‘Spring’ 2019 still, it appear only once. The course-id CS-319 has also been taught in two sections 1 & 2 in Spring 2019 but, here also duplicate has been eliminated and the course-id CS-319 appear only once.
2. Union All Set Operation
Union all is a variation of Union set operation. Instead of eliminating the duplicates like Union, it retains all the duplicates in the result. Let us take the same example as above but now with union all set operation. The query below will evaluate the course-ids that were taught in either in ‘Fall’ 2018 or in ‘Spring’ 2019.
(select Course-id
from Section
where Semester=‘Fall’ and year=2018)
union all
(select Course-id
from Section
where Semester=‘Spring’ and year=2019);
Observe the result above, two course-ids CS-101 and CS-319 that were eliminated in the union operation, appears twice in the result. In this way, the outcome of union all operation has the tuples that are in both the operands and it also preserves the duplicates.
3. Intersect Set Operations
Intersection (Intersect) operation is a binary operation whose outcome has the set of tuples that are present in both the relations included in the intersection operation. Interest operation eliminates the duplicates from the result.
Like the union, intersection operation is also commutative. So, the order of the relations involved in the intersection operation doesn’t vary the result. To understand the intersection operation clearly we will apply the intersection operation to the above example.
So here, we want the list of all the courses that were taught in ‘Fall’ 2018 as well as ‘Spring’ 2019. The query for the same would be as below:
(select Course-id
from Section
where Semester=‘Fall’ and year=2018)
intersect
(select Course-id
from Section
where Semester=‘Spring’ and year=2019);
There is only one course CS-101 that was taught in Fall 2018 and in Spring 2019.
4. Intersect All Set Operation
Intersect all operation is a variation of ‘intersect’ operation. Despite of eliminating the duplicates Intersect all operation retains them in the result. So, if you want the set of all the courses that were taught in Fall 2018 as well as in Spring 2019. Then the query for this using Intersect all is as below:
(select Course-id
from Section
where Semester=‘Fall’ and year=2018)
intersect all
(select Course-id
from Section
where Semester=‘Spring’ and year=2019);
As we have evaluated there is only one course CS-101 that was taught in ‘Fall’ 2018 as well as in ‘Spring’ 2019. And ‘intersect all’ retain ‘duplicate’ hence, CS-101 appear twice in the result.
5. Except Set Operations
Except operation is a binary operation that outputs all the tuples from its first operand that are not present in the second operand. Except operation is similar to the set difference. Except operation eliminates the duplicate tuples from its result.
So, to list all the courses that were taught in ‘Spring’ 2019 but the same was not taught in ‘Fall’ 2018 the query for this would be:
(select Course-id
from Section
where Semester =‘Spring’ and year=2019)
Except
(select Course-id
from Section
where Semester=‘Fall’ and year=2018);
Observe the result it has all the courses that were taught in Spring 2019, but the same was not taught in Fall 2018. Here, course CS-319 occurs twice as it was taught in two sections in Spring 2019 but except operation eliminates duplicates, it appears just once in the result.
6. Except All Set Operation
Except all operation is a variation of Except operation. The result of Except all operation retains the duplicate tuples. So, to list the set of courses that were taught in Spring 2019 but not in Fall 2018 the query would be:
(select Course-id
from Section
where Semester =‘Spring’ and year=2019)
Except
(select Course-id
from Section
where Semester=‘Fall’ and year=2018);
Observe the result above, it has retained the duplicate course CS-319 as it has appeared twice for two sections in Spring 2019.
So, this was all about Set operations in SQL. We have discussed three set operations union, intersect and except operation. Remember, the set operations are applicable to type compatible relations that have equal number of attributes with the same domain.
Leave a Reply