Images are reused AI-generated editorial illustrations, not documentary photographs or verified product screenshots.
Correlated and non-correlated SQL subqueries differ in whether the inner query depends on values from the outer query. That dependency affects how the expression should be understood, although actual execution is subject to the database optimizer. Use a small read-only example to trace the relationship instead of assuming every nested SELECT works the same way.
Identify the outer reference
A correlated subquery refers to a value from the surrounding query, such as the current outer client key. A non-correlated subquery can be understood independently of each outer row. Inspect aliases and scope carefully. A reused column name can obscure which table supplies the value and make a plausible query answer a different question.
Choose the result shape and predicate
A subquery can supply a scalar, rows or a condition according to the surrounding syntax. EXISTS asks whether qualifying rows exist; other expressions have different cardinality requirements. Do not substitute one form mechanically without checking meaning. A scalar subquery that unexpectedly produces several rows needs a resolved data or query rule, not an arbitrary first-row selection.
Test semantics before discussing speed
Use fixtures with a match, no match and several related rows. Check NULL and duplicate behavior under the intended engine. Avoid claiming a correlated query always executes once per row in a particular physical manner or is always slower; optimization can transform execution. Review the actual plan under approved conditions if performance is the question.
A practical checklist
- Identify which values come from the outer query.
- Use clear aliases and column references.
- Choose the required result shape and predicate.
- Test absent and multiple related rows.
- Separate logical dependency from physical execution assumptions.
Worked example
Illustrative example: a synthetic client query uses EXISTS to check for at least one qualifying order associated with that client key. The inner condition depends on the outer client. A separate fixed-list query does not. Tests include clients with zero, one and several orders so the result is not inferred from one happy-path row.
Common questions
Does nesting alone make a subquery correlated? No. Can every subquery return several rows? The surrounding expression determines what is permitted. Is correlation automatically a performance defect? No.
What to do next
Keep the dependency and result-shape explanation beside the query. This makes later refactoring safer than replacing patterns solely because one example looks shorter.