Joining Three Tables

An order knows a user_id and a product_id. The buyer's name is one table away; the product's name is one table away in a different direction. Getting both means joining both.

There is no new syntax. Joins chain — each JOIN attaches to the result built so far, and you write as many as the question needs.

FROM orders o
JOIN users u    ON u.user_id    = o.user_id
JOIN products p ON p.product_id = o.product_id

Both of these are many-to-one — every order has exactly one buyer and exactly one product — so neither join adds or removes a row. 24 orders in, 24 rows out, now carrying names from two other tables.

Your task: return order_id, the buyer's name as buyer, the product's name as product, and amount, for every order, ordered by order_id.

A join does not only attach to the table immediately above it. It attaches to everything joined so far, so its ON can reference any table named earlier in the FROM clause.

That is what lets you walk a chain of relationships. Here events knows a user, and the user knows a plan — so grouping events by plan means joining through users even though no column of events mentions a plan.

FROM events e
JOIN users u ON u.user_id = e.user_id   -- now u.plan is available

What it cannot do is reference a table named later. The FROM clause is read top to bottom, and a table that has not been joined yet does not exist.

Your task: return each plan, device, and the number of events as n, ordered by plan then device.

Here is what the third table actually buys you. With orders alone you can group by status or by month. Join users and you can group by country. Join products as well and you can group by category and country at once — a question neither table can answer on its own.

This is the everyday shape of analyst SQL: joins to reach the columns, GROUP BY to pick the dimensions, and a HAVING to keep the result readable.

Your task: for paid orders only, return category, country and total revenue, keeping only combinations totalling 100 or more. Order by revenue descending, then category, then country.

This is the most important station in the module, and the failure is entirely invisible.

You learned in 3-02 that users LEFT JOIN orders gives 26 rows, keeping the two users who never ordered. Now chain a plain JOIN to products onto it:

FROM users u
LEFT JOIN orders o  ON o.user_id = u.user_id      -- 26 rows here
JOIN products p     ON p.product_id = o.product_id -- 24 again

The padded rows carry o.product_id = NULL. NULL matches no product, the inner join keeps only matches, and the two users disappear — the LEFT JOIN upstream has been cancelled by the join downstream. No error. No warning. Just 24 where you expected 26.

The rule is worth memorising: once you go LEFT, stay LEFT. An inner join anywhere later in the chain re-filters everything before it.

Your task: prove it to yourself. Return u.name and p.name as product for every user, keeping the two who never ordered, ordered by u.user_id then o.order_id. Both joins have to be LEFT.

A quieter version of the same mistake, and one that reaches production regularly because the result is not empty — it is merely wrong.

Say you want revenue by country, and you also want the user's plan from subscriptions. Joining that table in looks harmless. It is not: two users have no subscription row, so an inner join to subscriptions deletes their orders as well.

orders JOIN users                          -- 24 orders, 9 countries
orders JOIN users JOIN subscriptions       -- 20 orders, 7 countries

Paid revenue drops from 1891.00 to 1424.00. Two countries vanish from the report entirely. Every number that remains is real, which is exactly why nobody catches it.

Your task: the correct version. Return country and paid revenue for every country that has any, ordered by revenue descending then country — without letting subscriptions near it.

Once three tables are joined, the interesting counts are the ones that cross them: not "how many orders" but how many different countries has this product sold into.

COUNT(DISTINCT ...) is what makes that work. A product sold four times in two countries has four rows after the join, and a plain count of countries would say four. DISTINCT collapses them to the two real values.

Keep the chain LEFT so the unsold product still appears. Its padded row has a NULL country, and COUNT(DISTINCT u.country) skips NULLs, so it correctly reports 0 — no COALESCE needed, because COUNT already returns 0 rather than NULL.

Your task: return every product's name, its number of orders as orders, and the number of distinct buyer countries as countries. Order by countries descending, then name.

Nothing changes at four except that there is more to be wrong about. The discipline is the same one from the beginning of Part 3: predict the row count before you run it, then check.

Here that prediction is the lesson. Start from 24 orders, join users (many-to-one, still 24), join products (many-to-one, still 24), then join subscriptions — and land on 20, because of the two users with no subscription. The number tells you something happened that the query text does not.

Your task: return o.order_id, u.name, p.name as product and s.plan for every order whose buyer has a subscription, ordered by order_id.

Every join question decomposes the same way, and doing it explicitly is faster than guessing:

Your task: "every user, their country, and the name of each product they have bought — including users who have bought nothing." Work through the three questions, then write it. Order by u.user_id, then o.order_id.

Columns: name, country, product.

Two tables in this database have a column called name. Three have a user_id. Two have a plan. At three tables, ambiguity stops being a rare annoyance and becomes the default.

Two habits handle it completely. Give every table a short alias, and qualify every column with it — including the ones that are not currently ambiguous, because the next person to add a join changes which ones those are.

Where two columns would land in the result under the same name, rename them with AS. A result with two columns called name is legal in Postgres and miserable to consume.

Your task: return the buyer as buyer, their country as country, the product as product, its category as category and the amount as amount, for paid orders only, ordered by order_id. Every column in the SELECT list qualified.