You closed module 2 hitting the same ceiling over and over: orders tells you customer_id = 9 and not "Camille Dubois", order_lines tells you product_id = 15 and not "Ceremonial matcha green tea". That ceiling ends here. In this lesson you'll learn what a JOIN really is, not as a formula you copy but as an operation you can rebuild in your head step by step: a cartesian product filtered by a condition. From that idea, everything else —INNER, LEFT, RIGHT, FULL, SELF, CROSS— stops being a list of names to memorise and becomes variations on a single mechanism.

This is the module's umbrella lesson: it gives you the conceptual frame, the syntax and the overview of the five types. Each of them is developed in depth in the lessons that follow.

1. Why you have to put back together what normalisation split apart
2. What a JOIN is: a filtered cartesian product
3. The ON clause and the matching condition
4. Modern syntax versus the old comma form
5. The accidental CROSS JOIN
6. USING and NATURAL JOIN
7. Table aliases and name ambiguity
8. An overview of the five types of JOIN
9. Chaining three or more tables
10. JOINs in the logical execution order
11. Common Mistakes and Tips
12. Exercises
13. Conclusion

In lesson 01-05 we justified why the category name isn't stored inside each product: if "Natural cosmetics" appeared repeated across four rows of products, fixing a typo would require four UPDATEs, and forgetting just one would leave two versions of the same fact living side by side in the table. Normalisation solves that by storing the name once only, in categories, and leaving in products nothing but a numeric reference: category_id.

The price of that decision is exactly module 2's ceiling:

A 1 is no use to anybody in a report. The fact exists, but it's spread across two tables, and you need an operation that puts them back together at query time.

The module's key idea: normalising means splitting apart so you can store well; the JOIN means putting back together so you can query well. They're two sides of the same coin, and that's why a well-designed database isn't an awkward database: it just asks you to learn how to walk its relationships.

The formal definition of a JOIN fits in one sentence:

A JOIN is the cartesian product of two tables, filtered by a condition.

The cartesian product pairs every row of the first table with every row of the second. If the first has 3 rows and the second 2, the result has 3 × 2 = 6.

Let's see it with two tiny extracts from GreenStore before touching the complete tables. These are the two working "tables":

Extract from categories (2 rows):

Extract from products (3 rows):

6 rows. Every possible combination. Most of them are rubbish: the kombucha doesn't belong to "Personal hygiene" and the toothbrush isn't a drink.

The good rows are the ones where p.category_id matches cat.id. Look at the previous table with that criterion in mind:

Three rows survive, one per product. That's exactly what a JOIN does:

Drawn as a flow of rows:

An important nuance about performance. That the JOIN is defined this way doesn't mean the engine executes it this way. PostgreSQL doesn't materialise 300 rows just to throw 280 away: it uses algorithms such as hash join, merge join or nested loop that go straight to the pairs that match, usually leaning on indexes. It's the same distinction between logical order and physical plan you saw in 02-01, and you'll study it with EXPLAIN in module 8. To reason about what a query returns, the "cartesian + filter" mental model is always correct.

With the trimming WHERE removed, the JOIN finally answers "which category is each product in?":

20 rows, the same number products has. That count is no coincidence and deserves a rule you'll use constantly:

When you join a table with N rows against another one through a mandatory foreign key pointing at a primary key, the result has exactly N rows: every row finds one partner and only one. If the count changes, something isn't the way you thought.

ON holds the matching condition: the rule that decides which row on the left goes with which row on the right.

In 95 % of the cases you'll write in your life, that condition takes exactly this shape:

That is: FK = PK. The reason is obvious if you remember 01-05: the foreign key exists precisely to point at one particular row of the other table, so the natural path between two tables is the one the FK itself draws.

These are GreenStore's canonical matching conditions. Look them up whenever you're unsure:

Although FK = PK equality is the usual case, ON accepts any boolean expression, just like WHERE:

When the condition isn't an equality you talk about a non-equi join. They're a minority, but they exist: date ranges, price bands, comparisons between rows of the same table. You'll use the p1.id < p2.id case in lesson 03-06 to generate pairs of products without repetitions.

Before the SQL-92 standard the word JOIN didn't exist. Tables were listed separated by commas in the FROM and the matching condition was written in the WHERE:

