A correlated subquery refers to a column from its outer query. This allows the inner query to calculate or test something specific to each current outer row. Derived tables, meanwhile, are subqueries in FROM that expose a result set to the outer query. Understanding both patterns helps solve per-group and existence questions.
Key Takeaways at a Glance:
- •A Correlated Subquery references columns from the outer query (outer reference) and cannot be executed independently.
- •Conceptually, a correlated subquery executes once for each candidate row evaluated by the outer query.
- •Modern query optimizers often unnest (decorrelate) correlated subqueries into equivalent semi-joins or hash joins.
- •Common interview benchmark: 'Find employees earning above their own department's average salary'.
In Simple Words (The Mental Model)
A regular subquery runs once, gets an answer, and shares it with everyone. A correlated subquery is personalized: it looks at each individual person from the outside table, takes their personal department ID, runs a private calculation just for their department, and checks if they qualify.
Real-World Analogy
Think of grading a student on a curve based on their own classroom. You can't just compute a single school-wide average. For Aarav in Class 10A, you calculate Class 10A's average. For Pooja in Class 10B, you calculate Class 10B's average. The inner calculation changes with every single student.