Performance Considerations for Correlated Subqueries
You'll learn: Why correlated subqueries can dramatically slow down your queries and the row-by-row execution pattern that causes this.
The Hidden Cost of Correlation
Correlated subqueries create a dependency between the outer query and the inner query. Unlike non-correlated subqueries that execute once and return a result set, correlated subqueries execute repeatedly—once for every single row in the outer query.
Think of it like this: Imagine you're checking student grades. A non-correlated subquery is like calculating the class average once, then comparing each student to that single number. A correlated subquery is like recalculating a custom average for each student individually—if you have 1,000 students, you're doing 1,000 separate calculations instead of just one.
The Row-by-Row Execution Pattern
When SQL encounters a correlated subquery, here's what happens:
- The outer query processes its first row
- The subquery runs using values from that specific row
- The result is used to complete the outer query's logic for that row
- Repeat for the next row, and the next, and the next...
If your outer query has 10,000 rows, your correlated subquery executes 10,000 times. This multiplicative effect means query time can explode with large datasets.
When They Become Problematic
Correlated subqueries are slowest when:
- The outer query returns many rows
- The subquery itself is complex or scans large tables
- Proper indexes don't exist to support the correlation condition
Key Takeaway: Correlated subqueries execute once per outer row, creating a row-by-row execution pattern that can severely impact performance on large datasets—always consider whether a JOIN or non-correlated alternative would be more efficient.