Différence entre CTE et SubQuery?

le principal avantage de la expression courante de la Table (lorsque vous ne l'utilisez pas pour requêtes récursives ) est l'encapsulation, au lieu d'avoir à déclarer la sous-requête à chaque endroit que vous souhaitez l'utiliser, vous êtes en mesure de la définir une fois, mais ont des références multiples à elle.

Toutefois, cela ne pas signifie qu'elle n'est exécutée qu'une seule fois (comme pour des itérations précédentes de cette réponse , merci à tous ceux qui ont commenté). La requête a certainement le potentiel d'être exécutée plusieurs fois si référencée plusieurs fois; l'optimiseur de requête prend finalement la décision de comment le CTE devrait être interprété.

CTE 's sont les plus utiles pour la récursion:

WITH hier(cnt) AS (
        SELECT  1
        UNION ALL
        SELECT  cnt + 1
        FROM    hier
        WHERE   cnt < @n
        )
SELECT  cnt
FROM    hier

retournera @n lignes (jusqu'à 101 ). Utile pour les calendriers, les faux ensembles de rangements, etc.

ils sont aussi plus lisibles (à mon avis).

en dehors de cela, CTE et subqueries sont identiques.

une différence qui n'a pas été mentionnée est qu'un seul CTE peut être référencé dans les différentes parties d'une union

à moins que je ne manque quelque chose, vous pouvez nommer CTE et les sous-séries aussi facilement.

je suppose que la principale différence est la lisibilité (je trouve le CTE plus lisible parce qu'il définit votre sous-commande à l'avant plutôt qu'au milieu).

et si vous avez besoin de faire quoi que ce soit avec la récursion, vous allez avoir un peu de mal à le faire avec un subquery;)

en ajoutant aux réponses des autres, si vous avez un seul et même sous-article Utilisé plusieurs fois, vous pouvez remplacer tous ces sous-Articles par un seul CTE. Cela vous permet de mieux RÉUTILISER votre code.

un fait important que personne n'a mentionné est que (au moins dans postgres), CTEs sont des clôtures d'optimisation:

https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences /

c'est-à-dire qu'ils seront traités comme leur propre requête atomique, plutôt que pliés dans l'ensemble du plan de requête. Je n'ai pas l'expertise pour donner une meilleure explication, mais vous devriez vérifier la sémantique pour la version de sql vous utilisez; pour les utilisateurs avancés, être en mesure de créer une barrière d'optimisation peut aider les performances si vous êtes niveau expert dans le contrôle planificateur de requête; dans 99% des cas, cependant, vous devriez éviter d'essayer de dire au planificateur de requête ce qu'il faut faire, parce que ce que vous pensez sera plus rapide est probablement pire que ce qu'il pense sera plus rapide. :- )

une chose que vous devez également comprendre est que dans les anciennes versions de SQL Server (oui beaucoup de gens ont encore besoin de soutenir les bases de données SQL Server 2000), CTEs ne sont pas autorisés et puis la table dérivée est votre meilleure solution.

indice: (MAXRECURSION n)

vous pouvez limiter le nombre de niveaux de récursivité autorisés pour un déclaration en utilisant l'indice MAXRECURSION et une valeur entre 0 et de 32 767 dans le OPTION clause

par exemple, vous pouvez essayer:

OPTION 
      (MAXRECURSION 150)

GO