Lesson 16 of 26 · see the practice database

Joining three or more tables, and self joins

Real questions cross several tables. "What did Asha buy?" needs the customer, her orders, the lines of those orders and the products on those lines. That is four tables. The good news is that a join of four tables is just three joins of two, one after another.

The map

Before joining, know how the tables link.

• orders.customer_id points to customers.id
• order_items.order_id points to orders.id
• order_items.product_id points to products.id
• employees.manager_id points to employees.id

order_items sits in the middle, tying orders to products. An order can hold many products, and a product can be in many orders. A table like this, whose job is to link two others, is called a junction table.

Adding one join at a time

Start with two tables and check the result. Then add the third.

SELECT o.id AS order_id, c.name AS customer, p.name AS product, oi.quantity
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE o.id IN (1, 2)
ORDER BY o.id, p.name;

Each JOIN adds one table and has its own ON saying how that table attaches to what is already there. Build big queries this way: run after each added join and look at the row count. If the count jumps unexpectedly, the last join is wrong.

What did one customer buy?

SELECT o.order_date, p.name AS product, oi.quantity
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE c.name = 'Asha Verma'
ORDER BY o.order_date, p.name;

The value of each order

An order's value is the sum of quantity times price over its lines. Join, then group by order.

SELECT o.id AS order_id, o.order_date, c.name AS customer,
       SUM(oi.quantity * p.price) AS total
FROM orders o
JOIN customers c    ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
GROUP BY o.id, o.order_date, c.name
ORDER BY total DESC
LIMIT 5;

Money spent by each customer

Group by the customer instead of the order, and leave out cancelled orders.

SELECT c.name, COUNT(DISTINCT o.id) AS orders,
       SUM(oi.quantity * p.price) AS spent
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC;

Look closely at COUNT(DISTINCT o.id). After joining to the items, an order with three lines appears as three rows. A plain COUNT(*) would count lines, not orders. This is the most common mistake in multi-table reports: joining to a table with many rows per parent multiplies the parent's rows. Whenever you count or add something from a "one" side after joining to a "many" side, stop and check.

Sales by category

SELECT p.category,
       SUM(oi.quantity) AS units,
       SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o   ON oi.order_id = o.id
WHERE o.status = 'delivered'
GROUP BY p.category
ORDER BY sales DESC;

Mixing inner and left joins

To keep the customers with no orders in a spending report, every join from customers onwards must be a left join. One inner join further down the chain would drop them again.

SELECT c.name, COALESCE(SUM(oi.quantity * p.price), 0) AS spent
FROM customers c
LEFT JOIN orders o       ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id = o.id
LEFT JOIN products p     ON oi.product_id = p.id
GROUP BY c.id, c.name
ORDER BY spent DESC, c.name;

The self join

A table can be joined to itself. In employees, each row has a manager_id that points to another row of the same table. To show each person beside their manager's name, use the table twice, under two different aliases, as if it were two tables.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;

Think of e as "the employee" copy and m as "the manager" copy. The aliases are not optional here. Without them the database could not tell the two uses apart.

The head of the shop is missing from that list, because she has no manager to match. A left join keeps her.

SELECT e.name AS employee, COALESCE(m.name, 'no manager') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

How many people report to each manager?

SELECT m.name AS manager, COUNT(*) AS team_size
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY team_size DESC, m.name;

Who earns more than their own manager? A self join can compare the two rows.

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Nobody does in our shop, so the result is empty. This question is an interview favourite.

Pairs within one table

A self join can also pair rows that share something. These are pairs of customers in the same city. The condition a.id < b.id stops each pair from appearing twice and stops a customer from being paired with themselves.

SELECT a.name AS first, b.name AS second, a.city
FROM customers a
JOIN customers b ON a.city = b.city AND a.id < b.id
ORDER BY a.city;

A method for any join query

1. Write down which tables hold the columns you need to show and to filter.
2. Find the path of keys that connects them. Include any table in between, even if you show nothing from it.
3. Start from the table whose rows the result is "about".
4. Add one join at a time, checking the row count.
5. Only then add WHERE, GROUP BY and the rest.

Try it yourself

SELECT DISTINCT p.name FROM customers c JOIN orders o ON o.customer_id = c.id JOIN order_items oi ON oi.order_id = o.id JOIN products p ON oi.product_id = p.id WHERE c.name = 'Ravi Kumar' ORDER BY p.name;

SELECT SUM(oi.quantity * p.price) FROM order_items oi JOIN products p ON oi.product_id = p.id WHERE oi.order_id = 20;

SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.id WHERE m.name = 'Rohit Bansal';

Mark this lesson as done

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.