CTEs en SQL: Expresiones de Tablas Comunes Explicadas

Una Common Table Expression (CTE) le da nombre a un conjunto de resultados temporal que solo existe durante la consulta en la que se define. Introducidas en SQL:1999, las CTEs permiten dividir una consulta compleja en bloques con nombre, referenciar el mismo resultado intermedio más de una vez y expresar recursión para árboles y grafos. PostgreSQL, SQL Server, MySQL 8+, Oracle y SQLite 3.8.3+ las soportan.

Llevo años escribiendo y revisando SQL, y en cuanto una consulta pasa de tres subconsultas anidadas, alguien del equipo pregunta “¿podemos reescribirla con una CTE?” Casi siempre es el instinto correcto. Las CTEs no hacen las consultas más rápidas por arte de magia, pero las vuelven legibles, testeables y reutilizables dentro de una sola sentencia. Esta guía cubre la sintaxis, la variante recursiva, los trade-offs de rendimiento entre motores y los casos donde conviene una tabla temporal o una subconsulta simple. Si también trabajás con funciones de ventana, las dos se complementan: las CTEs organizan el pipeline, las funciones de ventana calculan entre filas.

Cuándo Usarlas

• Una consulta tiene varias subconsultas anidadas y perdés la cuenta de los paréntesis.
• Necesitás el mismo resultado intermedio en más de un lugar (una CTE evita duplicar la subconsulta).
• Estás recorriendo una jerarquía como un organigrama, una lista de materiales o comentarios anidados.
• Querés que la consulta se lea como una secuencia de pasos con nombre para que un revisor siga la lógica.
• Te gustaría construir y probar una parte de la consulta a la vez seleccionando directamente de la CTE.
• Estás escribiendo una migración de datos y querés organizar las transformaciones en un orden legible.

Cuándo NO Usarlas

• Un SELECT simple o una subconsulta inline ya es rápido y claro. No lo envuelvas en una CTE solo por estilo.
• Tu motor de base de datos no soporta CTEs y no podés actualizarlo (MySQL 5.7 o anterior, SQLite < 3.8.3).
• Asumís que una CTE va a correr más rápido automáticamente. Por lo general no es así. En PostgreSQL antes de 12, las CTEs siempre se materializaban; desde 12 se inlinean por defecto, lo que puede cambiar el rendimiento en cualquier dirección.
• Estás en un hot path OLTP con presupuestos de sub-milisegundo y el planificador inlinea la CTE en un plan que no testeaste. Medí primero.
• Una CTE recursiva recorre un grafo con ciclos y no podés garantizar un guarda de profundidad. Una CTE recursiva sin control va a golpear max_recursion_depth o cte_max_recursion_depth y fallar, o peor, correr hasta que el statement haga timeout.

Sintaxis Básica

WITH cte_name AS (
    SELECT ...
)
SELECT * FROM cte_name;

La cláusula WITH declara una o más CTEs. El SELECT final las trata como tablas regulares dentro de su alcance. La CTE existe solo durante esa sentencia, no durante la sesión.

Ejemplo de CTE No Recursiva

Un patrón común de reporting: calcular los ingresos mensuales y comparar cada mes con el promedio.

WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(total) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY DATE_TRUNC('month', order_date)
),
avg_sales AS (
    SELECT AVG(revenue) AS avg_revenue FROM monthly_sales
)
SELECT
    ms.month,
    ms.revenue,
    ms.order_count,
    a.avg_revenue,
    ms.revenue - a.avg_revenue AS variance
FROM monthly_sales ms
CROSS JOIN avg_sales a
ORDER BY ms.month;

La primera CTE agrupa órdenes por mes. La segunda calcula el promedio. La consulta final une ambas sin repetir agregaciones. Si después necesitás filtrar meses por encima del promedio, extendés el SELECT final sin tocar las CTEs.

Múltiples CTEs y Encadenamiento

Podés declarar varias CTEs y encadenarlas. Cada CTE puede referenciar cualquier CTE declarada antes.

WITH
    active_users AS (
        SELECT user_id, last_login
        FROM users
        WHERE last_login >= CURRENT_DATE - INTERVAL '30 days'
    ),
    user_orders AS (
        SELECT user_id,
               COUNT(*) AS order_count,
               SUM(total) AS lifetime_value
        FROM orders
        WHERE user_id IN (SELECT user_id FROM active_users)
        GROUP BY user_id
    )
