When the Optimizer Silently Turns Your LEFT JOIN into an INNER JOIN
A LEFT JOIN that silently becomes an inner join produces wrong row counts and missing data with no error message. Developers who trust the SQL text over the execution plan will ship queries that drop rows they intended to keep.
Adding a filter like `WHERE t2.name2 = 'cc'` to a LEFT JOIN makes the optimizer realize that any NULL-padded rows from the outer join would be discarded anyway, so it swaps in a cheaper inner join. The result set shrinks, and the left table's unmatched rows vanish. An `IS NULL` check on the right table, however, blocks this rewrite because eliminating the outer join would change the query's semantics.
Moving the right-table filter into the ON clause preserves the outer join: the right table is filtered before the join, but all left-table rows survive. Left-table conditions behave differently depending on placement. A left-table filter in WHERE pre-filters the driving rows; the same filter in ON only gates which left rows are allowed to match, and a one-to-many right table can still multiply the row count.
The same rules apply to Oracle's `(+)` syntax. A filter without `(+)` triggers elimination just like a WHERE clause; adding `(+)` to the filter keeps the outer join intact. The key audit step is checking `EXPLAIN ANALYZE` for the presence of `Left` or `Right` in the join node name.
The optimizer's decision hinges on logical equivalence, not on the SQL keywords the developer typed. If the outer join and an inner join produce identical results under the given filters, the cheaper plan wins.
Many developers assume LEFT JOIN guarantees row preservation, but the guarantee is conditional on the entire query logic, not just the JOIN clause. The WHERE clause can retroactively invalidate it.
The one-to-many pitfall in Step 4 is underappreciated: putting a left-table condition in ON does not cap the result to one row per left-table row. A multi-match right table will still multiply rows, which can surprise developers who expect a simple filter.
The Oracle (+) migration angle is a concrete risk: legacy code that mixes (+) on the join with bare filters on the right table may have been silently running as inner joins for years without anyone noticing.