LEFT JOIN
Keep every row on the left.
Helen wants to email the customers who've never ordered, with a welcome discount. But an inner join hides exactly those people. We need a join that keeps every customer, even the ones with no orders. That's a left join.

- Email people who never ordered
- INNER JOIN hides them
- LEFT JOIN keeps them
Keep everyone
A left join keeps every row from the left table, which is the one after from. If a customer has matching orders, they're joined as usual. If not, the order columns are filled with null. Run it. We sorted by order ID, and nulls come first, so you can see customers with an empty order ID. They've never placed an order.
SELECT c.first_name,
c.last_name,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY o.order_id
LIMIT 4;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโ โ first_name โ last_name โ order_id โ โโโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโโโค โ Daniel โ Ali โ โ โ Daniel โ Ali โ โ โ Nina โ Silva โ โ โ Finn โ Rossi โ โ โโโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโโโ
Inner versus left
Here's the difference. Inner join keeps matched rows only. Left join keeps every row from the left table, and fills in nulls when there's no match. When you're not sure, ask yourself, do I want to lose the rows that have no partner?
INNER JOIN
- Matches only
- Unmatched rows dropped
- Who has ordered?
LEFT JOIN
- Every left row
- NULLs when no match
- Everyone, and their orders
Counting with a LEFT JOIN
Left join is great for counting things, including zeros. Here we count orders for every customer. Notice we count o dot order ID, not star, so customers with no orders get zero instead of one. Run it. The most loyal customers have eight orders each.
SELECT c.first_name,
c.last_name,
COUNT(o.order_id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY orders DESC
LIMIT 5;โโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโ โ first_name โ last_name โ orders โ โโโโโโโโโโโโโโผโโโโโโโโโโโโผโโโโโโโโโค โ Sam โ Costa โ 8 โ โ Diego โ Ali โ 8 โ โ Ella โ Costa โ 8 โ โ Vera โ Smith โ 8 โ โ Bruno โ Costa โ 8 โ โโโโโโโโโโโโโโดโโโโโโโโโโโโดโโโโโโโโโ
Summary
Left join keeps every row from the left table, and fills gaps with null. Next, how to use those nulls to find what's missing.
Key takeaways- LEFT JOIN keeps the left table
- Missing matches become NULL
- COUNT(col) gives zeros