Una vista da nombre a una consulta para siempre y para todo el mundo. Muchas veces lo que necesitas es lo contrario: dar nombre a un paso intermedio aquí, ahora y solo dentro de esta consulta, sin crear ningún objeto ni pedir permiso a nadie. Eso es una expresión de tabla común —Common Table Expression, CTE—, y se escribe poniendo WITH delante del SELECT.

Con ellas cerrarás dos promesas del curso. La primera es de 07-04 y 07-05: la legibilidad se rompe a partir de tres niveles de tablas derivadas, y aquí verás la misma consulta escrita de las dos formas, lado a lado. La segunda es de 03-06: un SELF JOIN recorre un nivel de la jerarquía, y para recorrer un árbol de profundidad desconocida hace falta WITH RECURSIVE — con el que sacarás por fin el organigrama completo de TiendaVerde con su nivel y su ruta, la cadena de referidos de tres saltos que va de Lucía a Núria, y una serie de doce meses generada de la nada.

1. WITH: la subconsulta con nombre, puesta delante
2. Legibilidad: tres niveles de tablas derivadas frente a tres CTE
3. Varias CTE encadenadas: la consulta por pasos
4. CTE, vista y tabla derivada: cuál usar
5. Materialización: lo que cambió en PostgreSQL 12
6. CTE en INSERT, UPDATE y DELETE; el patrón "mover filas"
7. WITH RECURSIVE: la anatomía
8. Los tres casos de TiendaVerde
9. Bucles infinitos y cómo protegerse
10. Errores Comunes y Consejos
11. Ejercicios
12. Conclusión

Una CTE es una subconsulta a la que se le da un nombre antes de la consulta principal. Vive solo durante esa sentencia y se usa después como si fuera una tabla.

Es exactamente la tabla derivada canónica de 07-04, con las mismas cifras, pero leída de arriba abajo: primero se define totales_pedido, después se usa. La sintaxis mínima es WITH nombre1 AS ( SELECT ... ), nombre2 AS ( SELECT ... ) SELECT ... FROM nombre1 JOIN nombre2 ..., donde nombre2 puede usar nombre1.

Cuatro reglas que conviene fijar desde el principio: cada CTE necesita nombre (y opcionalmente puede renombrar sus columnas, WITH t (pedido_id, total) AS (...)); se separan por comas, y WITH se escribe una sola vez aunque haya cinco; una CTE puede referirse a las anteriores, nunca a las posteriores (salvo con RECURSIVE, apartado 7); y una CTE se puede usar varias veces en la misma consulta, cosa que una tabla derivada no permite.

Aquí se cierra la promesa de 07-04. La pregunta: de los clientes que han comprado, ¿cuáles superan la facturación media por cliente, y por cuánto? Son tres pasos —total por pedido, total por cliente, media de esos totales— y con tablas derivadas quedan anidados:

Cuenta los paréntesis, y fíjate en que el mismo cálculo de totales por pedido aparece dos veces, copiado, porque una tabla derivada no se puede reutilizar. Ahora lo mismo con CTE:

Cinco de los doce clientes con compras superan la media de 60,66 € (727,95 € entre 12). El resultado es idéntico al de la versión anterior; lo que ha cambiado es que ahora se puede leer, revisar en un pull request y depurar paso a paso: basta con cambiar el SELECT final por SELECT * FROM totales_cliente para ver el resultado intermedio sin tocar nada más.

Y hay una ganancia que no se ve: totales_pedido se escribe una vez y se usa dos. En la versión anidada estaba duplicada, con todo lo que eso significa el día que cambie la fórmula del importe.

Ese es el patrón natural de escribir análisis complejos: no se piensa en una consulta, sino en una secuencia de transformaciones.

Cada CTE reduce o transforma la granularidad y tiene un nombre que dice qué contiene. Si el resultado final no cuadra, el diagnóstico es mecánico: ejecuta SELECT COUNT(*) FROM totales_pedido (deben salir 20), luego totales_cliente (12), y el nivel donde el número no sea el esperado es donde está el error. Es el consejo de 07-04 —"cuenta las filas de cada nivel"—, ahora con los niveles bautizados. Y nómbralas por lo que contienen, no c1, c2, c3: una consulta con WITH ventas_2025 AS ..., devoluciones_2025 AS ..., neto AS ... se entiende sin leer el SQL.

