JOIN vs subquery — when to use each

Question

Accepted Answer

When you need both individual row data and a group-level aggregate together, a window function is the cleanest option. PARTITION BY dept computes the dept average without collapsing rows. A correlated subquery works but re-executes for every row, which is slow on large tables. A self-join also works but requires explicit grouping before joining. GROUP BY alone collapses to one row per dept, losing individual employee detail. The window function expresses exactly what you want in one pass.