Correlated subquery performance

Question

Accepted Answer

Correlated subqueries execute once per outer row by definition. On 1M rows with no index on the join column, that is 1M full scans - potentially catastrophic. Some optimizers (PostgreSQL, SQL Server) can unnest simple correlated subqueries into JOINs, but this is not guaranteed for complex patterns. The fix: rewrite as a JOIN or CTE with GROUP BY when possible, and ensure the correlated column is indexed when you must keep the correlated form.