A correlated subquery references a column of the outer query, and is therefore evaluated for each outer row, at least logically. It expresses comparisons such as "above the average of its group". On large volumes, a join or a window function may be faster.
Example
SELECT * FROM produits p
WHERE prix > (SELECT AVG(prix) FROM produits WHERE categorie = p.categorie);