These are the two JOINs that most bewilder beginners, and for opposite reasons. The SELF JOIN looks impossible —how do you join a table to itself without going round in a loop?— and turns out to be the only way to walk the reflexive relationships you've been seeing since 01-05: employees.manager_id and customers.referred_by_id. The CROSS JOIN looks like a mistake —it's the cartesian product we presented in 03-01 as an accident— and turns out to be a deliberate tool for generating complete combinations.

Neither of them is a new "type" of matching. The SELF JOIN is a technique that can use any type (INNER, LEFT...); the CROSS JOIN is the degenerate case of a JOIN with no condition.

1. SELF JOIN: joining a table to itself
2. The employees hierarchy
3. The customers referral network
4. Non-hierarchical SELF JOIN: pairs within the same group
5. Employees in the same city
6. Hierarchies of arbitrary depth
7. CROSS JOIN: the deliberate cartesian product
8. When it's useful and when it's an accident
9. CROSS JOIN with generate_series for calendars
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

A SELF JOIN has no syntax of its own. It's an ordinary JOIN in which both tables are the same one:

No infinite loops: the engine treats each appearance of the table as an independent relation. It's exactly 03-01's cartesian product, with employees on both sides, filtered by the ON condition.

In 03-01 we said table aliases were a convenience. In a SELF JOIN they're essential, and for a physical reason: without them, the name employees would designate two different things at once.

PostgreSQL doesn't even try to guess: it refuses. With different aliases, each appearance has its own identity and everything works.

Course convention: in a SELF JOIN, aliases aren't abbreviated by initial but named after the role each copy plays. employees AS e and employees AS manager; customers AS c and customers AS referrer; products AS p1 and products AS p2 when the two roles are symmetric. An alias like e1/e2 is acceptable in symmetric pairs, but e/manager is always more readable than e1/e2 when the roles differ.

employees.manager_id is a foreign key pointing at employees.id: the reflexive 1:N relationship you drew in the ER diagram in 01-06. Each employee has at most one manager, and a manager can have several reports.

7 rows out of 8. Rosa Alcázar Vives, the general manager, is missing, because her manager_id is NULL and matches nobody. It's exactly 03-02's mechanism: a null FK never finds a partner.

And it's a dangerous result, because it looks complete. An org chart that leaves out the general manager is a wrong org chart.

8 rows: the complete team. Rosa's NULL means "root of the hierarchy", and in a report you'd present it as "—" or "Management" with COALESCE (06-04).

Rule: in a hierarchical SELF JOIN, the root of the tree always has its FK at NULL. If you want it to appear, the JOIN has to be a LEFT. It's the most frequent mistake when building org charts, category trees or folder structures.

Reversing the direction of the condition gives you the inverse relationship. It's enough to read the ON backwards:

It returns the same 7 pairings, presented from the manager's point of view. Notice that the ON condition is identical; the only things that change are which alias is called what and which columns are projected. It's the same lesson from 03-04 about LEFT and RIGHT: the matching doesn't change, the reading does.

customers.referred_by_id works the same way, but with a different business nuance: here the NULL doesn't mean "root of a hierarchy" but "arrived on their own".

15 rows: 8 referred customers and 7 with no referrer, exactly the counts 01-06 announced.

The network these data draw:

Marketing wants to reward the customers who have brought in the most people. The query starts from the referrer:

Three customers brought in by Lucía, GreenStore's first customer. In module 4 we'll count these referrals per person with GROUP BY to find out who leads the programme; here we stay at the detail level.

Notice a subtle writing detail: even though the alias referrer is written first in the FROM, it's still the "parent" table of the relationship. Which one comes first is a readability decision, not a meaning one: what sets the direction is the condition ON c.referred_by_id = referrer.id.

Not every SELF JOIN walks a declared reflexive relationship. Another very frequent use is pairing rows that share an attribute: products in the same category, employees in the same city, orders from the same day.

The marketing team wants to design bundles of two products from the same category. First attempt:

This returns 78 rows, and it has two serious problems:

The fix fits in three characters: p1.id < p2.id.

6 rows: the 6 possible pairs among the 4 Natural cosmetics products. Without the trimming WHERE, the query returns 29 pairs across the whole catalogue.

