AI Hints
Homeโ€บSQL Tutorialโ€บInner and Left Joins
SQL ยท Lesson 46 of 126

LEFT JOIN

Keep every row on the left.

โฑ 2 min readโ–ถ Video at 1:04:39๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

LEFT JOIN
From the video: 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
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;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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
Next up: Finding What Is Missing โ€” LEFT JOIN plus IS NULL