Real queries often combine three, four or more tables. The method is simple: join them one at a time, each with its own ON condition.
Here is a small shop with four tables:
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20), city VARCHAR(20));
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(20), price DECIMAL(8,2));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT NOT NULL, ordered_on DATE NOT NULL);
CREATE TABLE order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
unit_price DECIMAL(8,2) NOT NULL,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES (1, 'Maya', 'Pune'), (2, 'Rohan', 'Delhi'), (3, 'Sana', 'Pune');
INSERT INTO products VALUES (1, 'Notebook', 80), (2, 'Pen', 15), (3, 'Backpack', 999);
INSERT INTO orders VALUES (1, 1, '2026-05-02'), (2, 1, '2026-05-20'), (3, 2, '2026-05-21');
INSERT INTO order_items VALUES (1, 1, 2, 80), (1, 2, 5, 15), (2, 3, 1, 999), (3, 1, 1, 80), (3, 2, 10, 15);order_items is a link table between orders and products: each order has many items, and each product appears in many orders. This is the many-to-many pattern (Many-to-Many).
Build the query in steps
Step 1: orders with customers.
SELECT o.id AS order_id, c.name AS customer, o.ordered_on
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;+----------+----------+------------+
| order_id | customer | ordered_on |
+----------+----------+------------+
| 1 | Maya | 2026-05-02 |
| 2 | Maya | 2026-05-20 |
| 3 | Rohan | 2026-05-21 |
+----------+----------+------------+
3 rows in setStep 2: add the items.
SELECT o.id AS order_id, c.name AS customer, i.product_id, i.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
ORDER BY o.id, i.product_id;+----------+----------+------------+----------+
| order_id | customer | product_id | quantity |
+----------+----------+------------+----------+
| 1 | Maya | 1 | 2 |
| 1 | Maya | 2 | 5 |
| 2 | Maya | 3 | 1 |
| 3 | Rohan | 1 | 1 |
| 3 | Rohan | 2 | 10 |
+----------+----------+------------+----------+
5 rows in setThe order rows are now repeated, once per item.
Step 3: add the product names.
SELECT o.id AS order_id, c.name AS customer, p.name AS product, i.quantity, i.unit_price, i.quantity * i.unit_price AS line_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
JOIN products p ON p.id = i.product_id
ORDER BY o.id, p.name;+----------+----------+----------+----------+------------+------------+
| order_id | customer | product | quantity | unit_price | line_total |
+----------+----------+----------+----------+------------+------------+
| 1 | Maya | Notebook | 2 | 80.00 | 160.00 |
| 1 | Maya | Pen | 5 | 15.00 | 75.00 |
| 2 | Maya | Backpack | 1 | 999.00 | 999.00 |
| 3 | Rohan | Notebook | 1 | 80.00 | 80.00 |
| 3 | Rohan | Pen | 10 | 15.00 | 150.00 |
+----------+----------+----------+----------+------------+------------+
5 rows in setOne row per order line: who bought what, how many, at which price.
Summarise
Turn the detail into totals with GROUP BY:
SELECT o.id AS order_id, c.name AS customer, COUNT(*) AS line_count, SUM(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, c.name
ORDER BY o.id;+----------+----------+------------+-------------+
| order_id | customer | line_count | order_total |
+----------+----------+------------+-------------+
| 1 | Maya | 2 | 235.00 |
| 2 | Maya | 1 | 999.00 |
| 3 | Rohan | 2 | 230.00 |
+----------+----------+------------+-------------+
3 rows in setAnd by product, across all orders:
SELECT p.name AS product, SUM(i.quantity) AS units_sold, SUM(i.quantity * i.unit_price) AS revenue
FROM products p
JOIN order_items i ON i.product_id = p.id
GROUP BY p.id, p.name
ORDER BY revenue DESC;+----------+------------+---------+
| product | units_sold | revenue |
+----------+------------+---------+
| Backpack | 1 | 999.00 |
| Notebook | 3 | 240.00 |
| Pen | 15 | 225.00 |
+----------+------------+---------+
3 rows in setMixing INNER and LEFT joins
To include all products, even ones never sold, start from products and use LEFT JOIN:
INSERT INTO products VALUES (4, 'Marker', 145);
SELECT p.name AS product, COALESCE(SUM(i.quantity), 0) AS units_sold
FROM products p
LEFT JOIN order_items i ON i.product_id = p.id
GROUP BY p.id, p.name
ORDER BY units_sold DESC, p.name;+----------+------------+
| product | units_sold |
+----------+------------+
| Pen | 15 |
| Notebook | 3 |
| Backpack | 1 |
| Marker | 0 |
+----------+------------+
4 rows in setThe Marker has never been ordered, and still appears with 0. If you then join another table with an inner join, you must be careful: an inner join after a left join can remove those rows again, unless its condition is satisfied by the unmatched rows too.
The order of joins
For inner joins, the order you write them in does not change the result. MySQL’s optimizer chooses the best order itself. For outer joins, the order matters, because the left table is the one kept. Write the joins in the order that tells the story: from the main table outward.
The double-counting trap
The order total could be calculated from orders.total if such a column existed. But after joining items, any order-level column is repeated for each item. Summing it gives a wrong number (Aggregate Pitfalls). Always sum the item-level values, or aggregate in a subquery first.
Habits for big joins
- Alias every table, and prefix every column.
- Join on keys that are indexed on both sides.
- Add tables one at a time and run the query each time, checking the row count.
- Select only the columns you need.
- Look at
EXPLAINfor the join order and the indexes used (EXPLAIN). - Keep a limit on how many tables you join in one query. Ten tables is a sign that the query should be split, or that a view or a summary table would help.