Analistable

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

Schema
TableColumns
ordersorder_id, customer_id, product_id, amount
customerscustomer_id, name, country
productsproduct_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;
Result (inner joins)
order_idnameproductamount
O-9001Bakery LuneDesk120
O-9002Nordic SupplyChair75
O-9003Bakery LuneLamp80

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;
Result
nameorderstotal
Bakery Lune2200
Hart & Co00
Nordic Supply175

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;
Correct result
nameorderstotaltickets
Bakery Lune22002
Hart & Co001
Nordic Supply1750

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';
Result
order_idname
O-9001Bakery Lune
O-9002NULL
O-9003Bakery Lune
O-9004NULL

Written as WHERE c.country = 'France', the same query returns only O-9001 and O-9003.

Troubleshooting multi-table joins

Symptom, cause and fix
SymptomCauseFix
Rows vanish after adding a joinAn INNER JOIN after a LEFT JOIN, or a WHERE on the right tableUse LEFT JOIN throughout the chain; move filters into ON
Totals too highTwo one-to-many joins from the same parentAggregate in CTEs, then join
“ambiguous column name”The column exists in two tablesPrefix it with the alias
No matches at allKey types differ (text vs integer) or padded with spacesCAST 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 FROM the 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.

Related guides