11.2. Simplest Examples of Union, Intersect, and Minus¶
We will start with very simple input relations in this section as a means to visualize and understand how the first three set operators work.
11.2.1. Visualize an Example¶
In the previous section, one important point about the first three of these set operators is that the input relations are usually a result relation from performing another operation, for example a Filter and a Reduce. Let us use two such relations for our first example:
Gargling Creature, or in a longer form: creatureId of Creature who has achieved gargling skill, whose skillCode is ‘Z’.
4x100 relay Creature, or in a longer form: creatureId of Creature who has achieved 4x100 meter track relay skill, whose skillCode is ‘TR4’.
These two 1-column relations, which are result relations from performing a Filter on Achievement followed by a Reduce, can be drawn as if each row is an element in the set that makes up each relation, like this:
Note
Notice how these two relations each have one column and it’s the same in each: creatureId.
Now let’s imagine the result of each of the three set operations on these two input relations. This can be illustrated by indicating what will be in the result for each operator, like this
Study the above picture carefully to see what should result from each of the operators. We will examine the English query, the relational algebra precedence chart, and the SQL for each operator.
11.2.2. Union example¶
The English query is:
Find each gargling or 4x100 relay achieving creature.
The precedence chart for Union using the two intermediate relations mentioned above is:
The SQL for this query is:
Notice how we have a convenient keyword called UNION in this case. In the second and third tabs above we created intermediate relations to hold ‘gargling creature’ and ‘4x100 relay creature’. We then used those relations in the first tab. We will use these relations again when illustrating Intersect and Minus next.
Note
The Union operator automatically removes duplicate rows. Notice how creature 5 appears only once in the result relation, making it a relation.
11.2.3. Intersect example¶
The English query is:
Find each gargling and 4x100 relay achieving creature.
The precedence chart for Intersect using the two intermediate relations mentioned above is:
The SQL for this query uses the data and intermediate relations from the Union example, and is very similar to it:
Note
The Intersect operator automatically removes duplicate rows. Notice how creature 5 appears only once in the result relation, making it a relation.
11.2.4. Minus examples¶
The first English query is:
Find each gargling but not 4x100 relay achieving creature.
The precedence chart for Minus using the two intermediate relations mentioned above is:
The SQL for this query uses the data and intermediate relations from the Union example, and is very similar to it:
This second English query is also needed because Minus in non-symmetric:
Find each 4x100 relay but not gargling achieving creature.
The precedence chart for Intersect using the two intermediate relations mentioned above is:
The SQL for this query again uses the data and intermediate relations from the Union example, and is very similar to it:
Warning
The SQL for the Minus operator varies for each DBMS. In Oracle, the keyword is MINUS rather than SQLite’s EXCEPT shown above. In MySQL there is no single keyword syntax, and there is more than one way to get the job done. Here is one, using the phrase ‘NOT IN’ and some syntax known as an inner query (in the parentheses).
-- Minus in MySQL using NOT IN
--
SELECT A.creatureId from relay4x100Creature A
WHERE (A.creatureId) NOT IN
(SELECT B.creatureId from garglingCreature B)
;
11.2.5. Exercises¶
Try creating the precedence charts for these queries. Use a drawing tool such as draw.net. Try charts for these below as practice.
Tip
Before sending two relations into the set operation, you will first need to use other unary operators that you have already practiced. You will need to ensure that the two input relations to Union, Intersect, and Minus each have the same number an type of columns.
Recall this summary from the beginning of this chapter for which operations go with words used in the queries:
Input |
Operator |
Input |
Generic Phrase |
|---|---|---|---|
A |
Union |
B |
A or B |
A |
Intersect |
B |
A and B |
A |
Minus |
B |
A but not B |
B |
Minus |
A |
B but not A |
Not every English query below has these words- you can practice re-wording those to help guide you.
This drawio operator template has an additional tab for binary set ops that can serve as a template for drawing the precedence charts. If you dowloaded this and used it already, you just have to go find the extra tab with a new drawing.
English Query:
Find each skillCode, test_townId of achievement but not skillCode, origin_townId of skill.
Find each Skill originating in one town but not achieved in that town.
Find each Skill originating in one town or achieved in any town.
Find each Skill that has not been achieved (find each unachieved skill).
Find each Creature who has not achieved (find each non-achieving creature).
Find each Town with no achieved Skills.
Find each Town with no aspired to Skills.