The three set operators take two relations and return one:
| Algebra | Keeps the tuples that are | SQL |
|---|---|---|
r ∪ s | in r, in s, or in both | UNION |
r ∩ s | in both r and s | INTERSECT |
r − s | in r and not in s | EXCEPT |
Union-compatible
Both relations must be :
- the same number of attributes,
- the i-th attribute of r and the i-th attribute of s have the same domain.
The names do not have to match. The result uses the names of r. So you often project first:
text
π id (student) − π s_id (advisor)π id (student) − π s_id (advisor)
| id |
|---|
| S201 |
| S202 |
| S208 |
| S209 |
| S210 |
Sets, not lists
The results are sets: no duplicate tuples. UNION, INTERSECT and EXCEPT remove duplicates. UNION ALL keeps them, so it is not ∪.
∩ is a short form
text
r ∩ s = r − (r − s)r − s is the part of r that is not in s. Remove that part from r, and the tuples in both are left.