Correlated subqueries power per-row comparisons: employees earning above their department average, products priced higher than category median, customers spending more than their segment average, sales exceeding regional targets, students with GPA above major average. They enable row-specific calculations that simple aggregates cannot provide.

Sales Performance Analysis

Compare each salesperson performance to their region average

SELECT name, region, sales, (SELECT AVG(sales) FROM salespeople s2 WHERE s2.region = s1.region) as region_avg FROM salespeople s1 WHERE sales > (SELECT AVG(sales) FROM salespeople s2 WHERE s2.region = s1.region);

Identifies top performers per region. Slow on large datasets - better to use window functions: SELECT name, region, sales, AVG(sales) OVER (PARTITION BY region) FROM salespeople;

All