Explain INNER JOIN vs LEFT JOIN with a practical example.

Detailed explanation

Choose the join by asking: which rows must I not lose? If every customer must appear in a report, the customer table is the left side of a LEFT JOIN.

Example

Customers Asha (2 orders), Ben (1 order) and Chen (no orders):

SELECT c.name,
       COUNT(o.order_id)         AS orders,
       COALESCE(SUM(o.amount), 0) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

name	orders	revenue
Asha	2	120
Ben	1	20
Chen	0	0

With INNER JOIN, Chen would be missing entirely.

Common mistakes

1. COUNT(*) instead of COUNT(o.order_id). COUNT(*) counts Chen’s unmatched row as 1 order.
2. Filtering the right table in WHERE. WHERE o.amount > 0 removes rows where o.amount is NULL, which drops Chen and silently turns the query into an inner join. Move the condition into ON.
3. Assuming a left join cannot add rows. If the right side has several matches, the left row is repeated once per match.