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

Finding What Is Missing

LEFT JOIN plus IS NULL.

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

Now the clever part. If a left join fills in nulls when there's no match, then we can find the unmatched rows by looking for those nulls. This is one of the most useful patterns in all of SQL.

Finding What Is Missing
From the video: Finding What Is Missing
  • NULL means no match
  • So look for the NULLs
  • A pattern you'll use forever

Customers who never ordered

Left join customers to orders, then keep only rows where the order ID is null. Those are customers with no orders at all. Run it. Here are five of them. Helen's welcome discount email list.

query.sqlsql
SELECT c.customer_id, c.first_name, c.last_name, c.joined
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
LIMIT 5;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customer_id โ”‚ first_name โ”‚ last_name โ”‚   joined   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 4           โ”‚ Daniel     โ”‚ Ali       โ”‚ 2024-08-19 โ”‚
โ”‚ 5           โ”‚ Daniel     โ”‚ Ali       โ”‚ 2023-02-17 โ”‚
โ”‚ 9           โ”‚ Nina       โ”‚ Silva     โ”‚ 2023-11-18 โ”‚
โ”‚ 16          โ”‚ Finn       โ”‚ Rossi     โ”‚ 2025-01-18 โ”‚
โ”‚ 22          โ”‚ Yara       โ”‚ Novak     โ”‚ 2024-11-05 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

How many?

And how many are there in total? Same pattern, but count them. Run it. Thirty seven customers have never ordered. That matches the gap we found with the inner join.

query.sqlsql
SELECT COUNT(*) AS never_ordered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ never_ordered โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 37            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Where do they live?

Your turn. For each country, how many customers have never ordered? Show the country and the count, biggest first. Pause and try. Here's a solution. The same left join and is null pattern, then group by country.

โœ๏ธ Exercise: Where do they live?

Customers who never ordered, counted per country

Show hint

LEFT JOIN ... WHERE o.order_id IS NULL, then GROUP BY

Show solution
solution.sqlsql
SELECT c.country, COUNT(*) AS never_ordered
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
GROUP BY c.country
ORDER BY never_ordered DESC;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  country  โ”‚ never_ordered โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ USA       โ”‚ 11            โ”‚
โ”‚ UK        โ”‚ 8             โ”‚
โ”‚ Ireland   โ”‚ 5             โ”‚
โ”‚ Canada    โ”‚ 5             โ”‚
โ”‚ Australia โ”‚ 5             โ”‚
โ”‚ Germany   โ”‚ 3             โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Left join, then where the right side is null, finds rows with no match. You'll use this to find unsold products, inactive users, and missing data. Next, joining more than two tables.

Key takeaways
  • LEFT JOIN ... IS NULL
  • Finds rows with no match
  • Unsold, inactive, missing
Next up: Joining Three Tables โ€” order lines with book details