INNER JOIN vs LEFT JOIN
Short answer: INNER JOIN returns only rows where both tables match; LEFT JOIN returns every row from the left table and pads NULL where the right side has no match. Use INNER JOIN when "no match" means "no row." Use LEFT JOIN when "no match" means "row stays, with NULLs on the right." The choice is about what should happen to rows on the left with no counterpart on the right, nothing else.
Quick comparison #
INNER JOIN | LEFT JOIN | |
|---|---|---|
| Left rows with a match | included | included |
| Left rows without a match | dropped | included, right cols = NULL |
| Right rows without a match on left | dropped | dropped |
| Row count | <= rows on left | >= rows on left (always) |
| Use when | "must have a counterpart" | "show everyone, mark who has data" |
Same question, two answers #
When to use which #
INNER JOIN: the data on the right is required. "Show me orders with their customer." If an order has no customer, something is broken upstream and you'd rather see fewer rows than a row with NULL where the customer should be.
LEFT JOIN: the right side is optional context. "Show me every customer and their last order, if any." Customers with no orders should appear with a NULL last-order, not vanish. Also the right tool when you're auditing for orphans: LEFT JOIN then WHERE right.id IS NULL finds rows that should have a counterpart but don't.
The hidden third case: when you write INNER JOIN but mean LEFT JOIN. Symptom: row count quietly drops by 5% after deploying a new feature. Cause: the right-side table has incomplete data and the INNER JOIN silently filtered legitimate left rows. If you can't prove every left row will have a match, default to LEFT JOIN.
FAQ #
Which is faster, INNER JOIN or LEFT JOIN?
On modern engines with similar plans, the difference is usually negligible. INNER JOIN can be marginally faster because the optimizer has more freedom to reorder and prune, but the gap is rarely meaningful. Choose by semantics (must-match vs preserve-left), not by perceived performance.
Should I use INNER JOIN or LEFT JOIN by default?
Use INNER JOIN when the right side is required (every left row must have a match). Use LEFT JOIN when the right side is optional (you want to keep left rows even with no match). When in doubt, LEFT JOIN is the safer default; it preserves rows you might have lost. The exception is well-defined parent-child relationships where the parent must always exist.
Can a LEFT JOIN return fewer rows than the left table has?
Not under normal use — LEFT JOIN always returns at least one row per left row. If you see fewer, check whether a WHERE clause is filtering on the right side: WHERE right.col = 'x' evaluates to NULL for unmatched rows and excludes them, silently turning the LEFT JOIN into an INNER JOIN.
What does NULL mean in the right-side columns after a LEFT JOIN?
It means "no matching row on the right side for this left row." Use it to find orphans: SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL returns users with no orders. Test for null with IS NULL, never = NULL.
Can I convert a LEFT JOIN to an INNER JOIN?
Yes — when you can prove every left row has a match. The result is identical and INNER may be slightly faster. The risk: data quality changes (a new orphaned row) silently changes the result. If you can't guarantee every match, LEFT JOIN is safer.
How do I find left rows that have no matching right row?
The LEFT JOIN ... IS NULL anti-join pattern: SELECT a.* FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.id IS NULL. Equivalent to NOT EXISTS (...), both NULL-safe. Avoid NOT IN (subquery) here — a single NULL in the subquery makes it return no rows.
The WHERE vs ON trap turns LEFT JOIN into INNER JOIN. Putting a filter on the right-side table in WHERE (WHERE o.status = 'paid') silently drops the unmatched left rows — NULL = 'paid' is NULL, which fails the filter. Put right-side filters inside the ON clause to preserve them: LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'paid'. This is the single most common pitfall in joining queries; covered in detail on LEFT JOIN.