SELECT
    u.user_id,
    u.last_login,
    COALESCE(o.order_count, 0) AS order_count,
    COALESCE(o.lifetime_value, 0) AS lifetime_value
FROM active_users u
LEFT JOIN user_orders o ON u.user_id = o.user_id;

user_orders depende de active_users. Escribir la consulta así hace explícita la dependencia, lo que ayuda cuando estás debuggeando un plan o revisando un pull request de un colega. Prefiero esta forma sobre una sola subconsulta anidada porque podés testear cada CTE en aislamiento: comentá el SELECT final, ejecutá SELECT * FROM active_users y verificá el resultado intermedio.

CTE Recursiva para Jerarquías

Una CTE recursiva tiene un miembro ancla y un miembro recursivo unidos con UNION ALL. El ancla siembra la consulta; el miembro recursivo referencia la CTE misma y se repite hasta que no se producen filas nuevas o se alcanza un límite.

-- Organigrama: encontrar todos los reportes bajo el CEO
WITH RECURSIVE org_tree AS (
    -- Ancla
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Paso recursivo
    SELECT e.id, e.name, e.manager_id, ot.depth + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
    WHERE ot.depth < 10
)
SELECT id, name, depth
FROM org_tree
ORDER BY depth, name;

Para subir desde un empleado hasta el CEO, invertí el join:

WITH RECURSIVE chain_of_command AS (
    SELECT id, name, manager_id, 1 AS steps_to_ceo
    FROM employees
    WHERE id = 42

    UNION ALL

    SELECT e.id, e.name, e.manager_id, coc.steps_to_ceo + 1
    FROM employees e
    INNER JOIN chain_of_command coc ON e.id = coc.manager_id
)
SELECT name, steps_to_ceo
FROM chain_of_command
ORDER BY steps_to_ceo;

El flujo de ejecución de una CTE recursiva es simple una vez que lo viste: el ancla corre una vez, el miembro recursivo corre contra la salida del ancla, después contra su propia salida, y así hasta que no aparecen filas nuevas. El diagrama de abajo muestra ese ciclo.

Una vez debugueé una CTE recursiva que corrió 90 segundos antes de hacer timeout. El ancla estaba bien, pero la jerarquía tenía un ciclo: el empleado A reportaba a B, B reportaba a C, y C reportaba a A. El guarda depth < 10 salvó la base de datos, pero el fix real fue una limpieza de datos y un CHECK constraint que previno el ciclo. Siempre agregá un guarda de terminación, y si estás recorriendo jerarquías editables por usuarios, considerá una query separada de detección de ciclos.

CTE Recursiva con Agregación

Las CTEs recursivas pueden llevar agregaciones acumuladas por un árbol. Este ejemplo calcula el costo total de una parte incluyendo todos los sub-componentes en una lista de materiales.

WITH RECURSIVE bom AS (
    -- Ancla: ensamblado de nivel superior
    SELECT
        part_id,
        part_name,
        quantity,
        unit_cost,
        quantity * unit_cost AS line_cost,
        1 AS depth,
        CAST('/' || part_id AS TEXT) AS path
    FROM parts
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursivo: cada parte hija
    SELECT
        c.part_id,
        c.part_name,
        c.quantity * p.quantity AS quantity,
        c.unit_cost,
        c.quantity * p.quantity * c.unit_cost AS line_cost,
        p.depth + 1,
        p.path || '/' || c.part_id
    FROM parts c
    INNER JOIN bom p ON c.parent_id = p.part_id
    WHERE p.depth < 20
)
SELECT
    part_name,
    quantity,
    line_cost,
    depth,
    path
FROM bom
ORDER BY path;

La columna path es un materialized path que hace el output ordenable por posición en el árbol. Para jerarquías muy profundas, una columna materialized path almacenada outperforma la recursión; mirá la recipe de CTE recursiva para un tratamiento más profundo.

CTEs en UPDATE y DELETE

Las CTEs no se limitan a SELECT. PostgreSQL y SQL Server permiten usarlas en sentencias de modificación de datos, lo que es útil cuando las filas objetivo vienen de una subconsulta.

-- Borrar items de órdenes expiradas
WITH expired AS (
    SELECT id FROM orders WHERE status = 'expired'
)
DELETE FROM order_items
WHERE order_id IN (SELECT id FROM expired);

