CSE Roadmap

Common tier · about 3 hours · 5 parts

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.