Three tables and self-joins

How do you join more than two tables?

Add one JOIN ... ON per extra table. Each join takes the rows built so far and pairs them with rows of the next table. FROM orders o JOIN order_items i ON i.order_id = o.id gives one row per item, with the order's date attached. Adding JOIN products p ON p.id = i.product_id gives each of those rows its product name and price.

Every ON should connect the new table to a table that is already in the query. Read a chain of joins as a path through the schema: orders, then the items in them, then the products those items are. If you sketch the schema with arrows, the joins follow the arrows.

Open the Rows view on the query below. There are two JOIN steps now. Watch the row count: six orders become ten rows at the first join, one per item, and stay ten at the second, because each item has exactly one product.

SELECT o.id AS order_id, o.ordered_on, p.name, i.qty
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
ORDER BY o.id;

How do you join through a linking table?

Orders and products are many-to-many: an order holds several products and a product turns up in many orders. The linking table, order_items, holds one row per pairing, with the quantity. To get from an order to product names you go through it: orders to order_items, then order_items to products.

Skipping the linking table is not possible, because orders have no product column and products have no order column. If a query claims to join orders directly to products, it is matching unrelated ids, the same bug as joining on two primary keys.

The linking table also carries facts that belong to the pairing. qty is the classic one. Multiply it by the price and you have a line total for each item, which you can then sum per order.

SELECT o.id AS order_id, SUM(i.qty * p.price_pence) AS goods_pence
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
GROUP BY o.id;

Does the order of the joins matter?

For inner joins, the result is the same whichever order you write them in, as long as each ON only uses tables that are already joined. Write them in the order that reads naturally, usually following the question: start from the thing you want one row of, then add what describes it.

Once a LEFT JOIN is involved, the order changes the result. The left join keeps everything built so far. A plain JOIN after it can then drop the padded rows, because NULL matches nothing. If you need a left join in the middle of a chain, the later joins that hang off the left-joined table usually need to be left joins too.

What is a self-join?

Sometimes a foreign key points back into its own table. In this bakery, customers.referred_by holds the id of the customer who recommended them. Ben Cole's referred_by is 1, so Ana Ruiz referred him. To show both names on one row, you join customers to itself.

A self-join needs two different aliases, because SQL has to tell the two copies apart: customers c for the customer and customers r for the referrer. The ON then reads like the relationship: r.id = c.referred_by, the referrer is the customer whose id appears in this customer's referred_by.

Name the aliases after their roles. c and r, or cust and ref, make the query readable. Two copies called c1 and c2 invite exactly the mix-up in the Break It exercise, where the condition compared each customer with themselves.

SELECT c.name AS customer, r.name AS referred_by
FROM customers c
JOIN customers r ON r.id = c.referred_by;

How do you keep customers nobody referred?

The self-join above lists three customers. Ana and Ella came on their own, so their referred_by is NULL, they match no referrer, and the inner join drops them. Switch to LEFT JOIN and they stay, with NULL in the referrer column.

You can also turn the self-join around to count referrals. Join each referrer to the customers they referred and group by the referrer: Ana referred two people, Chloe one. Use a left join from the referrer side if you want people who referred nobody to appear with 0.

SELECT r.name AS referrer, COUNT(c.id) AS referrals
FROM customers r
LEFT JOIN customers c ON c.referred_by = r.id
GROUP BY r.id
ORDER BY referrals DESC;

Why do totals grow after an extra join?

This is the trap that produces confident, wrong numbers. Each order has one delivery_pence. Join orders to order_items and each order appears once per item. Order 6 has two items, so it appears twice, and so does its 250 delivery charge. Sum the delivery column now and order 6 counts as 500.

The rule: a join to a "many" table repeats every column from the "one" side. Counting or summing those repeated columns multiplies them. Join two different many tables to the same row, such as a customer's orders and a customer's reviews, and each order is repeated once per review as well, so both totals inflate.

Watch it happen in the Rows view with the query below. The JOIN step to order_items shows order 6 twice. The two totals disagree: right_total is 750 overall and wrong_total is 1000.

SELECT SUM(o.delivery_pence) AS wrong_total,
  (SELECT SUM(delivery_pence) FROM orders) AS right_total
FROM orders o
JOIN order_items i ON i.order_id = o.id;

How do you avoid the multiplication?

Keep each total at its own level. A few practical habits cover most cases.

• Only join what the question needs. Delivery charges live on orders, so a delivery total needs orders and nothing below it.
• Sum a column from the deepest table you joined. After joining items, SUM(i.qty) is safe because each item row appears once.
• Use COUNT(DISTINCT o.id) to count orders after joining their items, since plain COUNT counts item rows.
• When you need totals from two different many tables, run two queries, or work out each total separately first. The lessons on subqueries and WITH show how to do that in one statement.
• Check a number by hand. Pick one customer, add up their delivery charges from orders alone, and compare.

How do you check a query with several joins?

Build it one join at a time. Run the first two tables and count the rows. Add the next join and count again. The count should only grow when you join towards a "many" side, and you should be able to say why each time. A count that jumps when you expected it to stay put means a join is multiplying rows.

When AI tools write multi-join queries, the usual faults are a join on the wrong pair of columns, a self-join with the condition backwards, and a SUM over a column that the joins have repeated. Read every ON line, and for every SUM ask which table the column comes from and whether anything joined after it could repeat it.

Field notes

• Add one JOIN ... ON for each extra table you need.
• A many-to-many link goes through its linking table, such as order_items.
• For inner joins, write them in any order as long as each ON uses joined tables.
• Joining to a "many" table repeats the row above it, which can inflate sums.

Easy to get wrong

Orders and products are many-to-many. The only path between them runs through order_items.

The referrer's primary key must match the customer's referred_by. Comparing the two referred_by columns pairs people who share a referrer.

The extra join repeats each order once per item, so the delivery charge is summed several times.

After joining items, each order appears once per item. DISTINCT counts each order once.

Questions people ask

Is there a limit to how many tables I can join?

SQLite allows 64 tables in one join. Real queries rarely need more than five or six; beyond that, check whether every table is earning its place.

Does a self-join copy the table?

No. The database reads the same table twice under two names. Nothing is duplicated on disk.

Can I mix LEFT JOIN and JOIN in one query?

Yes. Watch the order: a plain join after a left join can drop the rows the left join kept.

How do I spot row multiplication quickly?

Count the rows before and after each join. If the count rises when you join something you only wanted to look at, the totals above it are now repeated.