w3schools - with sql example
Diferencia entre CTE y SubQuery? (9)
De esta publicación ¿Cómo usar ROW_NUMBER en el siguiente procedimiento?
Hay dos versiones de respuestas donde una usa una SubQuery y la otra usa una CTE para resolver el mismo problema.
Ahora bien, ¿cuál es la ventaja de utilizar un CTE (Common Table Expression) como ub-query (por lo tanto, más legible lo que la consulta está haciendo realmente)
La única ventaja de utilizar un CTE sobre sub selección es que realmente puedo nombrar la consulta secundaria. ¿Hay alguna otra diferencia entre esos dos cuando se usa un CTE como un CTE simple (no recursivo)?
A menos que me falta algo, puede nombrar los CTE y las subconsultas con la misma facilidad.
Supongo que la principal diferencia es la legibilidad (considero que el CTE es más legible porque define tu subconsulta por adelantado en lugar de por el medio).
Y si necesita hacer algo con la recursión, va a tener algunos problemas para hacer eso con una subconsulta;)
Agregando a las respuestas de los demás, si tiene una y la misma subconsulta utilizada varias veces, puede reemplazar todas estas subconsultas con un CTE. Esto le permite reutilizar su código mejor.
En la sub-consulta frente a versiones CTE simples (no recursivas), probablemente sean muy similares. Tendría que usar el generador de perfiles y el plan de ejecución real para detectar cualquier diferencia, y eso sería específico de su configuración (por lo que no podemos decirle la respuesta completa).
En general ; Un CTE se puede usar recursivamente; una subconsulta no puede. Esto los hace especialmente adecuados para estructuras de árboles.
La principal ventaja de la Expresión común de tabla (cuando no la utiliza para consultas recursivas ) es la encapsulación, en lugar de tener que declarar la subconsulta en cada lugar que desee usarla, puede definirla una vez, pero tener múltiples referencias lo.
Sin embargo, esto no significa que se ejecute solo una vez (según las repeticiones anteriores de esta respuesta , gracias a todos los que han comentado). La consulta definitivamente tiene el potencial de ser ejecutada varias veces si se hace referencia a ella varias veces; el optimizador de consultas finalmente toma la decisión sobre cómo debe interpretarse el CTE.
SUGERENCIA: (MAXRECURSIÓN n)
puede limitar el número de niveles de recursión permitidos para una declaración específica utilizando la sugerencia de
MAXRECURSIONy un valor entre 0 y 32.767 en la cláusulaOPTION
Por ejemplo, podrías intentar:
OPTION
(MAXRECURSION 150)
GO
Un hecho importante que nadie ha mencionado es que (al menos en postgres), los CTE son vallas de optimización:
https://blog.2ndquadrant.com/postgresql-ctes-are-optimization-fences/
Es decir, se tratarán como su propia consulta atómica, en lugar de plegarse en el plan de consulta completo. Me falta la experiencia para dar una mejor explicación, pero debe verificar la semántica de la versión de sql que está utilizando; para los usuarios avanzados, la posibilidad de crear una valla de optimización puede ayudar al rendimiento si eres un experto en controlar el planificador de consultas; en el 99% de los casos, sin embargo, debe evitar tratar de decirle al planificador de consultas qué hacer, porque lo que cree que será más rápido es probablemente peor de lo que cree será más rápido. :-)
Una cosa que debes entender también es que en las versiones anteriores de SQL Server (sí mucha gente todavía necesita admitir bases de datos de SQL Server 2000), no se permiten CTE y luego la tabla derivada es tu mejor solución.
Una diferencia que no se ha mencionado es que se puede hacer referencia a un solo CTE en las diversas partes de un sindicato
CTE son más útiles para la recursión:
WITH hier(cnt) AS (
SELECT 1
UNION ALL
SELECT cnt + 1
FROM hier
WHERE cnt < @n
)
SELECT cnt
FROM hier
devolverá @n filas (hasta 101 ). Útil para calendarios, conjuntos de filas ficticias, etc.
También son más legibles (en mi opinión).
Aparte de esto, las CTE y las subqueries son idénticas.