Finding What Is Missing
LEFT JOIN plus IS NULL.
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.

- 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.
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;โโโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโ โ 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.
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;โโโโโโโโโโโโโโโโโ โ 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.
Customers who never ordered, counted per country
Show hint
LEFT JOIN ... WHERE o.order_id IS NULL, then GROUP BY
Show solution
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;โโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโ โ 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