-- Actualizar tier del cliente según gasto de vida
WITH spend AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY customer_id
)
UPDATE customers c
SET tier = CASE
    WHEN s.lifetime_value >= 10000 THEN 'platinum'
    WHEN s.lifetime_value >= 5000  THEN 'gold'
    WHEN s.lifetime_value >= 1000  THEN 'silver'
    ELSE 'bronze'
END
FROM spend s
WHERE c.id = s.customer_id;

En MySQL 8.0+, la cláusula WITH está permitida en UPDATE y DELETE pero la sintaxis difiere; revisá la documentación de MySQL WITH para tu versión.

CTEs MATERIALIZED en PostgreSQL

Por defecto, PostgreSQL 12+ puede inlinear una CTE. Agregá MATERIALIZED para forzar al motor a calcularla una vez y guardar el resultado, o NOT MATERIALIZED para forzar el inlining.

WITH regional_sales AS MATERIALIZED (
    SELECT region, SUM(total) AS total_sales
    FROM orders
    GROUP BY region
    HAVING SUM(total) > 1000000
)
SELECT * FROM regional_sales;

Usalo cuando la CTE sea costosa y se referencie varias veces, o cuando EXPLAIN muestre que el planificador elige un mal plan. SQL Server y MySQL manejan la materialización de otra forma y normalmente no exponen la palabra clave.

Consideraciones de Rendimiento

El rendimiento de las CTEs es la parte que más desarrolladores misunderstanding. La intuición es “subconsulta con nombre = resultado cacheado = más rápido”. Rara vez es así.

PostgreSQL

• Antes de 12: las CTEs siempre se materializaban (calculadas una vez, almacenadas, leídas múltiples veces). Esto ayudaba cuando la CTE era costosa y se referenciaba dos veces, pero perjudicaba cuando el planificador no podía pushar filtros adentro de la CTE.
• 12+: las CTEs se inlinean por defecto, comportándose como una macro. El planificador puede pushar predicados y elegir mejores órdenes de join. Usá MATERIALIZED para restaurar el comportamiento anterior cuando lo necesites.
• CTEs recursivas: siempre materializadas. El working table se construye iterativamente. Indexá la columna de join (manager_id, parent_id) o la recursión va a hacer table scan en cada paso.

SQL Server

