What are correlated subqueries and how do they work?
This question assesses your understanding of subqueries that depend on the outer query. It tests your ability to handle row-by-row evaluation logic.
Why Interviewers Ask This
Correlated subqueries are powerful but can be slow. Interviewers ask this to gauge your ability to write complex logic and understand performance implications. They also test if you know when to replace them with joins for better performance.
How to Answer This Question
Define a correlated subquery as one that references columns from the outer query. Explain that it executes once for every row processed by the outer query. Provide an example, such as finding employees earning above their department average, and discuss performance considerations.
Key Points to Cover
- Depends on outer query columns
- Executes per row of outer query
- Can be replaced by joins for performance
Sample Answer
A correlated subquery depends on the outer query, referencing its columns, so it re-evaluates for every row. For instance, to find employees paid above their department average, the subquery calculates the average for ea…
Common Mistakes to Avoid
- Confusing correlated with non-correlated subqueries
- Ignoring performance costs
- Not knowing how to optimize with joins
Sound confident on this question in 5 minutes
Answer once and get a 30-second AI critique of your structure, content, and delivery. First attempt is free — no signup needed.