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

Performance Considerations for WHERE Subqueries

Understand execution patterns, when subqueries are efficient versus problematic, and alternatives like joins for optimization.

Performance Considerations for WHERE Subqueries

What You'll Learn

You'll learn to identify when WHERE subqueries are efficient versus problematic, and discover how to optimize them using alternatives like joins.

Understanding Subquery Execution Patterns

Not all subqueries perform equally. A subquery's efficiency depends largely on how the database executes it:

Correlated vs. Uncorrelated Subqueries

An uncorrelated subquery runs once and returns results that the outer query uses. Think of it like looking up a price list once, then checking your shopping cart against it.

-- Uncorrelated: runs ONCE
SELECT name FROM employees
WHERE department_id IN (SELECT id FROM departments WHERE location = 'NYC');

A correlated subquery references the outer query and must run for every row being evaluated. It's like checking the price list separately for each item in your cart—much slower!

-- Correlated: runs ONCE PER ROW
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);

When Subqueries Become Problematic

Subqueries can cause performance issues when:

  • They're correlated and run against large tables (thousands of executions)
  • You use NOT IN with nullable columns (can miss results and run slowly)
  • Multiple subqueries execute independently instead of sharing work

Optimization Strategy: Convert to Joins

Many WHERE subqueries can be rewritten as joins for better performance:

-- Instead of IN subquery:
SELECT e.name FROM employees e
INNER JOIN departments d ON e.department_id = d.id
WHERE d.location = 'NYC';

Joins allow the database optimizer to choose efficient execution strategies and often process data in a single pass rather than nested loops.

Key Takeaway: Prefer uncorrelated subqueries over correlated ones, avoid NOT IN with nullable data, and consider rewriting subqueries as joins when performance matters—the database can often optimize joins more effectively than nested subqueries.