Last reviewed
Correct answer: C. It references a column from the outer query, so it's re-evaluated per outer row
Explanation
Correlation is a matter of scope, not syntax. A subquery becomes correlated the moment it references a column belonging to a table in the enclosing query, a name its own FROM clause never mentions. MySQL defines it as "a subquery that contains a reference to a table that also appears in the outer query", and PostgreSQL notes that such variables act as constants during any one evaluation of the subquery.
Because that borrowed column changes from one outer row to the next, the inner query cannot be computed once and cached. SQLite states the runtime consequence plainly: "A correlated subquery is reevaluated each time its result is required. An uncorrelated subquery is evaluated only once and the result reused as necessary."
That makes option D the costly misconception. Re-running an inner query per outer row is generally more work, not less, which is why PostgreSQL and MySQL both rewrite such subqueries into joins and semijoins where they can. Option C is false; correlated subqueries live in WHERE constantly, usually under EXISTS. Option B mistakes typography for semantics: SQL keywords are case-insensitive, and letter case has no bearing on correlation.
Sources
“A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query.”
“A correlated subquery is reevaluated each time its result is required. An uncorrelated subquery is evaluated only once and the result reused as necessary.”
“The subquery can refer to variables from the surrounding query, which will act as constants during any one evaluation of the subquery.”
Practise 3 questions on this topic
Take SQL Subqueries — Timed Test (3 questions) — scored instantly, explanation for every question, no login.