Multi-Table Queries

Introduction

Real apps regularly join 4–8 tables in one query — orders, users, products, line items, addresses, payments. Doing this cleanly takes practice.

Understanding the topic

Core concepts to understand:

• Join order is logical to writers; the optimizer reorders for cost.
• Always alias every table (u, o, p, oi).
• Use indexes on every join column.
• Filter early in WHERE to shrink intermediate sets.
• Use EXPLAIN to verify the chosen plan.

Syntax reference

Visual workflow / architecture:

SELECT u.email, p.name, oi.qty, o.created_at
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.created_at > now() - interval '30 days';

Real-world use

An e-commerce 'recent purchases' page hits exactly this kind of 4-table join, often in <50ms thanks to indexes.

Best practices

• Alias everything; never repeat full table names.
• Index every join column.
• EXPLAIN to confirm join order and indexes.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. Walk through a 4-table join you wrote.
• Q2. How does the optimizer pick join order?
• Q3. Index strategy for joins.
• Q4. Hash join vs nested loop.
• Q5. When to break a big query into stages.