Product Sales per Category, Unsold Included

The difference on four rows

The Furniture category has four products. Join them to order_items with a LEFT JOIN:

SELECT p.product_name, oi.order_id
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
WHERE p.category = 'Furniture'
ORDER BY p.product_id;

You get four rows. Monitor Stand and Office Chair show their order ids (11 and 10), and Desk Lamp and Standing Desk show NULL because nobody ordered them. Change LEFT JOIN to INNER JOIN and only the two ordered products come back. That's the whole rule: an INNER JOIN drops left rows with no match, a LEFT JOIN keeps them.

Count the rows each join keeps

The store has 20 products and 19 order lines. Count the joined rows:

SELECT COUNT(*) FROM products p
JOIN order_items oi ON oi.product_id = p.product_id;

The INNER JOIN returns 19 rows, one per order line. The same query with LEFT JOIN returns 28: the 19 matches plus one NULL row for each of the 9 products that never sold. Notice that 28 is more than the 20 products you started with. A LEFT JOIN guarantees at least one row per left row, not exactly one, so Wireless Mouse shows up four times because it's on four order lines.

Why the INNER JOIN answer looks right

Run the practice question with an INNER JOIN and you still get three categories with plausible numbers:

SELECT p.category,
       COUNT(DISTINCT p.product_id) AS product_count,
       COUNT(DISTINCT oi.product_id) AS products_ordered
FROM products p
INNER JOIN order_items oi ON oi.product_id = p.product_id
GROUP BY p.category
ORDER BY p.category;

product_count comes back as 1, 8 and 2, the same as products_ordered. The real catalog has 5, 11 and 4. Nothing errors, and every category still appears because each one sold something. The bug only shows if you already know the catalog size, so it's easy to ship without noticing.

COUNT(*) after a LEFT JOIN

Once you switch to a LEFT JOIN, COUNT(*) counts the NULL rows too. Accessories gets 5 rows from the join but has only 1 order line, so COUNT(*) would report 5 sales. Count a column from the right table instead, such as COUNT(oi.id), which skips NULLs and returns 1. The same goes for SUM: it returns NULL for a group with no matches, so wrap it in COALESCE(..., 0).

The WHERE clause trap

A filter on the right table in WHERE quietly turns a LEFT JOIN back into an INNER JOIN. Say you want delivered orders per Electronics product:

SELECT p.product_name, COUNT(o.order_id) AS delivered_orders
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = oi.order_id
WHERE p.category = 'Electronics' AND o.status = 'delivered'
GROUP BY p.product_id
ORDER BY p.product_id;

This returns 6 of the 11 Electronics products. The unsold ones have NULL status, and NULL = 'delivered' isn't true, so WHERE removes them. Headphones (only a pending order) and Keyboard Mechanical (only a shipped one) vanish too. Move the condition into the join:

SELECT p.product_name, COUNT(o.order_id) AS delivered_orders
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.product_id
LEFT JOIN orders o ON o.order_id = oi.order_id AND o.status = 'delivered'
WHERE p.category = 'Electronics'
GROUP BY p.product_id
ORDER BY p.product_id;

Now all 11 come back, five of them with 0. The filter on the left table (category) stays in WHERE. The filter on the right table (status) goes in ON.

What to say in the interview

Start with the business question: do rows without a match belong in the answer? If yes, LEFT JOIN from the table that must be complete. Then mention the follow-ups before you're asked: count a right-side column instead of *, COALESCE the sums, and keep right-table filters in ON. To list only the rows with no match, add WHERE oi.id IS NULL, the anti-join used in Customers With No Orders. For more practice, see the SQL JOIN interview questions and Join Four Tables.