Explain a situation where replacing JOIN with a subquery improved performance.

If a customer has 1000 orders, that customer's row appears 1000 times. MySQL must process and filter these duplicate rows.

MySQL stops scanning as soon as it finds the first matching order. This reduces I/O, CPU usage, and memory consumption.

If sales is indexed on year, product_id, the subquery filters down to only relevant rows, which is far faster than joining millions of rows first.

Replacing a JOIN with a subquery improves performance when JOINs produce large intermediate results, when only existence checks are needed, when filtering can be pushed into the subquery, and when avoiding on-disk temporary tables is critical. EXISTS-based subqueries often provide superior performance for large, selective datasets.