Quand utiliseriez-vous une CTE plutôt qu'une sous-requête ?

Utilisez une CTE quand la lisibilité compte, ou quand le même résultat intermédiaire est référencé plusieurs fois, puisqu'elle nomme une étape au lieu de l'imbriquer. Les sous-requêtes conviennent très bien pour un petit filtre en ligne isolé. Les performances sont à peu près équivalentes sur la plupart des moteurs modernes, même si certains matérialisent les CTE et d'autres les inlinent, donc une CTE n'est pas automatiquement plus rapide ou plus lente que la requête imbriquée équivalente.

Surtout pour la lisibilité et la réutilisation. Si une requête a quatre étapes logiques, quatre CTE nommées se lisent comme un paragraphe et un relecteur peut suivre le raisonnement, alors que trois niveaux de sous-requêtes imbriquées obligent à commencer au milieu et à remonter vers l'extérieur. L'autre vraie raison, c'est de référencer deux fois le même ensemble intermédiaire sans le répéter. Ce que je n'irais pas prétendre, c'est que les CTE sont plus rapides. Ce mythe est partout. Selon le moteur et la version, une CTE peut être inlinée dans le plan ou matérialisée une fois, et l'un ou l'autre peut être le plus rapide selon le nombre de références et la sélectivité. Sur Postgres avant la version 12, les CTE étaient une barrière d'optimisation, ce qui les rendait parfois nettement plus lentes, donc je regarde le plan quand la performance compte vraiment. Là où une CTE est irremplaçable, c'est la récursivité : parcourir une hiérarchie de managers ou un arbre de catégories est simple avec une CTE récursive, et franchement pénible sans.