How to join multiple tables in SQL
By the Analistable team · Updated · 2 min read
Chain one JOIN … ON per extra table: FROM orders o JOIN customers c ON c.customer_id = o.customer_id JOIN products p ON p.product_id = o.product_id. Each join adds columns from one more table. Use LEFT JOIN for tables that may have no match, and once you've used a LEFT JOIN, keep later joins to that table as LEFT JOINs too, or the unmatched rows disappear again.
Part of our guide: How to merge data in pandas, R and SQL
Three tables
| Table | Columns |
|---|---|
| orders | order_id, customer_id, product_id, amount |
| customers | customer_id, name, country |
| products | product_id, product |
SELECT o.order_id, c.name, p.product, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
ORDER BY o.order_id;| order_id | name | product | amount |
|---|---|---|---|
| O-9001 | Bakery Lune | Desk | 120 |
| O-9002 | Nordic Supply | Chair | 75 |
| O-9003 | Bakery Lune | Lamp | 80 |
O-9004 belongs to customer C-104, who isn't in customers, so the inner join drops it.
Keep unmatched rows with LEFT JOIN
SELECT o.order_id, c.name, p.product, o.amount
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN products p ON p.product_id = o.product_id;Now O-9004 appears with name NULL. Watch for this mistake: a WHERE c.country = 'France' after a LEFT JOIN removes the NULL rows and turns it back into an inner join. Put such conditions in the ON clause if you want to keep unmatched rows.
Aggregating across joins
Count orders per customer, including customers with none:
SELECT c.name, COUNT(o.order_id) AS orders, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;| name | orders | total |
|---|---|---|
| Bakery Lune | 2 | 200 |
| Hart & Co | 0 | 0 |
| Nordic Supply | 1 | 75 |
COUNT(o.order_id) counts only matched orders; COUNT(*) would count Hart & Co's NULL row as 1.
Avoid double counting
- Joining two “many” tables to the same parent (orders and support tickets per customer) multiplies rows: 3 orders × 2 tickets = 6 rows, and SUM(amount) triples. Aggregate each table in a subquery first, then join the results.
- Check row counts after each join while building the query.
- Join on full keys: if a key has two columns (store and SKU), join on both.
Analistable generates read-only SQL like this from a question in plain language and runs it on your spreadsheets in the browser. Two-table basics and UNION: combining two tables in SQL.
Readable multi-table queries
Use short table aliases, put each join on its own line, and qualify every column with its alias (o.amount, not amount). It prevents “ambiguous column” errors and makes it obvious which table each value comes from.
Frequently asked questions
- How do I join three tables in SQL?
- Add one JOIN … ON clause per table: FROM a JOIN b ON … JOIN c ON ….
- Does the order of joins matter?
- For inner joins, not for the result. With LEFT JOINs it does: start from the table whose rows you want to keep.
- Why does my SUM double after joining?
- A one-to-many join repeats parent rows. Aggregate the many-side tables before joining them.