This trick deserves a whole paragraph, because it's the canonical pattern and gets reused in a thousand places.

For any two products A and B from the same category, the cartesian product generates four combinations:

The condition does two jobs at once with a single operator:

• < instead of <= removes the reflexive pairs (A, A), because no id is less than itself.
• < instead of <> removes the duplicate (B, A), because of the two possible orderings only one satisfies the strict inequality.

The arithmetic confirms the effect: with n products in a category, with no condition there are n² combinations; with <> there are n² − n; with < there are exactly n(n−1)/2, which is the combinatorial number of pairs.

Note: this is a JOIN with an inequality condition, what in 03-01 we called a non-equi join. The equality p1.category_id = p2.category_id defines the group; the inequality p1.id < p2.id selects one of the two orderings. It's usual for a non-equi join to accompany an equality one, not to replace it.

The same pattern, applied to employees. HR wants to organise team lunches by office and needs the pairs of colleagues who work in the same city:

(First 10 of 21 rows.)

21 pairs, all of them from Valencia: seven of the eight employees work there, and 7 × 6 / 2 = 21. Laia Puig Sanchis doesn't appear in a single row, because she's the only one working in Castellón and has nobody to pair with.

That detail matters: a SELF JOIN of this kind automatically excludes the elements that are unique within their group. If you wanted a listing where Laia also appeared (with NULL as her colleague), you'd need a LEFT JOIN with the same condition in the ON.

It's also worth noting that here city allows NULL. If two employees had the city unfilled, NULL = NULL isn't true and they wouldn't be paired, which in this case is the right behaviour: we don't know whether they work together.

A SELF JOIN walks exactly one level of the hierarchy. To go up two levels you need two chained SELF JOINs:

It works, and in GreenStore it's enough because the org chart has only three levels. But notice the limitation: the number of levels is hard-coded into the query. If the company grows to five levels, you have to rewrite it adding two more LEFT JOINs; if an employee sits seven levels below management, this query will never reach them.

For hierarchies of unknown depth you need another tool: recursive CTEs (WITH RECURSIVE), which are studied in lesson 10-02. With them you walk a tree of any depth in a single query, and you can compute each employee's level or the complete chain of command. The same goes for the referral network: "every customer descending from Lucía, directly or indirectly" is a recursive query, not a SELF JOIN.

The CROSS JOIN combines every row of one table with every row of the other, with no condition at all. It's step 1 of 03-01's mental model, without step 2.

It returns 6 × 20 = 120 rows. No condition, no ON: CROSS JOIN doesn't accept ON, and if you write one you get a syntax error.

The size grows as the product of the cardinalities, which on real tables is explosive:

That last row —a trillion rows— is the reason an accidental CROSS JOIN can run a server out of memory. The rule is simple: a CROSS JOIN is only acceptable when at least one of the two tables is small and of known size.

They're identical to the engine. The first is a statement of intent; the second is exactly what shows up when somebody forgets the matching condition with 03-01's old syntax.

Course convention: if you want a cartesian product, write CROSS JOIN in capitals and with a comment explaining why. It's the difference between "this is intended" and "this is an oversight".

This is why the CROSS JOIN exists in an analyst's daily life.

Picture the report "sales by category and month of 2025". If you build it from the real sales alone, the months with no sales simply don't appear: a category that sold nothing in August won't have an August row, and the chart will jump from July to September as if August didn't exist.

The professional solution consists of generating the complete skeleton first —every category × month combination— and then joining the sales onto it with a LEFT JOIN:

The skeleton:

(First 8 of 72 rows.)

72 rows = 6 categories × 12 months. No combination is missing, and on that basis the report balances even if a category sells nothing all year. Adding up the sales is module 4's business; the skeleton belongs here.

