Una CTE (Common Table Expression), definida con WITH, es un conjunto de resultados temporal nombrado que puedes referenciar dentro de una consulta. Las CTEs hacen que las consultas complejas sean más legibles al desglosarlas en pasos nombrados y lógicos — y permiten consultas recursivas (para jerarquías).
active_users (
id, name users active
)
active_users
name ;
El WITH ... AS (...) define una CTE — una consulta nombrada que referencias después. Es como una subconsulta, pero nombrada y legible, especialmente para consultas complejas.
WITH
monthly_sales AS (
SELECT month, SUM(amount) AS total FROM orders GROUP BY month
),
avg_sales AS (
SELECT AVG(total) AS avg_total FROM monthly_sales -- a CTE can reference another
)
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total; -- months above the average
Múltiples CTEs desglosan una consulta compleja en pasos legibles y secuenciales — cada una construida sobre la anterior. Esto es mucho más claro que subconsultas profundamente anidadas.
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- base case (the top)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- recursive step
)
SELECT * FROM subordinates; -- the whole tree under employee 1
Las CTEs recursivas (WITH RECURSIVE) recorren datos jerárquicos/de árbol (organigramas, árboles de categorías, listas de materiales, rutas en grafos) — algo que de otro modo es muy difícil en SQL.
Las CTEs son una característica SQL importante para legibilidad y recursión, y comprenderlas es valioso para escribir consultas complejas mantenibles y para manejar datos jerárquicos.
El beneficio cotidiano principal es la legibilidad: las consultas complejas con múltiples niveles de subconsultas se vuelven difíciles de leer y mantener, mientras que las CTEs te permiten desglosar la consulta en pasos lógicos secuenciales y nombrados (leer de arriba a abajo como una serie de operaciones), mejorando dramáticamente la claridad — por esta razón, las CTEs suelen preferirse sobre subconsultas profundamente anidadas para consultas complejas, convirtiéndolas en una herramienta importante para escribir SQL mantenible.
Igualmente importante es la capacidad de CTE recursiva (WITH RECURSIVE), que permite consultar datos jerárquicos y estructurados en árbol (organigramas, árboles de categorías, listas de materiales, recorrido de grafos) — una clase de consultas que de otro modo es muy difícil o imposible en SQL estándar, haciendo que las CTEs recursivas sean la solución estándar para el recorrido de datos jerárquicos.
Comprender ambos usos — CTEs para descomponer consultas complejas en pasos legibles, y CTEs recursivas para recorrido jerárquico — cubre el valor práctico.
Dado que las consultas complejas son comunes en aplicaciones reales (y la legibilidad/mantenibilidad importa), y dado que los datos jerárquicos (que las CTEs recursivas manejan bien de forma única) aparecen frecuentemente (estructuras organizacionales, categorías anidadas, datos en hilo), comprender las CTEs es un conocimiento valioso de nivel senior que mejora la mantenibilidad de las consultas y desbloquea consultas jerárquicas — una herramienta frecuentemente relevante para escribir SQL limpio y sofisticado y un tema común que demuestra capacidad SQL avanzada, particularmente la forma recursiva para datos estructurados en árbol.