Correlated Subqueries in MySQL Explained

A correlated subquery references columns from the outer query, so it re-executes for every outer row. It expresses powerful per-row logic — like 'the latest order of each customer' — but understanding its cost is essential for performance-aware data work.

Key Points

• References the outer row: WHERE o.amount = (SELECT MAX(amount) FROM orders x WHERE x.customer_id = o.customer_id)
• Runs once per outer row — can be slow on large tables
• Common uses: per-group maximums, existence checks with EXISTS
• MySQL 8 window functions often replace them with better performance
• Rewriting as a JOIN on a pre-aggregated derived table is another fix
• EXPLAIN shows DEPENDENT SUBQUERY when a query is correlated