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

Performance Considerations for SELECT Subqueries

Learn when SELECT subqueries execute and how to optimize queries that use them for better performance.

Performance Considerations for SELECT Subqueries

You'll learn: How SELECT subqueries impact query performance and when to optimize or avoid them.

The Performance Challenge

SELECT subqueries execute once per row in your result set when they're correlated (referencing the outer query), but only once total when non-correlated. This distinction is critical for performance.

Think of it like checking your phone: A non-correlated subquery is checking once before leaving home. A correlated subquery is checking at every street corner during your walk—the more corners (rows), the more checks you perform.

Execution Timing

Non-correlated SELECT subqueries run once:

SELECT product_name,
       (SELECT AVG(price) FROM products) AS avg_price
FROM products;

The inner query calculates the average once, then every row uses that same value. Fast and efficient.

Correlated SELECT subqueries run repeatedly:

SELECT c.customer_name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count
FROM customers c;

If you have 10,000 customers, the subquery executes 10,000 times—once per customer.

Optimization Strategies

  1. Consider JOINs instead: Correlated subqueries can often be rewritten as LEFT JOINs with GROUP BY, executing more efficiently
  2. Use WHERE subqueries: When filtering data, subqueries in WHERE typically outperform SELECT subqueries
  3. Limit result set size: Filter rows in the outer query before applying expensive subqueries
  4. Index supporting columns: Ensure columns referenced in correlated conditions are indexed
  5. Test with realistic data volumes: Performance degradation isn't always obvious with small datasets

Key Takeaway: Non-correlated SELECT subqueries are generally fast; correlated ones execute per row and may need optimization through JOINs, especially with large datasets.