La regla de decisión cabe en tres frases: un solo nivel y de usar y tirar → tabla derivada; dos o más pasos, o el mismo paso usado dos veces → CTE; lo que usarán otras consultas, otras personas o el equipo de BI → vista. Y no son excluyentes: una vista puede estar definida con WITH dentro.

Este apartado corrige lo que dicen casi todos los tutoriales antiguos. Hasta PostgreSQL 11, una CTE era una barrera de optimización: el motor la ejecutaba entera, guardaba su resultado en memoria y solo después seguía, de modo que un filtro de la consulta exterior no podía empujarse dentro. Escribir WITH t AS (SELECT * FROM lineas_pedido) SELECT * FROM t WHERE producto_id = 15 calculaba las 47 filas para descartar casi todas.

Desde PostgreSQL 12 ya no es así. Una CTE que se usa una sola vez y no tiene efectos secundarios se integra (inlining) en la consulta principal, exactamente igual que una vista o una tabla derivada, y el planificador puede empujar los filtros hacia dentro. Se acabó la penalización por escribir consultas legibles. Las dos palabras clave para forzar cada comportamiento:

Escrito como WITH totales_pedido AS MATERIALIZED ( ... ), el paso se calcula una sola vez por muchas veces que lo consulte la sentencia principal — que es lo que quieres cuando ese paso recorre 47 líneas y lo usas en dos subconsultas distintas.

La forma de saber qué está pasando es mirarlo, no suponerlo: si en el EXPLAIN aparece un nodo CTE Scan sobre un CTE nombre, se ha materializado; si no aparece por ninguna parte y ves directamente los escaneos de las tablas, se ha integrado. La técnica es la de 08-05.

El error heredado. Durante años se enseñó "usa una CTE para forzar el orden de ejecución" y "no uses CTE, que son lentas". Las dos afirmaciones caducaron en PostgreSQL 12. Si necesitas la barrera, pídela explícitamente con MATERIALIZED; si no, escribe la CTE porque se lee mejor y no pagues nada por ello. En MySQL 8 y SQL Server las CTE siempre se integran y no existe la palabra clave; en Oracle hay pistas equivalentes (/*+ MATERIALIZE */).

Un WITH puede preceder a cualquier sentencia, no solo a un SELECT. Y hay más: una CTE puede ser ella misma un INSERT, UPDATE o DELETE con RETURNING, y su resultado alimentar al siguiente paso. Ese es el patrón que resuelve algo que en 05-04 era imposible en una sola sentencia: mover filas de una tabla a otra.

TiendaVerde quiere archivar los pedidos cancelados —hoy, solo el 6— en una tabla histórica:

Una sola sentencia, una sola transacción, ninguna ventana en la que la fila no exista en ningún sitio. Las tres propiedades que lo hacen posible: todas las subsentencias ven la misma instantánea de la base de datos, la del inicio de la sentencia: la CTE borrados no ve el efecto del INSERT, y el INSERT no ve el efecto del DELETE sobre pedidos. El orden de ejecución no está garantizado, así que escribir en la misma tabla desde dos ramas de la misma sentencia da resultados imprevisibles. Y una CTE que escribe se ejecuta siempre, aunque la consulta principal no la use: es la única excepción a la integración del apartado 5.

Un segundo uso, más cotidiano: calcular con WITH y actualizar con el resultado. Alinear el precio de catálogo con el precio medio realmente vendido:

Diecisiete productos, los que se han vendido alguna vez. Y fíjate en la ventaja sobre la versión de 07-04: allí hacía falta un WHERE EXISTS para que los tres productos nunca vendidos no recibieran NULL; aquí el JOIN implícito con la CTE ya los deja fuera. (Recarga el script después de probarlo.)

Aquí se cierra la promesa de 03-06. Una CTE recursiva es una CTE que se referencia a sí misma, y tiene siempre la misma forma:

Cómo se ejecuta, que es lo único que hay que entender de verdad:

Iteración a iteración, con el organigrama de TiendaVerde: la base produce Rosa; la primera iteración busca los hijos de Rosa y produce Andrés, Beatriz y Daniel; la segunda busca los hijos de esos tres y produce Óscar, Laia, Marc e Irene; la tercera busca los hijos de esos cuatro, no encuentra ninguno, la tabla de trabajo queda vacía y el proceso termina. Total: 1 + 3 + 4 = 8 filas, los ocho empleados.

