Self and cross joins

Self-join: a table joined to itself

A self-join is just a join where both sides are the same table, given two different aliases. There’s nothing special in the engine — it’s the same filtered-product machinery — but it unlocks two whole classes of query.

Walking a hierarchy (adjacency list). A common pattern stores a tree by having each row point to its parent: users could have a referred_by column, or an employees-style table has manager_id referencing the same table’s id. To pair each employee with their manager’s name, join the table to itself:

-- Each employee alongside their manager's name (one level of hierarchy)
SELECT e.id, e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- LEFT JOIN so the CEO (manager_id IS NULL) still appears, manager = NULL

The LEFT JOIN matters: the top of the tree has no parent, so an inner self-join would silently drop the root. (One self-join climbs exactly one level; arbitrary-depth ancestry needs a recursive CTE — unit 05.)

Pairwise comparison. The other use is comparing rows of a table against other rows of the same table: find duplicate products, users in the same city, orders within an hour of each other. This is where the hook’s bug lives.

-- Pairs of products with the same name — deduped and self-excluded
SELECT a.id AS id_a, b.id AS id_b, a.name
FROM products a
JOIN products b ON a.name = b.name
WHERE a.id < b.id;     -- the critical line

Without WHERE a.id < b.id, you get three kinds of garbage: each row matches itself (a.id = b.id), and each genuine pair appears twice in both orders (A–B and B–A). The predicate a.id < b.id solves both at once — it excludes self-matches (an id is never less than itself) and keeps only one ordering of each pair. Use < for unordered pairs; use <> only if you genuinely want both directions.

Cross join: a deliberate product

The last lesson framed the Cartesian product as a footgun. But sometimes the product is exactly the result you want, and you write CROSS JOIN to say so on purpose. The canonical case is a dense grid — every combination of two dimensions, including the empty cells.

-- Every (day, product) cell for a 7-day report, even days with zero sales
SELECT d.day, p.id AS product_id, COALESCE(s.qty, 0) AS qty
FROM generate_series(DATE '2026-06-01', DATE '2026-06-07', INTERVAL '1 day') AS d(day)
CROSS JOIN products p
LEFT JOIN sales s ON s.product_id = p.id AND s.sold_on = d.day;

Here generate_series produces the 7 days, CROSS JOIN products makes every day-product combination (the scaffold), and the LEFT JOIN to actual sales fills in quantities, defaulting to 0 for empty cells. Without the cross join, days with no sales would be missing rows entirely — a classic reporting gap. Other deliberate uses: an “all sizes × all colors” variant matrix, or pairing each row with a fixed set of buckets.

The distinction the lesson title makes: an explicit CROSS JOIN reads as intent — “yes, I want the product.” A product that sneaks in through a comma join (FROM a, b with no WHERE linking them) or a too-loose ON is an accident and the outage from lesson 1. Write CROSS JOIN when you mean it; never let a product happen silently.