âš¡IntermediateJoins
Joining Multiple Tables
Chain multiple JOINs to combine data from three or more tables in a single query.
Overview
Joining more than two tables is natural in a normalised database. You simply chain JOIN clauses each JOIN adds one more table to the result. PostgreSQL evaluates them left-to-right by default, though the query planner may reorder them for efficiency. Keep the join conditions clear by using table aliases and explicit ON clauses for each join.
Key Points
- •Chain joins: FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ...
- •Each JOIN introduces a new table use aliases to avoid ambiguity
- •Mix join types: INNER for required relationships, LEFT for optional ones
- •PostgreSQL's planner reorders joins for efficiency automatically
- •Verify row counts when joining: unexpected Cartesian products inflate results
Examples
Joining four tables
SELECT
u.name AS user_name,
o.id AS order_id,
p.name AS product,
oi.quantity
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'shipped';Mixed join types (INNER + LEFT)
-- All orders with their discount code (if any)
SELECT o.id, c.name AS customer, d.code AS discount
FROM orders o
JOIN customers c ON c.id = o.customer_id
LEFT JOIN discount_codes d ON d.id = o.discount_id;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4