Tres detalles de sintaxis con trampa: RECURSIVE va inmediatamente después de WITH, una sola vez, aunque haya varias CTE y solo una sea recursiva; UNION ALL no elimina duplicados y es lo normal, mientras que UNION a secas los elimina en cada paso —protección rudimentaria contra ciclos, pero más cara—; y el término recursivo solo puede referenciar la CTE una vez, sin agregados, sin ORDER BY y sin LIMIT dentro.

Los ocho empleados, con su profundidad y su cadena de mando, y presentados como un árbol gracias a ORDER BY ruta. Compáralo con 03-06: allí hacían falta dos LEFT JOIN encadenados, el número de niveles estaba escrito en la consulta y aun así se llegaba solo hasta el "abuelo". Aquí la consulta no sabe cuántos niveles hay, y funcionaría igual con quince.

Dos variantes de una línea que valen mucho: el subárbol de una persona —cambia el WHERE e.jefe_id IS NULL del término base por WHERE e.id = 2 y obtienes a Andrés y sus tres subordinados, 4 filas—; y un orden estable, porque ruta con nombres depende de cómo ordene los acentos el idioma: acumula una segunda columna con los ids rellenados, a.ruta_id || '.' || lpad(e.id::text, 5, '0'), y ordena por ella.

La recursividad también funciona en sentido contrario: en lugar de bajar de padres a hijos, subir de hijo a padre. La pregunta de marketing es "¿de quién viene, en última instancia, Núria Bosch Ferrer?".

La cadena de tres saltos que 03-06 no podía recorrer: 1 → 2 → 5 → 13. La fila con salto = 3 es el origen de la rama, y se detecta porque su referido_por_id es NULL. Dándole la vuelta al JOIN —ON h.referido_por_id = d.id— y partiendo del cliente 1, se obtiene lo contrario: todos los descendientes de Lucía, que son 5 (Carlos, Marta e Inés en el nivel 1; Ana en el 2; Núria en el 3).

La recursividad no es solo para árboles: sirve para producir filas de la nada — los doce meses del informe, sin ninguna tabla de por medio:

Devuelve doce filas, de 2025-03 a 2026-02: el esqueleto exacto del informe mensual. En PostgreSQL esto se escribe mucho mejor con generate_series (03-06), pero generate_series no existe en MySQL ni en SQLite, y esta es la forma portable de conseguir lo mismo. Fíjate en dónde está la condición de parada: dentro del término recursivo, en su propio WHERE. Si la quitas, la consulta no termina nunca.

El peligro de la recursividad es el ciclo en los datos: si por un error de captura el cliente 1 apareciera como referido por el 13, la consulta 8.2 daría vueltas para siempre generando filas hasta agotar el disco temporal. Cuatro defensas, de la más artesanal a la más limpia:

1. La columna de ruta con = ANY(...). Se acumula el camino recorrido en un array y se rechaza a quien ya esté en él:

2. CYCLE, desde PostgreSQL 14, que es lo mismo escrito por el motor. Se añade tras el paréntesis de cierre de la CTE:

Significa: vigila la columna id, marca con TRUE en es_ciclo la fila en la que se detecte repetición y deja de expandir por ahí; en camino queda el recorrido. Es más corto, más rápido y más difícil de equivocar.

3. Un contador de profundidad, útil cuando además quieres limitar el nivel: WHERE a.nivel < 10 en el término recursivo. 4. El LIMIT de emergencia: SELECT * FROM cadena LIMIT 1000 detiene la ejecución al llegar a mil filas, porque PostgreSQL evalúa la recursión de forma perezosa. Es una red de seguridad para experimentar, no una solución: úsalo mientras desarrollas una recursiva sobre datos que no conoces.

Nota de dialecto:

