How to JOIN Three Tables in SQL: Real-World Example with Explanation
Step-by-step guide to joining 3+ tables in SQL. Covers INNER JOIN, LEFT JOIN ordering, and common mistakes that cause duplicate rows.
Why Three-Table Joins Confuse People
The syntax is the same as a two-table join — you just chain another JOIN. The confusion comes from knowing which table to join to which, and whether to use INNER or LEFT JOIN at each step.
Example Scenario
Tables: customers (id, name), orders (id, customer_id, total), order_items (id, order_id, product_name, qty). Goal: Get each customer's name, their order total, and the products in each order.
The Query
SELECT c.name, o.total, oi.product_name, oi.qty FROM customers c INNER JOIN orders o ON o.customer_id = c.id INNER JOIN order_items oi ON oi.order_id = o.id ORDER BY c.name, o.id;
INNER vs LEFT JOIN: When It Matters
Use LEFT JOIN when you want to include customers who haven't placed orders yet. Use INNER JOIN when you only want rows that exist in all three tables. Mixing them is valid: LEFT JOIN orders but INNER JOIN order_items.
Common Mistake: Duplicate Rows
If you see more rows than expected, you likely have a one-to-many relationship you didn't account for. Add GROUP BY or use DISTINCT, or reconsider whether you need a subquery/CTE to aggregate first.