Joining three or more tables without losing your mind

Each JOIN Adds Columns to the Running Result

SQL processes joins left to right (logically). Each JOIN takes the current result set and matches it against the next table. Think of it as progressively widening the row.

SELECT
  o.id          AS order_id,
  c.name        AS customer,
  p.name        AS product,
  oi.quantity,
  oi.unit_price
FROM orders o
JOIN customers c  ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products p   ON oi.product_id  = p.id
WHERE o.created_at >= '2024-01-01';

Join to an Aggregated Subquery

Sometimes you need to join a table to an aggregated result. You can join to a subquery in the FROM clause:

SELECT c.name, c.email, stats.total_spent
FROM customers c
JOIN (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
) stats ON c.id = stats.customer_id
ORDER BY stats.total_spent DESC;

Keep Aliases Consistent

Use one-letter or two-letter aliases based on the table name and stick with them throughout the query. o for orders, c for customers. When you join the same table twice (self-join or two references), use descriptive aliases like o1 and o2.