Motor	Palabra clave	Detalle
PostgreSQL	WITH RECURSIVE obligatorio	CYCLE y SEARCH desde la 14. Sin RECURSIVE, error de "relación no existe"
SQL Server	WITH a secas	RECURSIVE no existe; MAXRECURSION limita a 100 niveles por omisión
MySQL 8 / MariaDB	WITH RECURSIVE	Límite por cte_max_recursion_depth (1000 por omisión)
SQLite	WITH RECURSIVE (la palabra es opcional)	Soporte completo, incluido UNION
Oracle	WITH RECURSIVE, o el clásico CONNECT BY	CONNECT BY PRIOR ... START WITH ... es anterior al estándar y sigue muy vivo, con LEVEL, SYS_CONNECT_BY_PATH y NOCYCLE

• Olvidar RECURSIVE. Sin él, la CTE no puede referirse a sí misma: ERROR: relation "arbol" does not exist. Y va detrás de WITH, no delante del nombre de la CTE recursiva.
• Repetir WITH en cada CTE (se escribe una sola vez; las demás se separan por comas) o referirse a una CTE definida más abajo (solo se ven las anteriores, salvo la autorreferencia de RECURSIVE).
• Escribir un término recursivo sin condición de parada. La consulta no termina. En un árbol, la parada es implícita (se acaban los hijos); en una serie generada, tienes que escribirla tú en el WHERE.
• Ignorar los ciclos en los datos. Una jerarquía con un bucle cuelga la consulta. CYCLE (PG 14+) o la columna de ruta con = ANY(...).
• Creer que una CTE es siempre una barrera de optimización. Lo era hasta PostgreSQL 11. Desde la 12 se integra si se usa una vez; si quieres la barrera, pídela con MATERIALIZED.
• Usar UNION en lugar de UNION ALL "por si acaso". Elimina duplicados en cada paso y cuesta bastante más. Y escribir en la misma tabla desde dos ramas de la misma sentencia: el orden no está garantizado y el resultado es imprevisible.
• Consejo: escribe la consulta por pasos y ejecútala por pasos. Sustituye el SELECT final por SELECT * FROM paso_intermedio y comprueba las filas de cada nivel: 47 → 20 → 12 → 1.
• Consejo: en una recursiva, empieza siempre por el término base solo, comprueba que devuelve exactamente las raíces que esperas y solo entonces añade el UNION ALL. Y acumula siempre nivel y ruta: no cuestan nada y son la mitad del diagnóstico cuando algo sale mal.

Reescribe con CTE el informe de "dos agregados de granularidad distinta" de 07-04: por cliente, número de pedidos, facturación de producto, portes y total. Debe cuadrar en 727,95 € + 118,25 € = 846,20 €. (1) Escríbelo con dos CTE (por_pedido y por_cliente) en lugar de dos tablas derivadas. (2) Añade una tercera que calcule los totales generales y muéstralos junto a los de cada cliente. (3) ¿Se integrarán las CTE o se materializarán? ¿Cómo lo comprobarías?

RR. HH. quiere, para cada empleado, cuántas personas tiene por debajo en total (directas e indirectas). (1) Escribe una CTE recursiva que devuelva todos los pares (jefe, subordinado a cualquier profundidad). (2) Agrega para obtener el recuento por jefe, incluyendo con 0 a quienes no tienen a nadie. (3) Comprueba que Rosa sale con 7 y Andrés con 3.

Un compañero ha escrito esto para archivar las reseñas de productos descatalogados y no entiende el resultado:

1. ¿Qué devuelve el COUNT, 12 u otro número? ¿Por qué?
2. ¿Se ha borrado algo realmente? ¿Cuántas filas?
3. Reescríbelo para que archive de verdad en una tabla resenas_archivo y devuelva cuántas ha movido.

Solución 1

1 y 2:

(2 primeras de 12 filas.) Las columnas cuadran: 727,95 + 118,25 = 846,20 €. Lo que resuelve el problema es lo mismo que en 07-04 —agregar cada cosa a su granularidad antes de unir—, pero ahora los pasos tienen nombre y por_pedido se define una vez y se usa dos veces (desde por_cliente y, a través de ella, desde total_general).

3. por_pedido se usa una sola vez (desde por_cliente), así que se integrará; por_cliente se usa dos veces, así que PostgreSQL la materializará — y aquí es lo deseable, porque calcularla dos veces sería recorrer 47 líneas dos veces. Se comprueba con EXPLAIN (ANALYZE, COSTS OFF): si aparece un nodo CTE Scan on por_cliente, se ha materializado.

