Give the situations in which a query engine cannot flatten a correlated subquery into a join, and describe what the resulting plan does instead.

## The legality bar A rewrite is allowed only if it returns the same rows with the same multiplicity for every possible database state - not just for today's data. That makes decorrelation a proof obligation, and several constructs make the proof impossible. ## Blocker 1: row-limiting inside the subquery `(SELECT price FROM prices p WHERE p.item_id = i.id ORDER BY p.valid_from DESC LIMIT 1)` asks for a *per-outer-row* top-1. A join produces all matching rows; recovering 'the first per group' requires a ranking or a distinct-on style operator, and whether the engine can synthesize that depends on its rewrite repertoire. Many engines simply keep the subplan and re-run it per row. Hand-written alternatives are a window function with a rank filter, or a lateral join that keeps the per-row limit but lets the engine drive it with an index. ## Blocker 2: correlation under OR Join predicates are conjuncts. `WHERE o.flag = 'Y' OR EXISTS (SELECT ...)` cannot become a join condition because rows can qualify without the subquery being consulted. Some optimizers split the query into a union of two disjoint branches; when they do not, the subquery evaluates per row. Rewriting the OR into a `UNION ALL` of mutually exclusive branches is the manual version of that transform. ## Blocker 3: non-equality and expression-wrapped correlation Equality correlations map to hash or merge joins. Range correlations (`BETWEEN o.d - 7 AND o.d`) map only to theta or range joins, which not every engine implements; an inequality correlation may flatten to a nested-loop semi-join with an index probe (fine) or not at all (bad). Wrapping the correlation column in a function - `WHERE lower(s.k) = o.k` - can also defeat both flattening and index access unless a matching expression index exists. ## Blocker 4: volatile or side-effecting expressions A subquery calling a non-deterministic function, a sequence generator, or a procedure with side effects must be executed exactly as written - the number of invocations is observable. Engines mark such expressions volatile and refuse to hoist, cache, or flatten them. ## Blocker 5: aggregates and the count bug A scalar subquery like `(SELECT COUNT(*) FROM o WHERE o.cid = c.id)` returns 0 when there are no matching rows. Turning it into an inner join against a grouped aggregate loses those outer rows entirely; turning it into a left outer join gives NULL, not 0. The correct rewrite is a left outer join against the pre-grouped aggregate with a NULL-to-0 coalesce - this asymmetry is the classic *count bug*, and it is why naive flattening of aggregate subqueries is unsound. Optimizers that implement it handle the coalesce explicitly; those that do not leave the subplan in place. ## Blocker 6: position under an outer join If the correlated subquery sits in the null-supplying side of an outer join, or references a column that may be NULL-extended, moving the predicate into a join condition changes which rows get preserved. The rewriter has to reason about NULL-extension order and often declines. ## What the fallback plan looks like You see the inner query as an attached subplan, filter subquery, or a nested-loop inner side whose actual execution/loop count equals the outer row count. Cost is outer_rows x inner_cost. Some engines cache repeated parameter values, which helps when the correlation column has few distinct values and does nothing when it is unique. ## Remedies, in order of preference 1. **Pre-aggregate and outer join**: compute the inner result grouped by the correlation key once, then left join and coalesce. This is the manual decorrelation and it is usually the biggest win. 2. **Window function**: replaces per-row top-1 and per-row ranking subqueries with a single pass. 3. **Lateral/apply with an index**: keeps per-row semantics but makes each execution a cheap index probe - acceptable when the outer side is small or already filtered. 4. **Split the statement**: materialize the inner result into a temporary table, then join. Useful when the inner query is expensive and reused. 5. **Reduce the multiplier**: if the outer side is filtered down to a few hundred rows before the subquery is evaluated, per-row execution may be perfectly fine - always check the actual multiplier before rewriting. ## Judgment The senior move is not 'never write correlated subqueries'. It is: know the shapes that block flattening, read the plan for per-row execution counts, estimate the multiplier, and rewrite only where the multiplier is large.