Joining Multiple Tables in SQL
By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17
Before this INNER JOIN, LEFT JOIN and RIGHT JOIN
Builds toward Multi-CTE Query Architecture, Join Fanout and Aggregate Correctness, NULL Propagation in Complex Queries
How do you join multiple tables in SQL?
You can chain as many JOINs as you need in a single query, adding one table at a time.
Most reporting queries need more than two tables. A full order report needs customer names, product names, quantities, and order totals — each piece of data in a different table. You add one JOIN per table, each with its own ON clause. Each new table connects to something already in the query: to the driving table, or to a table you already joined.
How do you join three tables in one SQL query?
The approach scales to as many tables as you need. Here is what three tables joined together looks like: orders, customers, and order line items in one query:
SELECT o.id AS order_id, c.name AS customer_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_idThe first JOIN connects orders to customers via customer_id. The second JOIN connects orders to order_items via order_id. Each JOIN adds columns from a new table, and the query now has all three tables' data in each result row.
Add another JOIN to pull in product names:
SELECT o.id AS order_id, p.name AS product_name, oi.quantity, oi.unit_price
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.idHere the second JOIN connects to order_items, not to orders — because order_items has the product_id foreign key. Each ON clause specifies which columns match between two tables already in the query.
Can you mix INNER JOIN and LEFT JOIN in one query?
You can mix JOIN types. Some tables in the chain may have nullable foreign keys. Categories might be missing for some products; use LEFT JOIN for those:
SELECT c.name AS customer_name, p.name AS product_name, cat.name AS category_name FROM orders o JOIN customers c ON o.customer_id = c.id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id LEFT JOIN categories cat ON p.category_id = cat.id
Products with no category still appear. Their category_name column is NULL. The three INNER JOINs before it ensure orders, customers, line items, and products are all matched; the LEFT JOIN only relaxes the constraint for categories.
What goes wrong when a JOIN ON clause is wrong?
The one thing that trips people up: getting the ON clauses wrong.
Each JOIN's ON clause must connect a column from the new table to a column from a table already in the query. A common mistake is connecting two tables that don't share a direct relationship — JOIN products p ON p.id = c.id accidentally matches product IDs to customer IDs. Check your foreign key relationships before writing the ON clause, and verify that the row counts in the result look reasonable before relying on the output.
Practice Joining Multiple Tables in SQL
Brightlane's sales team needs a line-item report showing each order alongside the customer who placed it and the quantity of each item in that order.
Write a query to return the order ID, customer name, and quantity for every order line.
Assumptions:
- The result row count is one row per order line item (not one row per order).
Output:
- One row per order line, with columns
order_id,customer_name, andquantity.
Schema · ecommerce5 tables? = nullable
Run previews · Check grades
Write a query, then run it to see results here.
The full breakdown walks through the shape, each clause, why this approach beats the alternatives, and the trap to avoid.
See the full worked solution9 Joining Multiple Tables practice problems
Write a query to return the order ID, customer name, and quantity for every order line.
Write a query to return the order ID, product name, and quantity for every line item on record.
Write a query to return the employee name, department name, and salary amount for every salary record on file.
Write a query to return the customer name, product name, quantity, and unit price for every line item.
Write a query to return the customer name, product name, and category name for every line item where the product has a resolving category.
Write a query to return the customer name, product name, and unit price for every item in a delivered order.
Write a query to return the employee name, department name, and salary amount for every salary record belonging to an Engineering employee.
Write a query to return the customer name, order ID, and item ID for every row in the result.
Write a query to return the customer name, product name, category name, and quantity for every line item.
Start learning to practice all 9 Joining Multiple Tables problems, with instant grading and mastery tracking.
Common questions about Joining Multiple Tables
Does the order you write the JOINs in change the result?
Not for inner joins, which return the same rows whichever order you chain them in. The order matters for readability, and it matters a great deal once outer joins are involved, because a LEFT JOIN placed after a filter behaves differently from one placed before it.
Does each JOIN have to connect back to the first table?
No. Each ON clause has to reference something already in the query, which can be any table joined so far. Line items attach to orders and products attach to line items, so the chain reaches products without products ever naming orders.
How do you know a multi-table join is correct?
Count the rows before you aggregate anything. If joining to a second table multiplies the row count, any total computed over that result is inflated, and the number will look plausible while being wrong. Check the count first and the totals afterwards.
