Part 4 of 5 · 2 min read
Join and rename
Building a meaningful join from a product plus a selection, and renaming to avoid ambiguity.
Join and rename
Think of the Cartesian product from part 2 as a huge, mostly useless pile of every possible pairing. A join is that same pile, with a selection applied right afterward to keep only the pairs that actually make sense together.
In plain words: a join (⋈) combines two relations, keeping only the paired rows that satisfy a join condition. A join is formally equivalent to a Cartesian product followed by a selection on the matching condition. Rename (ρ) gives a relation, or one of its columns, a new name, needed when the same relation appears twice in one expression or when two relations share a column name.
A worked example. Joining orders with customers on matching customer_id, which is exactly the Cartesian product of the two tables, filtered down to only the pairs where the IDs agree.
The key lines.
orders ⋈ (orders.customer_id = customers.customer_id) customers
is equivalent to:
σ orders.customer_id = customers.customer_id (orders × customers)
SELECT * FROM orders JOIN customers ON orders.customer_id = customers.customer_id;
Join, rename
A join (⋈) combines two relations, keeping only paired rows satisfying a condition. It is equivalent to a Cartesian product followed by a selection. Rename (ρ) gives a relation or column a new name.
Example: orders ⋈ customers on matching customer IDs produces the same result as σ (orders × customers) filtered the same way, just computed far more efficiently in practice.
For the curious Why this matters for a real optimizer
A query optimizer, covered in Level 6, reasons about a query as a tree of exactly these operators. Recognizing that a join can be rewritten as a product plus a selection is also what lets an optimizer choose a smarter execution strategy than literally building the full product first.
Quick notes
- Selection (σ) keeps matching rows; projection (π) keeps named columns.
- Union (∪) and set difference (−) combine or subtract two same-shaped relations.
- Cartesian product (×) pairs every row of one relation with every row of another, with no condition.
- A join (⋈) is a Cartesian product followed by a selection on the join condition.
- Rename (ρ) gives a relation or column a new name, needed to avoid ambiguity.