Both return the same thing and PostgreSQL generates the same plan for either. But the old one has four serious problems:

Course rule: always JOIN ... ON. The FROM describes how the tables relate; the WHERE describes which rows we care about. They never get mixed. The only exception allowed is the deliberate CROSS JOIN, which we'll see in 03-06.

This is the practical reason the comma syntax was abandoned. Forget the condition in the WHERE:

300 rows = 20 products × 15 customers. And worst of all: there's no error at all. The query runs, returns plausible-looking data and, if you put it in a report, that report will be wrong with nothing to give it away.

The same slip with the modern syntax is impossible to make without noticing:

JOIN demands an ON (or a USING). If you really want the cartesian product you have to ask for it explicitly with CROSS JOIN, which is a statement of intent nobody writes by accident.

The size of the accident grows very fast:

That last row is the reason a badly written JOIN can bring a server down. In GreenStore it only translates into an absurd result; in production, into a phone call at three in the morning.

The unmistakable symptom: if a query returns far more rows than you expected and the data seems to repeat in a loop, count your ON conditions. With N tables you need N-1 matching conditions. Three tables, two ONs. Five tables, four ONs.

SQL offers two shortcuts for writing less. One is useful with reservations; the other is a trap.

If the matching column has the same name in both tables, USING (column) replaces the ON:

USING has a property ON doesn't have: it merges the common column into one, instead of returning it twice. That's why you can write it unqualified:

In GreenStore USING is almost useless, and not by accident: the schema follows the convention that the primary key is called id and the foreign key <table>_id. Since products.id and order_lines.product_id don't share a name, USING doesn't apply. It only works between two child tables that share the FK's name, like the example above.

NATURAL JOIN goes one step further: it automatically matches on every column that shares a name in both tables, without you saying which.

