Explain correlated subqueries and provide an example
This question tests advanced query logic. It evaluates understanding of row-by-row execution.
Why Interviewers Ask This
Correlated subqueries are powerful but slow. Interviewers check if you understand the dependency on outer rows. They also want to see if you know when to avoid them.
How to Answer This Question
Define it as a subquery referencing outer query columns. Explain it re-evaluates for each row. Provide an example comparing employee salary to department average.
Key Points to Cover
- Depends on outer row
- Re-evaluated per row
- Performance cost
- Use case examples
Sample Answer
A correlated subquery depends on the current row of the outer query, referencing its columns and re-evaluating for each row. For example, to find employees paid above their department average, the subquery calculates the average for the specific department of each employee row being checked.
Common Mistakes to Avoid
- Treating it as independent
- Not realizing performance hit
- Confusing with scalar subqueries
Practice This Question with AI
Answer this question orally or via text and get instant AI-powered feedback on your response quality, structure, and delivery.
Related Interview Questions
Describe a PRIMARY KEY and how it differs from a UNIQUE key
Medium
What is ER model in the DBMS?
Medium
FlipkartWhat is the difference between LIKE and equals operators in SQL?
Easy
TCSWhat is Join and explain its types?
Medium
FlipkartWhat is the difference between UNION and UNION ALL?
Easy
What is the ER model in DBMS?
Easy
Flipkart