When would you use a correlated subquery versus a JOIN or a window function, and what's the performance concern?

A correlated subquery references the outer row and conceptually runs once per outer row, which is intuitive for 'rows where X is the max for its group' but can be slow if the optimizer can't rewrite it.

A JOIN (often to a derived/grouped table) is usually faster for set-based filtering, and window functions like ROW_NUMBER()/RANK() OVER (PARTITION BY ...) express top-N-per-group elegantly without a self-join. The practical guidance: prefer set-based JOINs or window functions for large data, and check the execution plan, because a naive correlated subquery can degrade to O(n*m).