Finding What's Missing

LEFT JOIN marks the rows without a match with NULLs, and NULLs are easy to find with IS NULL. Together, they answer questions about what's missing. Which customers have never ordered anything?

SELECT c.name, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL
ORDER BY c.name;

Step by step: the LEFT JOIN gives each customer with each of their orders, and each customer without an order once, with NULLs. That's the result from the previous exercise. WHERE then keeps only the rows where o.id is NULL, and those are exactly the customers for whom no order was found.

Which column of the right table should you test? One that's never NULL in a row that did find a match: its primary key, or a column with NOT NULL. order_items and reviews have no id, so test, for example, i.order_id or r.stars. Don't pick a column that can be NULL anyway, such as a review's comment: you'd also get the games that do have a review, just one without a comment.

Typical mistake: WHERE o.id = NULL. As you know from lesson 3, = NULL is never true, so the query finds nothing. It also finds nothing when you write JOIN instead of LEFT JOIN: a JOIN has no rows with NULLs, because it drops the rows without a match before WHERE ever sees them.

The opposite question, which customers have ordered something, needs just JOIN. Each customer would be in the result once for each order, so add DISTINCT: SELECT DISTINCT c.name FROM customers AS c JOIN orders AS o ON ….