Multiple table joins
You can chain several joins to combine three or more tables in one query.
In this page:
Syntax
SELECT columns
FROM table1
JOIN table2 ON table1.key = table2.key
JOIN table3 ON table2.key = table3.key;Multiple table joins
Add one JOIN ... ON per extra table, each linking to something already joined. Order matters for readability but the planner chooses the actual execution order.
Watch for join fan-out: joining one-to-many tables multiplies rows, which can inflate aggregates.
Note:
Check row counts after each join when debugging results.
Example: Multiple table joins
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, qty INTEGER);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bob');
INSERT INTO orders VALUES (10, 1), (11, 2);
INSERT INTO products VALUES (100, 'Pen', 3), (101, 'Lamp', 45);
INSERT INTO order_items VALUES (10, 100, 5), (10, 101, 1), (11, 100, 2);
SELECT c.name, p.name AS product, oi.qty, oi.qty * p.price AS line_total
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 p.id = oi.product_id
ORDER BY o.id, p.id;
-- Output:
-- name | product | qty | line_total
-- Ada | Pen | 5 | 15
-- Ada | Lamp | 1 | 45
-- Bob | Pen | 2 | 6Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Multiplying rows by joining several one-to-many tables
- Missing an ON condition
- Ambiguous column names
Chapter Summary
- Chain JOIN ... ON clauses
- Each join links to a joined table
- Watch out for row multiplication
- Alias every table
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: