skip to content

Under what condition may an optimizer legally convert a LEFT OUTER JOIN into an INNER JOIN, and why is that conversion worth making?

level: middleimportance: must knowfreq 52%

answer

  1. null-rejecting WHERE predicate kills NULL-extended rows
  2. then LEFT JOIN == INNER JOIN
  3. inner joins reorder freely + allow pushdown
  4. filter in WHERE vs ON = the classic bug
  5. IS NULL predicate = anti-join, no conversion

basics

~20 s

When a predicate applied after the join rejects NULLs on the null-supplying side, the NULL-extended rows cannot survive anyway, so the outer join is equivalent to an inner join. Converting is worth it because inner joins can be reordered freely and get more access-path and pushdown options.

solid answer

~60 s

A `LEFT JOIN` produces every preserved-side row plus, for unmatched rows, NULLs in the right-side columns. If a later predicate is **null-rejecting** for that side - it evaluates to UNKNOWN or FALSE whenever those columns are NULL, e.g. `WHERE b.status = 'OPEN'` or `WHERE b.id > 0` in a `WHERE` clause - then every NULL-extended row is discarded. The outer join's extra rows are therefore invisible, and the optimizer rewrites it to an inner join. Why bother: outer joins constrain the optimizer. They are not freely associative or commutative, so join reordering is restricted; predicates cannot be pushed to the null-supplying side; and null-extension logic costs work. An inner join lifts all of that - the join order becomes fully searchable, filters push down to both base tables, and the whole predicate set participates in transitive derivation. The practical corollary is the classic bug: putting a filter on the outer-joined table in `WHERE` instead of `ON` silently turns the outer join into an inner join and drops the unmatched rows you wanted to keep. `IS NULL` predicates are the exception - they preserve NULL-extended rows and are how you express an anti-join.

code

sql · 13 lines
sql
-- optimizer converts to INNER JOIN: rows of a with no b are dropped
SELECT a.id, b.status
FROM a LEFT JOIN b ON b.a_id = a.id
WHERE b.status = 'OPEN';

-- stays an outer join: a-rows with no open b survive with NULL status
SELECT a.id, b.status
FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'OPEN';

-- anti-join: keeps exactly the unmatched a-rows
SELECT a.id
FROM a LEFT JOIN b ON b.a_id = a.id
WHERE b.a_id IS NULL;

go deeper

for a junior

Know the practical rule: a filter on the outer-joined table in WHERE turns a LEFT JOIN into an inner join and drops unmatched rows; put it in ON to keep them.

for a middle

Define null-rejecting predicates, state the equivalence, and give the IS NULL exception.

for a senior

Explain what the optimizer gains - reordering freedom, pushdown, transitive derivation, better estimates - and how you verify the conversion in a plan.

for a principal

Treat it as a review standard: outer joins with filters in WHERE are a defect pattern; also discuss join elimination and how constraints unlock these rewrites.