The other classic case doesn't even need tables: it's built from literal lists using VALUES (which you'll study in depth in 05-02).

It returns 12 rows: the 4 sizes × 3 colours of a clothing catalogue. It's the list of references you'd have to create. GreenStore doesn't sell clothes, but the pattern shows up in any shop with product variants.

generate_series is a PostgreSQL row-generating function. It produces a series of values and is used in the FROM as if it were a table:

It's with dates that it's most useful, because it accepts an interval as its step:

And crossed with categories it generates the complete calendar for the previous report, now with real dates:

72 rows, ready to receive the sales with a LEFT JOIN.

Dialect note: generate_series is PostgreSQL-specific. The equivalents in other engines:

Engine	How to generate a series
PostgreSQL	generate_series(start, stop [, step])
SQL Server	GENERATE_SERIES (since 2022); before that, a recursive CTE or a calendar table
Oracle	CONNECT BY LEVEL <= n
MySQL 8+	A recursive CTE, WITH RECURSIVE
SQLite	A recursive CTE, or the generate_series extension

The portable alternative on any engine is to maintain a permanent calendar table with one row per day or per month. It's what most data warehouses do, and it avoids depending on the dialect.

There's an advanced variant of the CROSS JOIN in which the right-hand table can refer to columns from the left:

It's called a CROSS JOIN LATERAL (or LEFT JOIN LATERAL) and it serves, for instance, for "the three most recent reviews of each product". It needs subqueries, which are module 7, so here we only mention it so you recognise the word if you see it. LATERAL is standard SQL and is available in PostgreSQL, Oracle and SQL Server (where it's called CROSS APPLY / OUTER APPLY).

• Forgetting the aliases in a SELF JOIN. ERROR: table name "employees" specified more than once. Each copy needs its own name.
• Using an INNER JOIN on a hierarchy. You lose the root of the tree: Rosa Alcázar Vives disappears from the org chart because her manager_id is NULL. Use a LEFT JOIN.
• Forgetting the p1.id < p2.id condition when generating pairs. You get every element paired with itself and every pair duplicated: 78 rows instead of 29.
• Using <> instead of <. It removes the reflexive pairs but not the duplicates: 58 rows instead of 29.
• Believing a SELF JOIN walks the whole hierarchy. It walks exactly one level. Two levels, two JOINs. Unknown depth, WITH RECURSIVE (10-02).
• Matching on a nullable column in a group SELF JOIN. Two employees with city at NULL don't pair up, because NULL = NULL isn't true.
• Writing a CROSS JOIN with the comma syntax. It's correct, but indistinguishable from an oversight. Write an explicit CROSS JOIN.
• Trying to put an ON on a CROSS JOIN. It doesn't accept one: if you need a condition, it isn't a CROSS JOIN, it's an INNER JOIN.
• Crossing two large tables "to see what comes out". Check the product of their counts first. 10,000 × 10,000 is a hundred million rows.
• Tip: draw the tree before writing the SELF JOIN. Knowing who's the parent and who's the child stops you inverting the ON condition, which is the most common mistake and the hardest to spot.
• Tip: count the expected pairs before running it. n(n−1)/2 for the pairs of a group of n. If the result doesn't match, you know immediately that the condition is missing or superfluous.
• Tip: for reports by period, always build the skeleton first. CROSS JOIN of the dimensions + LEFT JOIN of the facts. It's the standard way to have no gaps, and you'll use it as soon as you start aggregating in module 4.

Marketing wants a referral programme sheet showing, for each customer: their full name, who referred them (or NULL if they arrived on their own) and who referred their referrer (the "grandparent" of the network).

1. Write the query using two chained SELF JOINs over customers.
2. Restrict it to the customers who do have a referrer and comment on the result.
3. Why can't this query answer "every customer descending from Lucía, at any depth"?

Purchasing wants to launch bundles of two products from the same category whose combined price doesn't exceed €10, using active products only. Write the query that returns the category, the two products with their prices and the bundle price, sorted by bundle price.

Explain the role each of the ON and WHERE conditions plays.

Answer with reasoning, without running anything:

1. How many rows does SELECT * FROM employees AS e1 CROSS JOIN employees AS e2; return?
2. And SELECT * FROM employees AS e1 JOIN employees AS e2 ON e1.id <> e2.id;?
3. And SELECT * FROM employees AS e1 JOIN employees AS e2 ON e1.id < e2.id;?
4. Write the skeleton of a supplier × quarter of 2025 report using CROSS JOIN and generate_series. How many rows does it have?

Solution 1

1. With two chained SELF JOINs:

It returns the 15 rows of customers, with two columns that fall to NULL as the chain runs out.

