Keyword
LEFT JOIN
Keep every row of the left table, even when nothing matches on the right.
SELECT c.email, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;
A customer without orders still appears, with NULL in the order columns. A condition on the right
table belongs in ON; in WHERE it removes those rows again and turns the join into an inner one.
SELECT c.name, COUNT(p.product_id) AS products FROM categories c LEFT JOIN products p ON p.category_id = c.category_id GROUP BY c.name ORDER BY products, c.name LIMIT 6;
| name | products |
|---|---|
| Accessories | 0 |
| Coffee | 0 |
| Drinks | 0 |
| Gift boxes | 0 |
| Tea | 0 |
| Coffee gift boxes | 2 |