How to join multiple tables in SQL
By the Analistable team · Updated · 5 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.
A worked example of double counting
Add a tickets table (T-1 and T-2 for C-101, T-3 for C-102) and join it alongside orders in one go:
-- Wrong: orders and tickets multiply each other
SELECT c.name, SUM(o.amount) AS total, COUNT(t.ticket_id) AS tickets
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
LEFT JOIN tickets t ON t.customer_id = c.customer_id
GROUP BY c.name;Bakery Lune has 2 orders and 2 tickets, so the join produces 2 × 2 = 4 rows: the total comes out as 400 instead of 200, and the ticket count as 4 instead of 2. Aggregate each child table first:
WITH o AS (SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total
FROM orders GROUP BY customer_id),
t AS (SELECT customer_id, COUNT(*) AS tickets
FROM tickets GROUP BY customer_id)
SELECT c.name,
COALESCE(o.orders, 0) AS orders,
COALESCE(o.total, 0) AS total,
COALESCE(t.tickets, 0) AS tickets
FROM customers c
LEFT JOIN o ON o.customer_id = c.customer_id
LEFT JOIN t ON t.customer_id = c.customer_id
ORDER BY c.name;| name | orders | total | tickets |
|---|---|---|---|
| Bakery Lune | 2 | 200 | 2 |
| Hart & Co | 0 | 0 | 1 |
| Nordic Supply | 1 | 75 | 0 |
Filters in ON versus WHERE
With a LEFT JOIN, where you put a condition changes the row count. Moving the country filter into the join keeps all four orders and simply leaves name empty where the customer isn't French:
SELECT o.order_id, c.name
FROM orders o
LEFT JOIN customers c
ON c.customer_id = o.customer_id AND c.country = 'France';| order_id | name |
|---|---|
| O-9001 | Bakery Lune |
| O-9002 | NULL |
| O-9003 | Bakery Lune |
| O-9004 | NULL |
Written as WHERE c.country = 'France', the same query returns only O-9001 and O-9003.
Troubleshooting multi-table joins
| Symptom | Cause | Fix |
|---|---|---|
| Rows vanish after adding a join | An INNER JOIN after a LEFT JOIN, or a WHERE on the right table | Use LEFT JOIN throughout the chain; move filters into ON |
| Totals too high | Two one-to-many joins from the same parent | Aggregate in CTEs, then join |
| “ambiguous column name” | The column exists in two tables | Prefix it with the alias |
| No matches at all | Key types differ (text vs integer) or padded with spaces | CAST and TRIM both sides in the ON clause |
Build the query one join at a time and run SELECT COUNT(*) after each step. A left join from orders should still return 4 rows; if it returns more, the table you just joined has duplicate keys. The same chain in pandas and R is shown in merging data in pandas, R and SQL.
Join order and readability
For inner joins the database's optimiser chooses the physical order, so write them in the order that reads best. With outer joins the written order changes the result. A reliable pattern:
- Start
FROMthe table whose rows define the report (orders for a sales report, customers for a customer list). - Add lookup tables (customers, products) with LEFT JOIN if any key might be missing.
- Join pre-aggregated child tables last, from CTEs.
- Use
USING (customer_id)when the key has the same name in both tables, which also returns the key once instead of twice. DuckDB, PostgreSQL, MySQL and SQLite support it; SQL Server does not.
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.
- Should I filter in the ON clause or the WHERE clause?
- With LEFT JOIN, a condition on the right table in WHERE removes unmatched rows; put it in ON to keep them. With INNER JOIN both give the same result.