Scenario: Brightlane's operations team is auditing order completeness and needs the line-item count for every order — including orders with no line items recorded against them.
Task: Write a query to return each order's id and the number of line items recorded against it.
Assumptions:
- Some
ordershave no recorded line items; those orders must still appear in the result with a count of zero.
Output:
- One row per order present in the data, including orders with no line items.
- Columns in this order:
order_id,item_count.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
o.id AS order_id,
COUNT(oi.id) AS item_count
FROM
orders o
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY
o.idThe shape
Some orders have no recorded line items, and the audit needs them to appear with a count of zero rather than drop out entirely. LEFT JOIN keeps every order in the result, and COUNT(oi.id) correctly returns zero for orders whose line-item side is all NULL.
Clause by clause
SELECT o.id AS order_id, COUNT(oi.id) AS item_countreturns each order's ID and its line-item count.COUNT(oi.id)counts non-nulloi.idvalues inside each group — an order with no line items has only one joined row withoi.idas NULL, so the count is zero.FROM orders oreads every order. This is the side that must be preserved, including the orders with no line items.LEFT JOIN order_items oi ON oi.order_id = o.idattaches matching line items to each order. Orders with no matches still appear in the result, with NULLs filled in for everyorder_itemscolumn.GROUP BY o.idcollapses the joined rows down to one per order. Orders with line items get the real count; orders without line items get zero.
Why COUNT(oi.id) and not COUNT(*)
This is the load-bearing distinction for LEFT JOIN counting. COUNT(*) counts every row regardless of NULLs, so an order with no line items — represented in the joined result by one row with NULLs on the order_items side — would be counted as one, not zero. COUNT(oi.id) only counts rows where oi.id is not NULL, which is the correct definition of "has a line item" for this audit.
The trap
Swapping LEFT JOIN for INNER JOIN would drop every line-item-less order from the result entirely, and the audit's whole purpose is to find those orders. The choice between LEFT JOIN and INNER JOIN is set by whether the question needs to see the parents-without-children rows; here it does, so LEFT JOIN is the only shape that fits. Pairing it with COUNT(oi.id) instead of COUNT(*) is the second half of the same decision — the count has to agree with the join about what "no line items" means.
You practiced left-joining the parent table to its children so every parent appears in the result — including parents with zero children, which would drop out of an inner-join shape.
