2.2Joins, outer joins and division
The idea
Joins combine related rows. The natural join matches equal values of common columns. Outer joins also keep unmatched rows. Division answers "for all" questions.
Think of it like this: Matching guests to tables by ticket number; outer joins also list the guests with no table and the empty tables.
Why it exists
Join results, outer joins and division are asked nearly every year.
How it works
- Natural join: pair rows with equal common attributes, one copy of the common columns.
- Left outer keeps every left row; right keeps every right row; full keeps both.
- Unmatched rows get NULL in the other side's columns.
- R ÷ S: values of the extra attributes that occur with every row of S.
Worked example: Takes(sid, cid) ÷ Req(cid) with Req = {DB, OS}.
Students who took both DB and OS: 1 and 2. Student 3 took only DB.
Now play with it
- →Switch between natural, left, right and full and note where NULLs appear.
- →Run the division example and explain why student 3 is missing.
| eid | name | did |
|---|---|---|
| 1 | Asha | 10 |
| 2 | Ben | 20 |
| 3 | Chen | 10 |
| 4 | Dia | NULL |
| did | dname |
|---|---|
| 10 | CSE |
| 20 | ECE |
| 30 | ME |
| eid | name | did | dname |
|---|---|---|---|
| 1 | Asha | 10 | CSE |
| 2 | Ben | 20 | ECE |
| 3 | Chen | 10 | CSE |
Matches on the common column did. Dia (did NULL) and ME (no employee) disappear.
Check yourself
The result of R ÷ S (R(A, B), S(B)) consists of
Exam notes (the dense version)
Natural join ⋈ matches on all common attributes. A theta join is σ over a cross product. Outer joins keep unmatched rows with NULLs (left, right, full). Division R ÷ S returns the values of the extra attributes of R that appear with every row of S: "students who took all required courses". R ÷ S = π(R) − π((π(R) × S) − R).
Previous year questions: Relational Algebra & Calculus (12)
PYQs are grouped by topic, so this is the full set for Relational Algebra & Calculus; try the ones that match this subtopic. Open all 12 on the topic page.