Course contentsShow
← Data Science
Lesson 979 of 2,14525. SQL Subqueries and CTEsFree lesson

Performance Considerations

Understand why correlated subqueries can be slow and when they execute row-by-row.

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:

  1. The outer query processes its first row
  2. The subquery runs using values from that specific row
  3. The result is used to complete the outer query's logic for that row
  4. 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.