Chapter 2: Relational Algebra
Metadata Card
Dimension Value Difficulty (Intermediate Beginner) Prerequisites Chapter 1 SQL basics, basic set theory (union, intersection, difference) Keywords Relational algebra, selection σ, projection π, join ⋈, division ÷, relational calculus, query optimization Code Language SQL / Relational algebra expressions
Your Progress
You follow Master Chen into his study. Pushing open the heavy wooden door, you're greeted by walls full of hanging charts — not landscapes, not calligraphy, but pages of strange mathematical symbols.
"Don't sit down yet." Master Chen points at a chart covered in Greek letters. "Do you know what Xiao Liu did his first week managing the warehouse? He locked himself in his room and drew symbols for three days."
You glance at the nearest chart. σ, π, ∪, ⋈, ρ — each symbol has simple arrows and tables drawn next to it.
Why Relational Algebra?
Consider: find "the names and classes of warriors with level > 30"
SELECT name, class FROM adventurers WHERE class = 'Warrior' AND level > 30;In plain steps: (1) From adventurers, pick class = 'Warrior' rows; (2) From those, pick level > 30 rows; (3) Keep only name and class columns.
This is relational algebra in disguise:
π_name,class( σ_class='Warrior' ∧ level>30( adventurers ))The same query as a mathematical expression. Unlike SQL, this expression has no ambiguity — it precisely describes each operation and its execution order.
Six Basic Operations
| Operation | Symbol | Description | SQL Analogy |
|---|---|---|---|
| Selection | σ (sigma) | Filter rows by condition | WHERE |
| Projection | π (pi) | Select columns | SELECT columns |
| Union | ∪ | Combine two relations | UNION |
| Difference | − | One relation minus another | EXCEPT |
| Cartesian Product | × | All row combinations | CROSS JOIN |
| Rename | ρ (rho) | Rename attributes or relations | AS alias |
σ (Selection): Filter rows. σ_class='Warrior'(adventurers)
π (Projection): Keep only specified columns. π_name,level(adventurers) — automatically deduplicates (relations are sets).
× (Cartesian Product): Pair every row of R with every row of S.
∪ (Union): Combine rows from two relations (must have same schema).
− (Difference): Rows in R but not in S.
ρ (Rename): Give a new name to a relation or attribute.
Extended Operations
Join (⋈): Combines selection + cartesian product. The most important operation in practice.
R ⋈_{condition} SNatural Join: Automatically joins on matching attribute names.
Theta Join: Joins on an arbitrary condition.
Division (÷): "Find X that is associated with ALL Y." Used for universal quantification queries.
Query Optimization
Relational algebra laws allow the same query to produce different execution plans with identical results. For example:
π(σ(R ⋈ S)) = σ(π(R) ⋈ π(S)) (with conditions)The optimizer uses these equivalences to find the most efficient plan.
Traveler's Notes
Relational algebra isn't advanced mathematics. It's the skeleton behind SQL — the "route map" the database administrator follows. Without it, you can still use SQL. But understanding it tells you what the database is actually doing, and whether it's doing it right. When you sit back at your computer and write SELECT again, you'll know exactly what the optimizer is doing behind your SQL.