Correlated subqueries: which description correctly captures how a correlated subquery operates within an SQL SELECT?

Introduction / Context:Subqueries can be standalone (noncorrelated) or correlated. Correlated subqueries reference columns from the outer query, causing the inner query to re-execute for each row of the outer result. Recognizing this behavior helps avoid performance traps and choose better alternatives (joins, window functions) where appropriate.

Given Data / Assumptions:

• The inner subquery can reference values from the outer query row.
• Execution conceptually occurs per outer row (though optimizers may rewrite).
• Correlated subqueries often appear in WHERE, HAVING, or SELECT clauses.

Concept / Approach:In a correlated subquery, the inner query depends on parameters from the current outer row. Example: find customers whose total order amount exceeds the average of their own region—here, the inner query filters using the outer customer’s region each time.

Step-by-Step Solution:

Verification / Alternative check:Example: WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.cust_id = c.id) — the inner query uses c.id from the outer Customers row.

Why Other Options Are Wrong:Inner determines outer describes noncorrelated result usage (e.g., scalar subquery), not correlation. Other options are nonsensical or redundant.

Common Pitfalls:Performance issues due to per-row execution; consider rewriting as joins or using window functions for set-based efficiency.

Final Answer:Uses the result of an outer query to determine the processing of an inner query.