SQL Left Join
A left join keeps every row from its left input, even when the right input has no match. It is useful for finding customers who have not ordered or keeping zero counts in a report.
Understand the idea
The customer is the parent row we want to preserve. When no order matches, the order columns are NULL in the result. Count the order's non-nullable identifier to count matches. COUNT(*) would count the retained customer row and incorrectly report one order for a customer without orders.
Where to try it
Run this example in the SQL playground. The lesson link loads the shop tables and sample rows before this query. Select Run when you are ready. Every run starts fresh, so your changes do not affect the website or a previous run.
SQLite sample data · MySQL / MariaDB sample data · See the sample tables
A worked example
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY c.customer_id;
Read the result
Mira has 2 orders, Dev has 1, Asha has 1, and Noor has 0. Noor stays visible even though there is no related order.
Try one change
Count only paid orders while preserving all customers. Predict the four counts before running the changed query.