TopolearnReports must include customers with no orders and products never sold; inner joins silently drop them.
An inner join drops rows that have no partner. Dev has no orders, so he disappears from any customers-orders inner join. An outer join keeps them:
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;LEFT JOIN keeps every row of the left table. Where there is no match, the right table's columns are filled with NULL. RIGHT JOIN is the mirror image (rarely used; swap the tables and write a LEFT JOIN instead), and FULL JOIN keeps unmatched rows from both sides. MySQL has no FULL JOIN.
Those NULLs make outer joins useful for finding what is missing, a pattern called an anti-join:
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- DevTwo classic mistakes:
- Filtering the right table in WHERE.
LEFT JOIN orders o ... WHERE o.status = 'shipped'removes the NULL-extended rows (NULL is not 'shipped'), quietly turning the query back into an inner join. A condition about the optional side belongs inON:LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'shipped'. - **Counting with COUNT(*).** For Dev, the left join produces one row with NULL order columns, so
COUNT(*)says 1. Count a column from the right table,COUNT(o.id), which skips NULLs and gives 0.
When the result reads oddly, ask of each table: must a match exist (inner) or is it optional (outer)?
Example
SELECT c.name,
COUNT(o.id) AS orders, -- not COUNT(*)
COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.status <> 'cancelled' -- optional side: filter in ON
GROUP BY c.id, c.name
ORDER BY spent DESC;
-- name | orders | spent
-- Ana | 2 | 257.50
-- Ben | 2 | 247.99
-- Chloe | 0 | 0
-- Dev | 0 | 0
-- Products never ordered (anti-join)
SELECT p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.product_id IS NULL;
-- (no rows: every product has been ordered)Remember
- LEFT JOIN keeps all left rows, NULL-filling missing matches
- WHERE right_col IS NULL after a LEFT JOIN finds unmatched rows
- Conditions on the optional table go in ON, not WHERE
- Count a right-side column, not *, to get zero for no matches
Watch out for
- A WHERE condition on the right table that turns a LEFT JOIN into an inner join.