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.