The join in relational algebra
Natural join, equijoin and theta join: which attributes are matched, which schema and how many tuples you get, and what happens to tuples that have no partner.
- 24 cards
- 4 topics
- Free
The outline, topic by topic
Each topic is weighted the way the exam weighs it.
- Concepts
- Questions
- Sort and link
- The numbers
A few cards from the deck
Natural join (⋈): what is it?
Show the answer
The operator that matches the tuples of two relations having the same value on every attribute of the same name, and keeps each of those attributes once.
EMPLOYE ⋈ SERVICE matches on id_service without it being written: the shared column name sets the condition.
What is the schema of EMPLOYE ⋈ SERVICE?
```text
EMPLOYE(num, nom, id_service)
1 Durand 20
2 Morel 10
3 Petit 20
4 Roux 30
5 Faure NULL
6 Lopez 10
SERVICE(id_service, libelle)
10 Compta
20 Paie
30 Achats
40 Direction`- (num, nom, id_service, libelle)
- (num, nom, id_service, id_service, libelle)
- (nom, libelle)
- (num, nom, libelle)
Show the answer
(num, nom, id_service, libelle)
The schemas are merged and the common attribute id_service is kept only once: 3 + 2 − 1 = 4 attributes. Nothing is removed apart from this duplicate.
The natural join matches tuples that have the same value on the attributes … in both relations.
Show the answer
The natural join matches tuples that have the same value on the attributes of the same name in both relations.
It therefore needs no written condition, but it depends on column names: renaming an attribute changes its result.
Equijoin: what is it?
Show the answer
A join whose condition is an equality between one attribute of each relation; both equal columns stay in the result.
It gives a result of degree 5 where the natural join of EMPLOYE and SERVICE gives 4, as id_service appears twice.