## What an outer join adds `A LEFT JOIN B ON p` returns all rows of the inner-join result, plus every row of A that matched nothing, extended with NULLs in B's columns. Those NULL-extended rows are the only difference between the outer and the inner join. ## Null-rejecting predicates A predicate is **null-rejecting** for B if it cannot evaluate to TRUE when B's columns are NULL. Ordinary comparisons qualify: `b.status = 'OPEN'`, `b.amount > 0`, `b.k IN (1,2,3)` are all UNKNOWN when the column is NULL, and a `WHERE` clause keeps only TRUE. So do most strict function applications compared to a value. Not null-rejecting: `b.id IS NULL`, `COALESCE(b.status,'X') = 'X'`, `b.status IS NOT DISTINCT FROM NULL`, and any predicate that yields TRUE for NULL inputs. ## The rewrite If the query applies a null-rejecting predicate on B *after* the join - i.e. in `WHERE`, or in an `ON` clause of a subsequent join that has the same effect - then the NULL-extended rows produced by the left join are all removed downstream. The outer join and the inner join therefore produce the same final result, and the rewriter substitutes the inner join. Engines apply this transitively: converting one outer join can make a neighbouring one convertible too, sometimes collapsing a whole chain. ## Why the optimizer wants it 1. **Join reordering.** Inner joins are commutative and associative, so the optimizer can enumerate orders freely. Outer joins are not: reordering them requires special conditions and most planners restrict the search space rather than reason it through. More orders means a better chance of finding the cheap one. 2. **Predicate pushdown.** Filters cannot be pushed to the null-supplying side of an outer join without changing semantics. After conversion, both sides are ordinary inputs and every filter can be pushed to its base table - which in turn enables index and partition access. 3. **Transitive derivation.** With `a.k = b.k` as an inner-join equality, a predicate on `a.k` can be copied to `b.k`. That derivation is unsound across an outer join because the null-supplying side may legitimately hold no matching value. 4. **Cheaper execution.** No NULL-extension bookkeeping; some algorithms (certain merge/hash variants) have simpler and faster inner-join paths. 5. **Better estimates.** Outer-join cardinality estimation must model unmatched rows; inner joins use the well-understood selectivity model. ## The correctness trap this implies for query authors Because the rewrite is automatic and silent, a filter written in the wrong clause changes your result set: - `... LEFT JOIN b ON b.a_id = a.id WHERE b.status = 'OPEN'` - the outer join is effectively an inner join; rows of A with no B disappear. - `... LEFT JOIN b ON b.a_id = a.id AND b.status = 'OPEN'` - the filter is part of the match condition; A-rows with no matching open B survive with NULLs. That difference is one of the most common real SQL bugs and one of the most common interview follow-ups. Nothing warns you: the query is valid, the plan is fine, the numbers are simply wrong. ## The deliberate exception: anti-join by IS NULL `LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL` keeps exactly the NULL-extended rows - the rows of A with no match. That predicate is not null-rejecting, so no conversion happens; instead many optimizers recognize the whole pattern and implement it as an anti-join. It is a legitimate way to express 'rows with no counterpart', though `NOT EXISTS` states the intent more directly. ## Related simplifications The same family includes converting a `FULL OUTER JOIN` to a left or inner join when one side has a null-rejecting predicate, and removing a join entirely (**join elimination**) when the joined table contributes no columns and a foreign key plus uniqueness guarantees exactly one match - a rewrite that makes wide views over many lookup tables cheap when only a few columns are selected. ## How to verify Read the plan: if you wrote a left join and the plan shows an inner/hash join with no outer marker, the conversion happened. Then ask whether that was your intent - if you needed the unmatched rows, move the filter into the `ON` clause.

  • Which predicates on the null-supplying side do NOT trigger the conversion?
    Predicates that can be TRUE when the columns are NULL: `b.id IS NULL`, `COALESCE(b.status,'X') = 'X'`, `b.status IS NOT DISTINCT FROM NULL`, and OR-ed forms where one branch tolerates NULL. These preserve the NULL-extended rows, so the outer join must remain. `IS NULL` in particular is how the outer-join-plus-IS-NULL anti-join idiom works.
  • Why can't the optimizer just push the WHERE filter to the b-side scan instead of converting the join?
    Because filtering b before the join and filtering the joined result are different queries: pre-filtering leaves unmatched a-rows alive and NULL-extended, while post-filtering removes them. The conversion to an inner join is the sound step; once it is an inner join, pushing the predicate into b's scan becomes legal too. Order matters: convert first, then push.

saying these in an interview costs you the question

  • Saying a filter on the right table works the same in WHERE and in ON
  • Claiming outer joins can be reordered as freely as inner joins
  • Thinking b.id IS NULL triggers the outer-to-inner conversion
  • Believing the conversion changes results (it is result-preserving by definition)
  • Assuming LEFT JOIN is inherently slower, rather than more constrained for the optimizer

context

resolving links…