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.
Common mistakes
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.status = 'active'The WHERE clause tests a column from the right table, and unmatched rows have NULL there, so they are filtered out. The LEFT JOIN silently becomes an INNER JOIN and the rows you wanted to keep disappear.
SELECT * FROM a LEFT JOIN b ON a.id = b.a_id AND b.status = 'active'SELECT * FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NOT NULLAn explicit way of writing an inner join with extra steps. If you want only matched rows, an INNER JOIN says so directly and lets the planner optimise accordingly.
SELECT COUNT(*) FROM a LEFT JOIN b ON a.id = b.a_idCounts rows after the join, so a row in a matching three rows in b counts three times. COUNT(DISTINCT a.id) is what answers the question people usually mean.
Questions
Why does my LEFT JOIN behave like an INNER JOIN?
Almost always a condition on the right table in the WHERE clause. Unmatched rows carry NULL there, and NULL fails every comparison, so they are removed after the join. Move that condition into the ON clause.
What is the difference between ON and WHERE here?
ON decides which rows are joined; WHERE filters the result afterwards. For an INNER JOIN the two are equivalent, which is why the distinction only bites once someone changes it to a LEFT JOIN.
How do I find rows with no match?
LEFT JOIN then WHERE on a right-table column IS NULL. It is the one case where filtering the right table in WHERE is correct, because you are deliberately keeping the unmatched rows.
Does the join type affect performance?
An INNER JOIN gives the planner more freedom, since it may reorder tables and drop rows early. A LEFT JOIN constrains the order, so where both produce the same result, the inner form is usually the faster one to write.
What about RIGHT JOIN?
It is a LEFT JOIN with the tables written the other way round, and almost nobody uses it. Consistency matters more than symmetry here — reading a query is easier when every outer join leans the same way.