Solución 2

(4 primeras de 8 filas; los empleados 5, 6, 7 y 8 cierran también con 0.) Rosa con 7 y Andrés con 3, como pedía el enunciado. Dos ideas hacen que funcione: el término base arranca desde todos los empleados a la vez, no solo desde la raíz, de modo que cada uno construye su propio subárbol; y el COUNT(*) - 1 descuenta la fila (x, x) en la que cada empleado se cuenta a sí mismo — que es justamente lo que permite que los cuatro sin subordinados aparezcan con 0 en lugar de desaparecer.

Solución 3

1. Devuelve 12, es decir, el recuento de antes del borrado. Es la propiedad 1 del apartado 6: todas las partes de la sentencia ven la misma instantánea, la del instante inicial. El SELECT principal no ve el efecto del DELETE de la CTE.

2. Sí se ha borrado, pero cero filas. El único producto con activo = FALSE es el 20 (Cápsulas de espirulina) y no tiene reseñas, así que la CTE devuelve el conjunto vacío. Si el producto descatalogado fuera el 1, se habrían borrado sus 2 reseñas y el COUNT habría seguido diciendo 12 — que es donde está la trampa real del ejercicio. 3. La versión correcta:

Ahora sí: el DELETE alimenta al INSERT, el INSERT devuelve lo insertado y el SELECT final cuenta el resultado de la operación, no el estado de una tabla. Es el patrón "mover filas" completo, en una sentencia y en una transacción.

WITH es la herramienta que convierte SQL en algo que se puede leer:

• Una CTE es una subconsulta con nombre puesta delante. Vive solo durante la sentencia, se puede reutilizar dentro de ella —cosa que una tabla derivada no permite— y no requiere permisos ni deja rastro. Frente a tres niveles de tablas derivadas anidadas, tres CTE encadenadas dicen lo mismo con la mitad de paréntesis y se depuran paso a paso: 47 líneas → 20 pedidos → 12 clientes → 1 media, con 5 clientes por encima de los 60,66 € de media.
• CTE, vista o tabla derivada: un paso de usar y tirar, derivada; dos o más pasos o reutilización, CTE; algo que usarán otras consultas y otras personas, vista.
• Desde PostgreSQL 12 una CTE ya no es una barrera de optimización: se integra si se usa una vez y se materializa si se usa varias. AS MATERIALIZED y AS NOT MATERIALIZED fuerzan cada comportamiento, y el EXPLAIN dice cuál está ocurriendo. Lo que digan los tutoriales anteriores a 2019 sobre esto ya no vale.
• Un WITH puede preceder a un INSERT, UPDATE o DELETE, y una CTE puede ser ella misma una escritura con RETURNING: de ahí el patrón "mover filas", que borra de una tabla e inserta lo borrado en otra en una sola sentencia atómica. Todas las ramas ven la misma instantánea.
• WITH RECURSIVE = término base UNION ALL término recursivo, iterando hasta que no salen filas nuevas. Con él: el organigrama completo con nivel y ruta (8 empleados, 3 niveles), la cadena de referidos de tres saltos 1 → 2 → 5 → 13, y una serie de 12 meses generada sin generate_series. Y los ciclos en los datos, que la cuelgan, se evitan con una columna de ruta y = ANY(...), con CYCLE ... SET ... USING ... desde PostgreSQL 14, con un límite de profundidad o, mientras se experimenta, con un LIMIT de emergencia.

Con las CTE ya sabes descomponer una consulta y recorrer una estructura. Queda el hueco que 04-05 dejó abierto con todas las letras: agregar sin colapsar las filas. Cuando quieres el total del pedido junto a cada una de sus líneas, el porcentaje que representa cada producto sobre la facturación global, la posición de cada cliente en un ranking, cuánto ha variado un mes respecto al anterior o la media móvil de un trimestre, un GROUP BY no sirve: colapsa exactamente lo que quieres conservar. En la lección siguiente, funciones de ventana, verás la cláusula OVER que resuelve todo eso de una vez, la anatomía de PARTITION BY / ORDER BY / marco, por qué no se puede filtrar por una función de ventana en el WHERE —el error más frecuente de todos— y los rankings, acumulados y medias móviles de TiendaVerde calculados sin perder ni una de las 47 líneas.