Joining three or more tables

SQL allows chaining as many JOINs as needed. Each JOIN adds one table to the query.

Three-table join example

Employee → Department → Location:

SELECT
  e.first_name,
  e.last_name,
  d.name   AS department,
  l.city   AS city
FROM employees    e
JOIN departments  d ON d.id  = e.department_id
JOIN locations    l ON l.id  = d.location_id;

Four-table join example (store)

Customer → Order → Order Item → Product:

SELECT
  c.first_name   AS customer,
  o.order_date,
  p.name         AS product,
  oi.quantity,
  oi.unit_price
FROM orders      o
JOIN customers   c  ON c.id  = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id  = oi.product_id
ORDER BY o.order_date, c.last_name;

Key rules

1. Each JOIN has its own ON clause.
2. You can mix JOIN, LEFT JOIN, etc. in the same query.
3. Order of JOINs does not affect the logical result (though it can affect performance).

Readability tips

• Use short, consistent aliases (e, d, o, c, p).
• Align ON conditions vertically so the join structure is obvious at a glance.
• Break across multiple lines — there is no penalty for whitespace.

Many-to-many relationships

A junction table (like order_items) sits between two tables to represent a many-to-many relationship. Always join through it:

customers → orders → order_items → products

Tips

• If a multi-join query returns more rows than expected, a fan-out from a one-to-many join is likely the cause.
• EXPLAIN ANALYZE shows the join strategy PostgreSQL chose and where time is spent.