Relational algebra includes set theory operations because relations (tables) are treated as sets of tuples (rows). These operations work on two relations that are union-compatible (i.e., same number of columns and same data types).
Here is a clear explanation of the Relational Algebra operations: JOIN, DIFFERENCE, SELECT, UNION, with syntax and examples:
1. SELECT (σ) — Selection
Definition:
- Selects rows (tuples) from a relation that satisfy a given condition.
- It is a horizontal subset operation.
Syntax:
σ<condition>(Relation)
Example:
σ age > 25 (Students)
Returns all students with age greater than 25.
2. UNION (∪)
Definition:
- Combines all rows from two relations, eliminating duplicates.
- Relations must be union-compatible (same number and type of columns).
Syntax:
R ∪ S
Example:
If R = {1, 2} and S = {2, 3}, then:
R ∪ S = {1, 2, 3}
3. DIFFERENCE (−)
Definition:
- Returns rows that are in first relation but not in second.
- Both relations must be union-compatible.
Syntax:
R − S
Example:
If R = {1, 2} and S = {2, 3}, then:
R − S = {1}
4. JOIN (⨝)
Definition:
- Combines rows from two relations based on a common attribute or join condition.
- It is a combination of Cartesian Product and Select.
Types:
- Theta Join (
⨝ condition) - Equi Join (condition uses
=) - Natural Join (auto joins on same attribute names)
Syntax:
R ⨝ R.B = S.B S
Example:
Sailors(sid, sname) Reserves(sid, bid) Sailors ⨝ Sailors.sid = Reserves.sid Reserves
Returns matched rows where sailor has reserved a boat.
Summary Table
| Operation | Symbol | Purpose |
|---|---|---|
| SELECT | σ | Filter rows based on a condition |
| UNION | ∪ | Combine rows from two relations |
| DIFFERENCE | − | Get rows in one relation not in the other |
| JOIN | ⨝ | Combine rows based on matching condition |