Anatomy of a Correlated Subquery

Interviewers split subqueries into two camps. A plain (uncorrelated) subquery can run on its own. A correlated subquery references a column from the outer query, so it cannot run standalone.

• Uncorrelated: evaluated once, result reused for every outer row.
• Correlated: re-evaluated once per outer row, because it depends on that row.

The tell-tale sign is a column from the outer table appearing inside the inner query. Spot that and you can name the pattern instantly.

Picture the engine looping over outer rows. For each outer row it plugs that row's values into the inner query, runs it, and uses the result to decide or compute something.

This is the mental model interviewers want you to verbalize: "the inner query runs once for every outer row."

That phrasing also hints at the classic follow-up: correlated subqueries can be slow because the inner query may execute thousands of times. We will fix that in lesson 4.