Diferença entre CTE e SubQuery?

Deste post Como usar ROW_NUMBER no procedimento a seguir?

Existem duas versões de respostas em que uma utiliza uma SubQuery e a outra usa uma CTE para resolver o mesmo problema.

Agora, então, qual é a vantagem de usar um CTE (Common Table Expression) sobre como ub-query (assim, mais legível o que a consulta está realmente fazendo)

A única vantagem de usar um CTE sobre sub-seleção é que eu posso realmente nomear a subconsulta. Existem outras diferenças entre os dois quando um CTE é usado como um CTE simples (não recursivo)?

Na subconsulta versus versões CTE simples (não recursivas), elas provavelmente são muito semelhantes. Você teria que usar o profiler e o plano de execução real para identificar quaisquer diferenças, e isso seria específico para sua configuração (por isso, não podemos dizer a resposta completa).

Em geral ; Um CTE pode ser usado recursivamente; uma subconsulta não pode. Isso os torna especialmente adequados para estruturas de trees.

A principal vantagem da Common Table Expression (quando não a utiliza para consultas recursivas ) é o encapsulamento, em vez de ter que declarar a subconsulta em cada lugar que você deseja usá-la, você pode defini-la uma vez, mas ter múltiplas referências para isso.

No entanto, isso não significa que seja executado apenas uma vez (conforme as iterações anteriores desta resposta , obrigado a todos que comentaram). A consulta definitivamente tem o potencial de ser executada várias vezes se referenciada várias vezes; O otimizador de consulta, em última análise, toma a decisão de como o CTE deve ser interpretado.

CTE são mais úteis para recursion:

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

irá retornar @n linhas (até 101 ). Útil para calendars, conjuntos de linhas fictícios etc.

Eles também são mais legíveis (na minha opinião).

Além disso, os CTE e subqueries são idênticos.

Uma diferença que não foi mencionada é que um único CTE pode ser referenciado nas várias partes de um sindicato.

A menos que eu esteja perdendo alguma coisa, você pode nomear CTEs e subconsultas com a mesma facilidade.

Eu acho que a principal diferença é a legibilidade (eu acho o CTE mais legível porque define sua subquery na frente e não no meio).

E se você precisar fazer algo com recursion, você terá um pouco de dificuldade para fazer isso com uma subconsulta;)

Adicionando às respostas dos outros, se você tiver uma e a mesma subconsulta usada várias vezes, poderá replace todas essas subconsultas por um CTE. Isso permite que você reutilize seu código melhor.

Uma coisa que você precisa entender também é que em versões mais antigas do SQL Server (sim, muitas pessoas ainda precisam suportar bancos de dados do SQL Server 2000), CTEs não são permitidos e, em seguida, a tabela derivada é sua melhor solução.

Um fato importante que ninguém mencionou é que (pelo menos em postgres), os CTEs são cercas de otimização:

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

Ou seja, eles serão tratados como sua própria consulta atômica, em vez de serem inseridos em todo o plano de consulta. Eu não tenho o conhecimento para dar uma explicação melhor, mas você deve verificar a semântica para a versão do sql que você está usando; para usuários avançados, a capacidade de criar uma cerca de otimização pode ajudar o desempenho se você for especialista em controlar o planejador de consultas; em 99% dos casos, no entanto, você deve evitar tentar dizer ao planejador de consultas o que fazer, porque o que você acha que será mais rápido é provavelmente pior do que o que ele acha que será mais rápido. 🙂

DICA: (MAXRECURSÃO n)

você pode limitar o número de níveis de recursion permitidos para uma instrução específica usando a dica MAXRECURSION e um valor entre 0 e 32.767 na cláusula OPTION

Por exemplo, você poderia tentar:

 OPTION (MAXRECURSION 150) GO