INNER JOIN vs LEFT JOIN in SQL, With Real Output

You switched INNER to LEFT and the row count barely moved

Here's the confusing part nobody mentions: swap INNER JOIN for LEFT JOIN in a real query, and sometimes the result doesn't grow the way the tutorials promise. Sometimes it grows by a strange, specific number. The textbook answer, "LEFT JOIN keeps unmatched rows," is true, but it only tells you half of what's happening.

The full rule: a join produces one output row for every matching pair. INNER JOIN keeps only pairs that match on both sides. LEFT JOIN keeps those same matched pairs, unchanged, and adds one extra row, with NULL on the right, for every left-table row that matched nothing at all. If a left row matches three times, both joins show it three times. If a left row matches zero times, only LEFT JOIN shows it, once. That second case is the only thing that ever differs between them, and it's also the reason a single matched row quietly duplicating itself causes more production bugs than the missing rows ever do.

INNER JOIN: only the pairs that match

The store dataset has 15 customers. Some have one completed order, some have several, and a few have none at all, every order they placed was cancelled or is still pending.

19 rows come back, covering only 10 distinct customers. Ava Coleman and Ella Price each appear three times, once per completed order. The other five customers, the ones whose only orders were cancelled or are still pending, don't appear at all. INNER JOIN doesn't know "customer" is the entity you care about; it only knows pairs that satisfy the ON condition.

LEFT JOIN: the missing five come back as NULL

Change INNER JOIN to LEFT JOIN and run the identical query.

24 rows now: the same 19 matched rows, untouched, plus one new row for each of the five customers who had no completed order, with order_id as NULL. That's the entire effect of LEFT JOIN. It didn't touch Ava's three rows or merge them into one; it only added back the customers that INNER JOIN had dropped entirely.

Why switching sometimes does nothing at all

Drop the status filter and join every customer to every order, no conditions beyond the key:

Both columns return 25. Every single one of the 15 customers in this table has placed at least one order, so there is no unmatched left row for LEFT JOIN to add back. LEFT JOIN only differs from INNER JOIN when an unmatched row actually exists; if your data happens not to have one, the two keywords are interchangeable, and you'll swear you "didn't change anything" when you did. Tutorials never show you this case because their example tables are built so a mismatch always exists.

The row-count bug: COUNT(*) is not COUNT(customers)

This is the part worth re-reading the INNER JOIN result for. It returned 19 rows across 10 customers, and a COUNT(*) on that result reports 19, because COUNT(*) counts rows, not customers.

19 rows, 10 customers. Nothing here is a bug in SQL, the join is doing exactly what it was asked to do, but "how many customers placed a completed order" is a question about entities, and a join's row count answers a question about matches. The moment anyone writes COUNT(*) against a joined result and reports it as a count of customers, orders, or anything on the "one" side of a one-to-many join, the number is wrong by however many extra matches those rows picked up. COUNT(DISTINCT c.id) asks the right question.

The same bug shows up in SUM, and it's harder to notice

COUNT(*) being "too high" is easy to spot once you know to look. SUM() giving a plausible-but-wrong number is not, because there's no obviously round figure to compare it against. Say you want the total catalog price of every product that's actually been ordered at least once.

That number is too high, and silently so. A product ordered three times contributes its price three times, because the join produced three rows for it, one per order. The query never asked "how many distinct products," it asked "sum the price column across every matched pair," and a price is not a per-order fact, it's a per-product one. Don't join at all for a per-product total; test for existence instead.

Same question, no fan-out, because EXISTS only ever returns true or false per product, never once per matching order.

When fan-out is fine

None of this means LEFT JOIN or multiplying rows is inherently wrong. A report that wants "every completed order, with the customer's name on each line" needs exactly those duplicated-looking rows, one per order is the correct shape. The bug only appears when a fan-out happens and then gets summed or counted as if it hadn't. If you genuinely want one row per customer with their order count attached, GROUP BY after the join collapses the duplicates back down correctly, which is the pattern the LEFT JOIN lesson walks through in detail. The rule that keeps you safe either way: before trusting any aggregate after a join, ask whether the table on the "one" side could have matched more than once. If it could, COUNT(*) and bare SUM() are counting the wrong thing.

Where to go next

The INNER JOIN and LEFT JOIN lessons cover the syntax and the ON vs WHERE placement rule in full. The LEFT JOIN example builds the "find unmatched rows" anti-join pattern step by step. Once both joins feel predictable, the practice problems are the fastest way to find out whether you'd catch an inflated COUNT or SUM in your own query, before production does it for you.