Processing model: Is a correlated subquery executed as a nested subquery that is evaluated once per candidate row of the outer query (subject to optimizer rewrites)?

Introduction / Context:
Correlated subqueries are a core SQL pattern for existence checks and scalar lookups. Their evaluation model influences performance and indexing strategy.

Given Data / Assumptions:

• The inner subquery references columns from the outer query.
• Semantically, the inner subquery must consider each outer row.
• Optimizers may transform correlated subqueries into equivalent joins or semi-joins.

Concept / Approach:
Conceptually, a correlated subquery is nested: for each outer row, run the inner query using that row’s values. Engines avoid repeated work where possible via rewrites, caching, and indexes, but the logical model remains a per-row evaluation.

Step-by-Step Solution:
Detect correlation by references to outer columns.Ensure selective predicates and supporting indexes on inner tables.Consider EXISTS/NOT EXISTS for semi-join/anti-join semantics.Rewrite to JOINs where clearer or faster; verify with EXPLAIN plans.Measure performance with realistic data distributions.

Verification / Alternative check:
EXPLAIN plans display nested-loop behavior or transformed join operators; both are consistent with per-row semantics.

Why Other Options Are Wrong:
The behavior does not depend on DISTINCT or GROUP BY; correlation itself drives per-row evaluation.

Common Pitfalls:
Missing indexes on correlated predicates; assuming the optimizer will always rewrite to a join.

Final Answer:
Correct