Keep the rows with no match
Use LEFT JOIN to keep unmatched rows, and to find what has no partner. About 10 minutes.
LEFT JOIN keeps every left row
A plain JOIN drops rows that have no partner. That's wrong when the question is "every customer, and their orders if they have any".
LEFT JOIN keeps every row of the left table. When a row has no match on the right, it is still in the answer, with NULL in all the right table's columns. Those NULLs are how you spot the unmatched rows.
SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
| id | name |
|---|---|
| 1 | Asha |
| 2 | Ravi |
| 3 | Meera |
| id | customer_id |
|---|---|
| 7 | 2 |
| 8 | 2 |
Asha: no order has customer_id 1. LEFT JOIN keeps her anyway, with NULL for the order.
Finding what is missing, and counting zeros
To find customers with no orders, LEFT JOIN and keep the rows where the order id IS NULL. A matched row has a real id, so only the unmatched survive.
To count orders per customer with zeros included, use COUNT(o.id), not COUNT(*). COUNT(*) counts the unmatched row itself and says 1. And a test on the right table belongs in ON, not WHERE, or the NULL rows are thrown away and it becomes an inner join.
SELECT c.name, COUNT(o.id) AS orders FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name;
A customer has no orders. Which query shows them with an order count of 0?
- ALEFT JOIN, then COUNT(o.id)
- BLEFT JOIN, then COUNT(*)
- CAn inner JOIN, then COUNT(o.id)
Show the answer
LEFT JOIN, then COUNT(o.id). The unmatched row is kept, and COUNT(o.id) skips its NULL id, giving 0.
Customers who never ordered
The customers table has one row per customer and orders has one row per order. List the name of every customer who has never placed an order, A to Z.
customers(id, name, city) orders(id, customer_id, status)
Up to 1,000 customers · names are unique