BrevFeed
Sign in

18. CROSS JOIN and self-joins

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

CROSS JOIN: every combination, no matching condition

CROSS JOIN pairs every row of the left table with every row of the right table — no ON condition, because there's nothing to match. If the left table has m rows and the right has n, the result has exactly m × n rows. This is the mathematical Cartesian product the relational model is built on (Module 0) — every other join type is essentially "a cross join, then a filter."

SELECT s.store_id, c.category_id
FROM store s
CROSS JOIN category c;
-- 500 store rows × 16 categories = 8000 rows

(pagila's store table ships 500 rows in this dataset — only store_id 1 and 2 are actually referenced by any customer or inventory row; the rest is bulk padding data, useful later in Module 10 for index/performance lessons that want a bigger table to measure against. Don't assume "500 stores" means 500 operating stores.)

Real uses are narrower than the name suggests. The main legitimate case: generating every combination of two independent dimensions, typically to guarantee complete coverage before an aggregate — e.g. "show 0 for every store/category pair with no sales, not just the pairs that had at least one":

-- Every store × category combination, LEFT JOINed against actual
-- inventory counts, so combinations with zero inventory still show 0
-- instead of being silently absent from the result.
SELECT s.store_id, c.name AS category, count(i.inventory_id) AS items_in_stock
FROM store s
CROSS JOIN category c
LEFT JOIN film_category fc ON fc.category_id = c.category_id
LEFT JOIN inventory i ON i.film_id = fc.film_id AND i.store_id = s.store_id
GROUP BY s.store_id, c.name
ORDER BY s.store_id, category
LIMIT 10;

(GROUP BY is Module 5 — don't worry about the aggregation mechanics yet, just notice the CROSS JOIN establishing "every combination exists as a row to begin with.")

An accidental cross join is a classic, expensive bug: forgetting a join condition entirely. Two tables with 1,000 and 10,000 rows, cross-joined by mistake, silently produces 10 million rows — often not erroring, just running very slowly and returning a nonsensical result set. Always ask "why does this join have no ON clause" before writing one; if the answer isn't "I deliberately want every combination," you're missing a condition.

Self-joins: a table joined to itself

Nothing in SQL requires the two sides of a join to be different tables — a self-join joins a table to itself, using two different aliases to tell the two "copies" apart. This is how you express relationships within one table's own rows.

Classic use case 1 — hierarchies. pagila's staff table doesn't have a manager hierarchy, but this is the textbook shape: an employee table where each row has a manager_id pointing back to another row in the same table.

-- Hypothetical employee hierarchy — the shape to recognize:
-- SELECT e.name AS employee, m.name AS manager
-- FROM employee e
-- LEFT JOIN employee m ON m.employee_id = e.manager_id;

Classic use case 2 — comparisons within a table. A real pagila example: find pairs of films with the same replacement_cost, without comparing a film to itself and without listing each pair twice:

SELECT f1.title AS film_a, f2.title AS film_b, f1.replacement_cost
FROM film f1
JOIN film f2
    ON f1.replacement_cost = f2.replacement_cost
    AND f1.film_id < f2.film_id      -- prevents self-pairing AND duplicate reversed pairs
ORDER BY f1.replacement_cost, film_a
LIMIT 10;

That f1.film_id < f2.film_id condition is the whole trick, worth understanding precisely: without it, you'd get every film paired with itself (film_id = film_id, cost trivially equal), and every real pair twice, once as (A, B) and once as (B, A). Using < instead of <> solves both problems in one stroke — it excludes self-pairs (x < x is never true) and picks a single canonical direction (only the pair where the first film_id is smaller survives).

Check yourself

  1. If table A has 50 rows and table B has 200 rows, how many rows does A CROSS JOIN B produce?
  2. What's the most common accidental way a query ends up behaving like a cross join?
  3. In the self-join film-pairing example, what would change about the result if you used f1.film_id <> f2.film_id instead of f1.film_id < f2.film_id?
← Previous Next →