2. Only the customers with a referrer (it's enough to change the first LEFT JOIN into an INNER JOIN; the second must remain a LEFT):

8 rows. Only two customers have a "grandparent" in the network: Ana (brought in by Carlos, who in turn came from Lucía) and Núria (brought in by Ana, who came from Carlos). The rest descend directly from somebody who arrived on their own.

Notice that the chain Lucía → Carlos → Ana → Núria has three hops, and this query only gets to see two: Núria's row shows Carlos as the grandparent, but Lucía —the great-grandparent— no longer fits.

3. Why it's no good for "every descendant of Lucía": because the number of levels is written into the query. Each extra level demands another LEFT JOIN, and to answer that question you'd have to know the network's maximum depth in advance. With a chain of seven referrals, you'd need seven JOINs. The right tool is a recursive CTE (WITH RECURSIVE), which walks the tree until it's exhausted in a single query: lesson 10-02.

Solution 2

5 possible bundles, all from Food and Drinks: they're the two categories with cheap products. In Natural cosmetics, the cheapest bundle would be the lip balm (€4.60) with the shampoo (€8.40), which already comes to €13.

The role of each condition:

The matching conditions go in the ON and the filtering ones in the WHERE, following 03-01's convention. Since every JOIN is an INNER, here it would be equivalent to put them in either place (03-02), but the separation keeps the query readable.

Solution 3

1. A CROSS JOIN of employees with itself: 8 × 8 = 64 rows. Every combination, including the eight pairs of each employee with themselves.

2. With ON e1.id <> e2.id: 56 rows. The 8 reflexive pairs are removed (64 − 8), but each pair still appears twice, in both orders.

3. With ON e1.id < e2.id: 28 rows. That's 8 × 7 / 2, the number of distinct pairs from 8 elements. Each pair exactly once and none with itself.

The progression 64 → 56 → 28 sums up the whole of section 4.

4. Supplier × quarter of 2025 skeleton:

20 rows = 5 suppliers × 4 quarters. The generated quarters start on 1 January, 1 April, 1 July and 1 October 2025; the upper bound is 2025-10-01 because generate_series includes the final endpoint, and putting 2025-12-01 would produce an unwanted fifth value.

On top of this skeleton, a LEFT JOIN with the real purchases would give the quarterly report by supplier with no gaps, including supplier 5 (EcoNordic Supplies, inactive), which would appear with all its quarters at NULL.

You've closed the two special JOINs:

• A SELF JOIN is an ordinary JOIN in which the same table appears twice. Aliases are mandatory —ERROR: table name specified more than once— and it's worth naming them after the role they play: e / manager, c / referrer, p1 / p2.
• The employees hierarchy is walked with e.manager_id = manager.id. With an INNER JOIN you lose the root (7 rows, no Rosa); with a LEFT JOIN the complete team appears (8 rows). In every hierarchy, the root has its FK at NULL.
• The customers referral network works the same: 8 referred customers and 7 who arrived on their own. Lucía Martínez Soler brought in three.
• For pairs within the same group —products from the same category, employees in the same city— the canonical pattern is ON a.group = b.group AND a.id < b.id. The strict < removes reflexive pairs and inverted duplicates in one stroke: 29 pairs instead of 78.
• A SELF JOIN walks one level per JOIN. For arbitrary depth you need recursive CTEs (10-02).
• The CROSS JOIN is the explicit cartesian product, with no ON. It's an accident when the matching condition is forgotten, and a tool when it deliberately generates a report's skeleton (category × month) or a matrix of variants (size × colour).
• PostgreSQL's generate_series produces numeric and date series usable in the FROM. Crossed with a dimension, it gives complete calendars with no gaps; in other engines you replace it with recursive CTEs or with a calendar table.

There's only one way of combining tables you still don't know. Every JOIN in this module adds columns: it takes a row from here, a row from there and glues them side by side. In the next lesson, UNION, INTERSECT and EXCEPT, you'll do the opposite: stack whole results vertically, adding rows instead of columns. You'll build a unified contact list out of customers, employees and suppliers; you'll find out which cities have both customers and employees; and you'll meet the three never-sold products again, this time without a single JOIN.