• Las CTEs siempre se inlinean; no hay keyword MATERIALIZED. El planificador las trata como syntactic sugar.
• Una CTE referenciada múltiples veces en la misma sentencia se evalúa múltiples veces. Si necesitás caching, usá una temp table (SELECT ... INTO #temp).

MySQL

• Las CTEs se inlinean. WITH RECURSIVE usa una evaluación iterativa con cte_max_recursion_depth (default 1000) como límite de seguridad.
• No hay hint MATERIALIZED. Para resultados costosos compartidos, usá una temp table.

EXPLAIN ANALYZE

Siempre revisá el plan antes de asumir que una CTE ayuda. En PostgreSQL:

EXPLAIN ANALYZE
WITH monthly_sales AS (
    SELECT DATE_TRUNC('month', order_date) AS month, SUM(total) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT * FROM monthly_sales WHERE revenue > 10000;

Si ves CTE Scan on monthly_sales con Storage: Materialized y el filtro se aplica después de la materialización, estás computando la CTE completa y filtrando después. Probá NOT MATERIALIZED o pushá el filtro adentro de la CTE. Para un tratamiento completo de query plans, mirá la guía de tuning de PostgreSQL.

CTE vs Tabla Temporal

Aspecto	CTE	Tabla Temporal
Alcance	Una sentencia	Sesión o transacción
Persistencia	Ninguna	Vive hasta drop o fin de sesión
Índices	Ninguno	Podés agregar índices
Estadísticas	Estimates del planificador	Podés ANALYZE para stats precisas
Reutilización	Dentro de una query	Entre queries en la sesión
Mejor para	Legibilidad, lógica de una sentencia	Resultados grandes reutilizados entre queries

Si una CTE se referencie más de una vez y el resultado intermedio es grande, una temp table con índice suele outperformar. Yo recurro a una temp table cuando el CTE scan domina el plan y el resultado se necesita en una segunda query.

CTE vs Subconsulta vs Vista

Aspecto	CTE	Subconsulta	Vista
Legibilidad	Nombrada, reutilizable	Inline, anónima	Nombrada, persistente
Reutilización	Varias veces en una sentencia	Hay que duplicarla	Referenciable desde cualquier query
Recursión	Soportada	No soportada	No soportada
Materialización	Opcional en PostgreSQL	Evaluada cada vez por defecto	Materialized view opcional
Persistencia	Scope de la sentencia	Scope de la sentencia	Objeto persistente del schema
Mejor para	Lógica multi-paso en una query	Filtrado one-off	Lógica compartida entre muchas queries

CTE vs Subconsulta

Aspecto	CTE	Subconsulta
Legibilidad	Nombrada, reutilizable	Inline, anónima
Reutilización	Se puede referenciar varias veces	Hay que duplicarla si se usa otra vez
Recursión	Soportada	No soportada
Materialización	Opcional en PostgreSQL	Evaluada cada vez por defecto

Buenas Prácticas

• Nombrá las CTEs por el concepto de negocio (monthly_sales, active_users, expired_orders), no por la operación SQL (cte1, subquery, step2).
• Mantené un solo paso lógico por CTE. Si estás joineando y agregando en la misma CTE, separala para que cada paso sea testable.
• Siempre agregá un guarda WHERE depth < N en una CTE recursiva. Elegí N según tus datos; 10 está bien para un organigrama, 100 para un árbol de productos profundo.
• Usá MATERIALIZED en PostgreSQL solo después de revisar el plan de consulta con EXPLAIN ANALYZE. No adivines.
• Testeá una CTE en aislamiento seleccionando directamente de ella antes de agregar la consulta final. Agarré bugs de agregación de esta forma que habrían sido invisibles dentro de un statement de 40 líneas.
• Agregá una columna path a las CTEs recursivas sobre árboles para que el output sea ordenable y debuggeable.

Errores Comunes

• Recursión infinita: olvidar el guarda de terminación o tener un ciclo en los datos de la jerarquía. La query va a golpear cte_max_recursion_depth (MySQL) o correr hasta que el statement haga timeout (PostgreSQL). Siempre agregá WHERE depth < N y considerá un constraint de detección de ciclos en la tabla origen.

• Tratar CTEs como tablas temporales: solo viven durante la consulta. Para persistencia entre sentencias, usá CREATE TEMP TABLE o una tabla real. Vi desarrolladores envolver una CTE en una transacción y esperar que persista entre SELECT statements, lo cual no funciona.

• Suposiciones de rendimiento: algunos motores inlinean CTEs, otros materializan. Una CTE referenciada dos veces en SQL Server se evalúa dos veces. Siempre medí con EXPLAIN ANALYZE antes de afirmar que una CTE “optimiza” algo.

• Exceso de CTEs anidadas: diez CTEs encadenadas pueden ser más difíciles de leer que las subconsultas originales, especialmente cuando cada CTE referencia a otras tres. Si la cadena es larga, considerá un pipeline de temp tables o una vista.

• Recursión mutua: dos CTEs que se referencian entre sí no está soportado en SQL estándar. Vas a obtener un error de sintaxis o un “undefined CTE”. Restructurá con una sola CTE recursiva o un enfoque procedural.

• Pushar filtros demasiado tarde: si materializás una CTE y después filtrás en el SELECT final, el motor computa la CTE completa primero. Pushá el filtro al WHERE de la CTE o usá NOT MATERIALIZED en PostgreSQL 12+.

Diferencias entre Motores de un Vistazo

Feature	PostgreSQL	SQL Server	MySQL	Oracle	SQLite
CTE no recursiva	8.4+	2008+	8.0+	9i+	3.8.3+
CTE recursiva	8.4+	2008+	8.0+	11gR2+	3.8.3+
Hint MATERIALIZED	12+	No	No	No	No
WITH en UPDATE/DELETE	9.1+	2008+	8.0+ (limitado)	No	No
Comportamiento por defecto	Inlined (12+)	Inlined	Inlined	Inlined	Inlined
Límite de recursión	Ninguno (guarda manual)	Ninguno (guarda manual)	cte_max_recursion_depth (1000)	Ninguno	SQLITE_MAX_EXPR_DEPTH

See Also

• Documentación de PostgreSQL WITH clause
• Documentación de SQL Server CTE
• Documentación de MySQL WITH (Common Table Expressions)
• Documentación de SQLite recursive CTE
• Standard SQL:1999 (ISO/IEC 9075-2) — la especificación original de CTEs
• Recipe de CTE recursiva — una recipe enfocada en queries recursivas
• Guía de funciones de ventana — complementa las CTEs para analytics
• Guía de tuning de PostgreSQL — EXPLAIN ANALYZE y comportamiento del planificador