INNER JOIN vs LEFT JOIN: the difference, in plain English

The two tables we'll use

customers has three rows. orders has two — one customer never ordered anything, and one order belongs to a customer who was deleted.

customers (id, name)
-- 1 Ada, 2 Ben, 3 Cleo

orders (id, customer_id, total)
-- 10 -> customer 1, total 40
-- 11 -> customer 9, total 15

INNER JOIN — only rows that match on both sides

INNER JOIN keeps a row only when the condition finds a partner. Ben and Cleo disappear because they have no orders, and order 11 disappears because customer 9 does not exist. You get exactly one row: Ada with her 40.

Use it when a missing match means the row is not interesting.

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

LEFT JOIN — every row from the left table, matched or not

LEFT JOIN keeps all three customers. Ben and Cleo still appear, with NULL where the order columns would be. This is how you answer “who hasn't ordered yet?”

The classic mistake: putting a condition on the right table in WHERE instead of in the ON clause. WHERE o.total > 0 throws the NULL rows away and quietly turns your LEFT JOIN back into an INNER JOIN. Put right-table conditions in ON, or test for IS NULL on purpose.

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;  -- customers with no orders

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is a LEFT JOIN with the tables swapped — most people just rewrite it as a LEFT JOIN so the reading order stays natural. FULL OUTER JOIN keeps unmatched rows from both sides, which is useful when you are reconciling two lists and want to see what is missing in each. MySQL has no FULL OUTER JOIN; you emulate it with a LEFT JOIN UNION a RIGHT JOIN.

Why row counts suddenly explode

If a join returns more rows than the left table has, the right side has several matches per row — that is normal for one-to-many data, but it will double your SUM() totals. Aggregate the right side first (in a subquery or CTE), then join the single row per customer.

If a join returns a huge number of rows, check that you wrote an ON condition at all. A JOIN without ON is a cartesian product: every row paired with every row.