LEFT JOIN vs INNER JOIN

These two account for the overwhelming majority of joins in real queries, and they're also the pair people mix up most — not because the definitions are hard, but because a query that looks like it should keep unmatched rows can silently stop keeping them, with no error and no warning. SQL JOIN Explained covers all five join types as a survey; this is the deep dive on just these two.

At a glance

The core difference, concretely

Three users — two with orders, one (Carol) with none — and this INNER JOIN:

SELECT u.name, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- name   status
-- Alice  completed
-- Alice  pending
-- Bob    completed

Carol is gone entirely — she has no matching row in orders, so she never makes it into an inner join's result. Same tables, same condition, LEFT JOIN instead:

SELECT u.name, o.status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

-- name   status
-- Alice  completed
-- Alice  pending
-- Bob    completed
-- Carol  NULL

Carol is back, with NULL standing in for the order columns she has none of. Both outputs above are real, run against actual tables — this is exactly what each join does, not a simplification.

The trap: a WHERE clause can quietly turn your LEFT JOIN back into an INNER JOIN

This is the single most common real-world LEFT JOIN bug, and it produces no error — just a wrong result that looks entirely plausible. Take the same query, now filtering for completed orders:

SELECT u.name, o.status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';

-- name   status
-- Alice  completed
-- Bob    completed

Carol is gone again — silently, despite the LEFT JOIN. Here's exactly why, traced through what actually happens: the join runs first and produces Carol's row with o.status = NULL, exactly as the earlier example showed. Then WHERE o.status = 'completed' runs against that already-joined row — and in SQL, comparing anything to NULL with = never evaluates to true, not even NULL = NULL (verified: that comparison itself returns NULL, which WHERE treats the same as false). Carol's row gets built by the join and then thrown away by the filter, and the net effect is indistinguishable from an INNER JOIN — the LEFT JOIN did real work that a later clause just undid.

The fix is to decide what you actually mean and say it in the right place. If the intent is "every user, and their completed orders if they have any" — keeping Carol, with no order shown — the condition belongs in the join itself, not in WHERE:

SELECT u.name, o.status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed';

-- name   status
-- Alice  completed
-- Bob    completed
-- Carol  NULL

Moving the filter into ON changes when it applies: it now only decideswhich orders count as a match during the join itself, before any row gets discarded, rather than filtering the combined result afterward. Carol still has no completed order, so she still gets NULL — but she isn't dropped, because nothing filtered her row away after the fact. If the intent really was "only users with at least one completed order," the original query with the WHERE clause was correct all along, and an INNER JOIN would express the same intent more honestly than a LEFT JOIN that's secretly behaving like one.

The general rule this generalizes to: any WHERE condition that references a column from the right-hand side of a LEFT JOIN, using an operator that treats NULL as "doesn't match" (which is every ordinary comparison operator — =, <, IN, and so on), silently filters out every unmatched left row. The one common exception: WHERE o.status IS NULL after a LEFT JOIN is a real, idiomatic pattern — it's exactly how you find left-side rows with no match at all, since IS NULL is the one comparison that actually treats NULL as a match instead of silently failing.

Which one should you use?

• INNER JOIN when a row without a match is meaningless for the question you're asking — a report of order totals per user doesn't need a row for users who've never ordered anything.
• LEFT JOIN when the left-hand table is the actual subject and the right-hand table is supplementary — every user matters, whether or not they've ordered anything, and losing a user because they haven't is the bug, not the feature.
• As a sanity check on an existing query: if a LEFT JOIN is present but every row in the actual output has a non-null value on the right side, something's either filtering the nulls away (the trap above) or the join was never really needed — worth checking which.

Does LEFT JOIN cost more than INNER JOIN?

Not inherently. The database picks a join algorithm — nested loop, hash join, or merge join, covered in How Query Optimization Works — based on table sizes, available indexes, and selectivity, largely independent of whether the join is inner or outer. What genuinely differs: an outer join constrains the planner more than an inner join does, since it can't freely reorder outer joins the way it can reorder inner joins without changing the result — in a query with several joins, that can mean fewer plan options to choose from, not a flatly slower execution. In practice, the join type is rarely the bottleneck; missing indexes on the join columns almost always matter more, which SQL Index Advisor checks for automatically.

Try it yourself

SQL Query Validator catches structural mistakes before you run a join-heavy query, and SQL Explain Helper reads a real EXPLAIN ANALYZE plan to show which join actually ran and how expensive it was. Both run entirely in your browser.