Self-Joins, CROSS JOIN & Many-to-Many

Some joins look strange only because the match-maker has a new job. A self-join matches a table to itself, a CROSS JOIN returns every possible pairing, and a junction table lets two many-sided tables meet without stuffing lists into one cell.

The hard part is not memorizing new keywords. It is reading the grain of the result: what one row now represents.

Self-joins: one table, two roles

A self-join uses the same table twice with different aliases. The café staff table has manager_id, which stores another staff member's id, so the table can play two roles: worker and manager.

SELECT s.name AS staff_member,
       s.role,
       m.name AS manager
FROM staff AS s
LEFT JOIN staff AS m
  ON s.manager_id = m.id
ORDER BY s.id;

 staff_member |  role   | manager
--------------+---------+---------
 Rosa         | owner   | NULL
 Ken          | manager | Rosa
 Ada          | barista | Ken
 Tomas        | barista | Ken
 Yuki         | baker   | Ken
 Priya        | barista | Ken
(6 rows)

staff AS s means "the staff member row." staff AS m means "the manager row." Same stored table, two table aliases, two meanings.

This is also the pattern for flights and connections. One alias can mean "first flight," another can mean "next flight," and the ON clause matches first arrival airport to next departure airport. The table is the same; the roles are different.

CROSS JOIN: every combination

A CROSS JOIN has no ON clause because it does not look for a relationship. It pairs every row on the left with every row on the right.

SELECT s.name,
       slot.slot
FROM staff AS s
CROSS JOIN shift_slots AS slot
WHERE s.role = 'barista'
ORDER BY s.id, slot.id;

 name  |  slot
-------+---------
 Ada   | morning
 Ada   | closing
 Tomas | morning
 Tomas | closing
 Priya | morning
 Priya | closing
(6 rows)

That is useful when every combination is the point: possible shift assignments, product-size menus, calendar slots, test cases. It is dangerous when accidental. If the question needed a keyed match and you wrote CROSS JOIN anyway, the row count can explode while the query still runs. Plain JOIN without ON is a syntax error in Postgres, which is kinder than a silent every-combination result.

Many-to-many uses a table in the middle

A many-to-many relationship means many rows on one side can match many rows on the other. The school version is students -> enrollments -> courses. In the café, customers can sign up for many workshops, and each workshop can have many customers:

SELECT c.name,
       w.title
FROM customers AS c
JOIN workshop_signups AS ws
  ON ws.customer_id = c.id
JOIN workshops AS w
  ON w.id = ws.workshop_id
ORDER BY c.id, w.id;

  name  |      title
--------+------------------
 Ada    | Latte Art
 Ada    | Home Espresso
 Grace  | Latte Art
 Linus  | Latte Art
 Linus  | Pour-over Basics
 Edsger | Home Espresso
(6 rows)

Read chained joins left to right. Start with customers. JOIN workshop_signups changes the grain to one row per customer-signup. JOIN workshops adds the workshop facts for each signup. If you chain four or five joins, keep asking the same question after each one: what does one row represent now?

Fan-out: when matches multiply rows

Fan-out is row multiplication caused by one row matching several rows. It is normal when the grain calls for it, and a bug when you forget it happened.

Watch what happens when order rows and workshop signup rows both hang off the same customer:

SELECT c.name,
       o.id AS order_id,
       o.amount,
       w.title
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
JOIN workshop_signups AS ws
  ON ws.customer_id = c.id
JOIN workshops AS w
  ON w.id = ws.workshop_id
ORDER BY c.id, o.id, w.id;

  name  | order_id | amount |      title
--------+----------+--------+------------------
 Ada    |      101 | 220.00 | Latte Art
 Ada    |      101 | 220.00 | Home Espresso
 Ada    |      102 |  92.00 | Latte Art
 Ada    |      102 |  92.00 | Home Espresso
 Grace  |      103 |  45.00 | Latte Art
 Linus  |      104 | 220.00 | Latte Art
 Linus  |      104 | 220.00 | Pour-over Basics
 Linus  |      105 |   8.00 | Latte Art
 Linus  |      105 |   8.00 | Pour-over Basics
 Edsger |      106 |  45.00 | Home Espresso
 Edsger |      107 |  60.00 | Home Espresso
(11 rows)

This grid is correct if the question is "which order-workshop combinations exist for each customer?" It is wrong if you forgot that each order can repeat once for every workshop signup.

The bad version shows up as a plausible total:

SELECT c.name,
       SUM(o.amount) AS reported_total
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
JOIN workshop_signups AS ws
  ON ws.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
 
SELECT c.name,
       SUM(o.amount) AS order_total
FROM customers AS c
JOIN orders AS o
  ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

  name  | reported_total
--------+----------------
 Ada    |         624.00
 Grace  |          45.00
 Linus  |         456.00
 Edsger |         105.00
(4 rows)
  name  | order_total
--------+-------------
 Ada    |      312.00
 Grace  |       45.00
 Linus  |      228.00
 Edsger |      105.00
(4 rows)

The fix is not magic. Ask the query's grain question before you aggregate. If the report is order totals, stay on the customer-order path. If the report is workshop attendance, stay on the customer-signup-workshop path. Joining both paths creates a wider question than your SUM sentence says.

You now have the full join vocabulary: matched pairs, kept unmatched rows, self-joins, every-combination joins, and many-to-many chains. Section 6 turns back inside a row and gives you the function toolkit for shaping text, dates, and numbers after the right rows are on the screen.