Course contentsShow
← Computer Science
Lesson 1609 of 2,87236. Database FundamentalsFree lesson

Subquery Performance Considerations

Understanding when subqueries are efficient versus when joins or CTEs would perform better.

Subquery Performance Considerations

What you'll learn: You'll discover when subqueries are efficient and when alternative approaches like joins or CTEs deliver better performance.

The Performance Question

Not all subqueries are created equal. While they're conceptually elegant and easy to write, subqueries can sometimes become performance bottlenecks. Understanding when to use them—and when to reach for joins or CTEs instead—is crucial for writing efficient database queries.

When Subqueries Work Well

Scalar subqueries (returning single values) in the SELECT or WHERE clause are often fine for small datasets or when executed once. Uncorrelated subqueries that run independently of the outer query typically perform well because they execute just once and the result is reused.

When Subqueries Struggle

Correlated subqueries can be performance killers. Since they reference columns from the outer query, they may execute once per row in the outer table. With 10,000 outer rows, that correlated subquery runs 10,000 times—a major problem.

Analogy: It's like checking your pantry every single time you add an item to your shopping list, versus checking once at the beginning and keeping notes.

Better Alternatives

  • Joins often outperform correlated subqueries because databases can optimize them with efficient algorithms
  • Subqueries in the FROM clause (inline views) can sometimes be optimized similarly to joins
  • CTEs (Common Table Expressions) make complex logic readable and are calculated once, improving both clarity and performance

General Guidelines

Use subqueries when they're simple and uncorrelated. Switch to joins when you're correlating across tables or notice slow performance. Consider CTEs when you need the same subquery result multiple times.

Key Takeaway: Correlated subqueries execute repeatedly and often perform poorly; prefer joins or CTEs for operations that relate tables, reserving subqueries for simple, one-time calculations.