INNER JOIN vs LEFT JOIN
INNER JOIN vs LEFT JOIN: which rows survive when there's no match, and the row-count bug this mixes up.
Last updated
The short answer
INNER JOIN keeps only rows that match on both sides — use it when a missing related row makes the result meaningless anyway. LEFT JOIN keeps every row from the left table regardless of a match, filling unmatched columns with NULL — use it whenever 'no related row' is itself a valid, meaningful case, like customers with zero orders.
| Dimension | INNER JOIN | LEFT JOIN |
|---|---|---|
| Unmatched left rows | Dropped | Kept, right columns NULL |
| Unmatched right rows | Dropped | Dropped (unless RIGHT/FULL JOIN) |
| Result size | Only the overlap of both tables | At least the size of the left table |
| Typical use | Orders that must have a valid customer | All customers, including those with 0 orders |
| COUNT gotcha | Undercounts if you meant 'all rows' | COUNT(col) skips NULLs; COUNT(*) doesn't |
Choose INNER JOIN when
- →A row without a match on the other side is meaningless for what you're computing — an order without a valid product, for instance.
- →You want the smallest, fastest result set and don't need placeholder rows for missing matches.
- →You're joining two tables where referential integrity guarantees a match will basically always exist anyway.
Choose LEFT JOIN when
- →You need every row from the primary table even when there's nothing to match — 'all customers, with their order count if any.'
- →You're building a report where zero is a real, reportable answer rather than a row that should just vanish.
- →You're checking for the absence of a related row — LEFT JOIN plus WHERE right.id IS NULL is the standard idiom for that.
The catch nobody mentions
COUNT(column) on the right side of a LEFT JOIN silently undercounts, because COUNT ignores NULLs — a customer with zero orders shows as 0 with COUNT(orders.id), which is usually what you want, but the same query with COUNT(*) instead counts 1 (the padded NULL row itself). That's a real and common off-by-one bug when people swap between the two without checking which columns are actually NULL.