Using correlated subquery when JOIN would work

Use JOIN with GROUP BY

Correlated subqueries execute per row. JOINs usually much faster.

Consider JOINs or window functions

High

Poor performance

SELECT name, (SELECT AVG(salary) FROM employees e2 WHERE e2.dept = e.dept) FROM employees e;

SELECT name, AVG(salary) OVER (PARTITION BY dept) FROM employees;

Window functions are much faster for per-group aggregates.

Use window functions instead of correlated subqueries in SELECT

High

Correlated subquery in SELECT executes per row