Zero rows. (COUNT(*) simply counts the rows of the result; it's an aggregate function and it's studied in 04-04. Here we're using it as a measuring instrument, and it's the only time it'll appear in this module.)

Why zero? Because products and categories share two column names: id and name. NATURAL JOIN builds the condition on its own:

That is, it demands that the product and the category have the same identifier and the same name. No pair satisfies that. The query doesn't fail, doesn't warn: it returns a perfectly polite empty result.

And there's something worse than an empty result: a result that changes by itself. If tomorrow somebody adds an active column to categories, that NATURAL JOIN will start matching on active too and will return something else, without anybody having touched the query.

Course rule: NATURAL JOIN is banned. It's a shortcut that saves twenty characters in exchange for making your query's meaning depend on the column names somebody picks in the future. In this schema, on top of that, name exists in categories, suppliers, products, customers and employees: the minefield is already laid.

As soon as there are two tables in play, column names can repeat. And if they repeat, the engine doesn't guess:

Both products and categories have id and name. PostgreSQL doesn't choose for you: it forces you to qualify the column with the name (or alias) of its table.

You could write it all out with the full table name:

It works, but it's unbearably verbose as soon as there are four tables. Table aliases solve that:

Notice that two different kinds of alias are needed here, and it's worth not confusing them:

Without the column alias, the result would have two columns called name, and neither you nor your application would know which is which. It's exactly the problem we anticipated in 02-01 when talking about SELECT *.

The course's table aliases. So that every query in the syllabus reads the same way, we fix these abbreviations and we'll always use them:

And when the same table appears twice (the self joins of 03-06), aliases stop being a convenience and become mandatory, and we'll use meaningful names: employees AS e and employees AS manager, customers AS c and customers AS referrer.

Two syntax details:

• The AS in table aliases is optional: FROM products p is identical to FROM products AS p. In this course we always write it, for consistency with column aliases.
• Once the alias is defined, the original name stops being usable. If you write FROM products AS p, you can't refer to products.price: it'll give you missing FROM-clause entry for table "products".

Every JOIN shares the mechanism from section 2. What changes is what happens to the rows that find no partner.

In table form, with the typical GreenStore question each one answers:

To those five you add the SELF JOIN, which isn't a sixth type but a technique: using any of the previous ones to join a table to itself, and so walk the reflexive relationships of employees.manager_id and customers.referred_by_id. That's 03-06 too.

A note on vocabulary you'll see in the documentation: INNER JOIN is the inner join; LEFT, RIGHT and FULL are the outer joins, because they keep rows that fall "outside" the matching. Hence their full names are LEFT OUTER JOIN, RIGHT OUTER JOIN and FULL OUTER JOIN; the word OUTER is optional in all three.

Business questions are rarely answered with two tables. Chaining is simply adding one JOIN ... ON ... line per new table, and it works because the result of a JOIN is itself a table that can be joined again.

"Who bought each order line?" The customer's name isn't in order_lines and isn't reachable in one hop: you have to go through orders.

(First 10 of 47 rows.)

Three tables, two ON conditions, and the count is still 47: the same as order_lines has. Notice that the customer's name repeats across the first three rows, because order 1 has three lines. That isn't a mistake: it's the natural consequence of joining through the "many" side of a 1:N relationship, and in 03-02 you'll see why it's the number one cause of module 4's inflated sums.

(First 10 of 47 rows.)

There it finally is: the readable sales detail. What was sold, from which category and for how much. In module 4 we'll add up those amounts by category and answer "which category bills the most?"; for now we stay at the detail rows, which is what this module knows how to produce.

Notice how the amount is calculated following the convention from 02-02: you operate at full precision inside the ROUND and round only when presenting. Line 6 illustrates it: 6 * 1.95 * 0.90 = 10.53.

How to build a chain, in three steps. This method will save you from nearly every mistake:

1. Find the starting point: the table that holds the level of detail you want (one row per order line → order_lines).
2. Trace the path on the ER diagram from 01-06 to every piece of data you need, following the arrows of the foreign keys.
3. Write one JOIN ... ON per hop, in the order of the path, and check the row count at the end.

Here comes the extension of the diagram we've been carrying since 02-01, and it's the most important piece of the lesson for what comes next.

The question it answers is: at what moment are JOINs resolved? Answer: inside the FROM step, before WHERE.

Read it slowly, because three consequences that govern the whole module come out of it:

1. ON and WHERE run at different moments. ON acts while the pairing is being built; WHERE acts afterwards, over the already combined table.

2. In an INNER JOIN that difference doesn't show. Putting o.status = 'delivered' in the ON or in the WHERE gives exactly the same result, because in an INNER JOIN there's no step 1d: the rows with no partner are discarded either way. You'll check it in 03-02.

3. In a LEFT JOIN the difference is enormous. Step 1d reintroduces the partnerless rows after applying the ON, but before applying the WHERE. The result:

It's the most frequent and hardest-to-spot mistake in all of intermediate SQL, and lesson 03-03 demonstrates it with the same query written both ways and its two different results. Keep this diagram to hand when you get there.

One final note on the ordering inside the FROM itself: when you chain several JOINs, they're resolved left to right. A JOIN B JOIN C means (A JOIN B) JOIN C: first A is joined with B, and the result is joined with C. With pure INNER JOINs the order is irrelevant (03-02); with chained LEFT JOINs, it's anything but (03-03).

• Forgetting the ON condition when using the comma syntax. It produces a silent cartesian product: lots of rows, no error. With N tables you need N-1 matching conditions.
• column reference "id" is ambiguous. Two tables in the FROM have a column with that name. Qualify it: p.id, cat.id. It always happens with id and with name in this schema.
• missing FROM-clause entry for table "products". You defined the alias p and then wrote products.price. Once there's an alias, the original name no longer exists for that query.
• Using NATURAL JOIN "because it's shorter". It matches on every same-named column, including the ones somebody adds tomorrow. In products NATURAL JOIN categories it returns 0 rows because of name.
• Matching on the wrong column. ON ol.product_id = o.id compiles perfectly and returns rubbish: you're comparing a product identifier with an order one. Always check that both sides of the equality are talking about the same entity.
• Assuming a JOIN never changes the number of rows. It's only preserved when you join through a mandatory FK against a PK. If the FK allows NULL, you lose rows (03-03); if the right side has several rows per left-hand one, you multiply them (03-02).
• Mixing old and modern syntax in the same query. FROM a, b JOIN c ON ... is legal and it's a precedence minefield. Don't do it.
• Tip: write the JOIN before the SELECT. Build the FROM with its chains first, run it with SELECT * and LIMIT 5 to see the shape of the result, and only then choose the columns.
• Tip: count the rows after every JOIN you add. If you add a table and the count shoots up, that table has several rows per previous row. If it drops to zero, your ON condition matches nothing.
• Tip: keep the ER diagram from 01-06 open. JOINs are paths through that diagram. Nobody memorises them; you read them.

Purchasing needs to know what share of the catalogue depends on foreign suppliers. Write a query that returns, only for products whose supplier isn't from Spain: the product's id and name, its price, the supplier's name and their country. Sort by country and, within each country, by product id.

Then answer: why does the condition on the country go in the WHERE and not in the ON?

This query, written by a colleague, lands on your desk:

1. Rewrite it with the modern JOIN ... ON syntax, clearly separating matching from filtering.
2. What exactly would happen if somebody accidentally deleted the line o.customer_id = c.id in the original version? Work out how many rows it would return.

Without running anything, predict the number of rows each of these queries returns and justify each prediction in one sentence. Then run them and check.

Solution 1

12 rows of the catalogue's 20: 8 products come from the two Spanish suppliers (Huerta del Turia and BioSierra Ibérica) and the other 12 from France, Germany and Portugal.

Why the condition goes in the WHERE: s.country <> 'Spain' doesn't say how the tables are matched —that's already said by p.supplier_id = s.id—, it says which rows of the result we care about. It's the separation of responsibilities from section 4: ON to match, WHERE to filter.

That said, in this particular case putting it in the ON would give the same result, because it's an INNER JOIN and steps 1c and 2 of the logical order behave identically when there are no orphan rows to reintroduce. The distinction becomes critical in the LEFT JOIN of 03-03, and that's why it's worth picking up the right habit from now on.

Solution 2

1. Modern rewrite:

The two delivered orders from French customers. The rewrite makes visible what the old version hid: a single matching condition and two filtering ones.

2. If o.customer_id = c.id is deleted:

The query becomes a cartesian product filtered only by country and status. The arithmetic:

• Customers from France: 2 (Camille Dubois and Julien Moreau).
• Orders with status delivered: 14.
• Resulting rows: 2 × 14 = 28.

And they'd be 28 false rows: every delivered order would appear associated with both French customers, including the orders that in reality belong to Lucía, to Sofia or to Tiago. No error, no warning, a completely invented report. It's exactly the danger from section 5.

Solution 3

Case d) is the most instructive of the five and the reason lessons 03-03 and 03-04 exist: if management asks you for "the list of orders with their sales rep" and you hand over 10 rows out of 20, you've lost half the business without noticing.

