Mixing Join Types
You'll learn how to combine INNER, LEFT, and RIGHT joins in a single query to handle complex multi-table relationships.
The Core Idea
Sometimes your data story requires pulling from three or more tables, but each relationship has different requirements. You might need all records from one table (LEFT JOIN), but only matching records from another (INNER JOIN). SQL lets you chain multiple join types together—each join operates on the result of everything before it.
How It Works
When you mix join types, SQL processes them left to right, building up the result set step by step:
SELECT customers.name, orders.order_id, payments.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
INNER JOIN payments ON orders.id = payments.order_id
Here's what happens:
- First join (LEFT): All customers appear, even those without orders (orders columns show NULL)
- Second join (INNER): Only rows where payment exists stay in the result
Important: The INNER JOIN on line 3 filters out customers with no orders, despite the LEFT JOIN! Why? Because orders.id will be NULL for customers without orders, and NULL never matches in an INNER JOIN.
Reading the Flow
Think of it like a pipeline: customers → (keep all, add orders) → (keep only those with payments). Each join transforms the intermediate result.
Key Takeaway: When mixing join types, join order matters critically—later INNER JOINs can filter out rows preserved by earlier LEFT JOINs, so visualize the data flow step-by-step to predict your results.