Subquery classification: Is a correlated subquery defined by the situation in which the outer query depends on data produced by the inner query, or is it the other way around?
-
AIncorrect (in a correlated subquery, the inner query references the outer row)
-
BCorrect (the outer query depends on the inner subquery's data)
-
COnly true for EXISTS subqueries
-
DOnly true for scalar subqueries
-
EDepends on the join order chosen by the optimizer
Answer
Correct Answer: Incorrect (in a correlated subquery, the inner query references the outer row)
Explanation
Introduction / Context: SQL supports non-correlated and correlated subqueries. Distinguishing them helps predict performance and correctness. This question probes whether you know which side references which in correlation: does the inner query refer to the outer, or does the outer depend on the inner in a special way?
Given Data / Assumptions:
- The statement claims a correlated subquery is when the outer query depends on data from the inner query.
- We assume standard SQL semantics for correlated subqueries.
- We consider EXISTS/NOT EXISTS and typical WHERE clause correlations.
Concept / Approach: A correlated subquery is one where the inner subquery references columns from a row of the outer query. Because of this dependency, the inner subquery is re-evaluated for each outer row to which it is correlated. While it is true that the outer result uses the subquery’s outcome, correlation specifically refers to the direction of reference: the inner referencing the outer. Therefore, stating that correlation is defined by the outer depending on the inner’s data is imprecise and, as a definition, incorrect.
Step-by-Step Solution:
Write an example: SELECT * FROM A a WHERE EXISTS(SELECT 1 FROM B b WHERE b.key = a.key).Observe that b.key = a.key references a.key from the outer query.The inner query runs per outer row to test existence, making it correlated.Conclude that correlation is inner-to-outer referencing.Verification / Alternative check: Compare with a non-correlated subquery: WHERE col IN (SELECT col FROM T). The inner has no reference to the outer; it runs once and the result is reused, unlike a correlated subquery.
Why Other Options Are Wrong:
- Correct: Misstates the definition.
- EXISTS-only or scalar-only constraints are incorrect; correlation can appear in various subquery forms.
- Optimizer join order does not change the logical definition of correlation.
Common Pitfalls: Assuming any subquery is correlated; ignoring performance implications because correlated subqueries often evaluate many times, one per outer row.
Final Answer: Incorrect (in a correlated subquery, the inner query references the outer row)