You now have the module's complete conceptual frame:

• Normalisation splits apart so you can store without redundancy; the JOIN puts back together so you can query. They're complementary, not contradictory.
• A JOIN is a cartesian product filtered by a condition. That mental model —combine everything with everything and keep what matches— explains the behaviour of every type of JOIN, even though the engine executes it far more efficiently.
• The condition goes in ON and nearly always takes the form FK = PK. You have the table of GreenStore's eleven canonical conditions to look up whenever you're unsure.
• The modern JOIN ... ON syntax replaces the old comma form: it separates matching from filtering, prevents the accidental CROSS JOIN and is the only one that supports outer JOINs.
• USING only helps when the columns share a name (rare in this schema) and NATURAL JOIN is banned: in products NATURAL JOIN categories it returns 0 rows because of the name column.
• Table aliases are mandatory in practice: without them come the column reference "id" is ambiguous errors. The course's are fixed: p, cat, s, c, e, o, ol, r, rt.
• You know the overview of the five types and you know the only difference between them is what happens to the rows that find no partner.
• You know how to chain three or more tables by following the ER diagram, with N-1 ON conditions for N tables, and you have the two paths you'll use all course long: order_lines → orders → customers and order_lines → products → categories.
• And you know that, in the logical order, JOINs are resolved inside the FROM step, before the WHERE: that's where the critical difference between putting a condition in ON or in WHERE will come from.

In the next lesson, INNER JOIN, we'll go down into the detail of the type you've just used without naming it: which rows it keeps, which it loses and why. You'll watch customers 13, 14 and 15 disappear when joining customers with orders, and the 10 web orders disappear when joining orders with employees. Those disappearances, far from being a fault, are the very definition of the INNER JOIN, and understanding them is what makes the LEFT JOIN of 03-03 make sense.