BrevFeed
Sign in

18. CROSS JOIN and self-joins

📖 Reading · 8 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).

Try it

A classic self-join: find every pair of actors who share a last name. Return three columns — last_name, the first actor's first name as actor_a, and the second actor's first name as actor_b — with each pair listed once (no self-pairs, no reversed duplicates: the actor_id < trick from the film example). Order by last_name, actor_a, actor_b.

-- pairs of actors with the same last_name, each pair once
SELECT a1.last_name, a1.first_name AS actor_a, a2.first_name AS actor_b
FROM actor a1
JOIN actor a2 ON ...

SELECT a1.last_name, a1.first_name AS actor_a, a2.first_name AS actor_b
FROM actor a1
JOIN actor a2
    ON a2.last_name = a1.last_name
    AND a1.actor_id < a2.actor_id
ORDER BY a1.last_name, actor_a, actor_b;

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 →