CTE en SQL: las expresiones de tabla comunes explicadas de forma sencilla

Una CTE (Common Table Expression, expresión de tabla común) es el resultado de una consulta con nombre propio que solo existe dentro de una única consulta SQL. Se declara con la cláusula WITH, y a partir de ahí puedes referirte a ella por su nombre, como a una tabla normal:

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

En el fondo, una CTE es una subconsulta a la que se le ha dado un nombre y que se ha llevado al principio de la consulta. Este truco tan sencillo cambia por completo la legibilidad: una consulta compleja se convierte en una secuencia de pasos fáciles de entender.

Una subconsulta con nombre propio

Busquemos los pedidos que superan el ticket medio en la base de datos de un servicio de reparto. Con una subconsulta queda así:

MySQL 8.4
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC, order_id
LIMIT 5;

Y ahora lo mismo con una CTE:

MySQL 8.4
WITH avg_check AS (
    SELECT AVG(total_amount) AS avg_amount FROM orders
)
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT avg_amount FROM avg_check)
ORDER BY total_amount DESC, order_id
LIMIT 5;

En una consulta tan corta la ventaja todavía no se nota. Aparece cuando hay más de un paso.

Varias CTE en una misma consulta

Las CTE se pueden declarar en cadena, separadas por comas, y cada una puede usar las anteriores. Busquemos los productos que generan más ingresos que la media de los productos de su categoría:

MySQL 8.4
WITH product_revenue AS (
    SELECT p.category, p.name, SUM(o.total_amount) AS revenue
    FROM orders o
    JOIN products p ON p.product_id = o.product_id
    GROUP BY p.category, p.name
),
category_avg AS (
    SELECT category, AVG(revenue) AS avg_revenue
    FROM product_revenue
    GROUP BY category
)
SELECT pr.name, pr.category, pr.revenue, ROUND(ca.avg_revenue, 2) AS category_avg
FROM product_revenue pr
JOIN category_avg ca ON ca.category = pr.category
WHERE pr.revenue > ca.avg_revenue
ORDER BY pr.category, pr.revenue DESC;

La consulta se lee de arriba abajo, como una receta: primero calculamos los ingresos por producto, luego la media por categoría y al final comparamos. La misma consulta con subconsultas anidadas habría que leerla de dentro hacia fuera, y además los ingresos por producto, que se usan dos veces, habría que escribirlos dos veces. Puedes modificar la consulta aquí mismo: cambia la comparación del WHERE a < y verás los productos que se quedan por debajo de la media de su categoría.

CTE, subconsulta o tabla temporal

Las tres herramientas resuelven un problema parecido: «guardar un resultado intermedio en algún sitio». La diferencia está en cuánto viven y en lo que permiten hacer:

Regla práctica: la subconsulta, para algo pequeño y de un solo uso; la CTE, para ganar legibilidad y reutilizar dentro de la consulta; la tabla temporal, cuando el resultado lo necesitan varias consultas seguidas o hace falta un índice sobre él.

¿En qué se diferencia de verdad una CTE de una tabla temporal creada con CREATE TEMPORARY TABLE?

CTE recursivas

Una CTE recursiva es la única forma en SQL estándar de recorrer una estructura cuya profundidad no se conoce de antemano: un organigrama, un árbol de categorías, un hilo de comentarios. Es un clásico de las entrevistas para puestos de nivel intermedio.

Empecemos con el ejemplo mínimo: generar los números del 1 al 5:

MySQL 8.4
WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 5
)
SELECT n FROM numbers;

Una CTE recursiva siempre tiene tres partes:

• El ancla, SELECT 1 AS n: la fila de partida, que se ejecuta una sola vez.
• La parte recursiva, SELECT n + 1 FROM numbers: hace referencia a la propia CTE y se ejecuta una y otra vez, recibiendo cada vez como entrada las filas del paso anterior.
• La condición de parada, WHERE n < 5: en cuanto la parte recursiva devuelve un resultado vacío, el recorrido termina. Sin esta condición, la consulta entraría en un bucle infinito.

¿Qué pasa si quitas la condición de parada de una CTE recursiva?

Recorrer una jerarquía: el organigrama

Ahora un problema real. Tenemos una tabla de empleados en la que cada uno hace referencia a su responsable:

MySQL 8.4
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
);

La pregunta «muestra todos los empleados con su nivel jerárquico» no se resuelve con un JOIN normal: no sabemos cuántos niveles tiene la jerarquía. Una CTE recursiva la resuelve en una sola consulta:

MySQL 8.4
WITH RECURSIVE hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
)
SELECT employee_id, name, level
FROM hierarchy
ORDER BY level, employee_id;

El ancla ha encontrado a la directora (manager_id IS NULL), el primer paso de la recursión a sus subordinados directos, el segundo a los subordinados de estos, y así hasta el final. Aquí la parada se produce sola: cuando un nivel no tiene subordinados, el JOIN devuelve un resultado vacío.

El mismo patrón sirve para categorías de productos, menús anidados, hilos de comentarios: cualquier tabla en la que una fila hace referencia a su padre.

Cuándo una CTE puede ralentizar la consulta

Las CTE sirven para la legibilidad, no para la velocidad. En la mayoría de los casos el optimizador despliega una CTE igual que una subconsulta, y no hay diferencia de rendimiento. Pero hay algunos matices que conviene conocer:

• Materialización. El SGBD puede calcular la CTE completa en un área temporal y solo después aplicar los filtros externos. Hasta la versión 12, PostgreSQL lo hacía siempre: la condición WHERE de fuera de la CTE no «bajaba» a su interior, y una CTE de millones de filas se calculaba entera. Desde PostgreSQL 12, las CTE que se usan una sola vez se integran en la consulta, y el comportamiento se puede controlar con MATERIALIZED / NOT MATERIALIZED.
• Reutilización. Si la CTE se usa varias veces en la consulta, el SGBD puede calcularla una vez y reutilizarla, lo cual es una ventaja. Pero también puede calcularla dos veces: revisa el plan de ejecución con EXPLAIN.
• Recursión sin límites. Un error en la condición de parada o un ciclo en los datos (un empleado que es su propio jefe) provoca una recursión infinita. MySQL la corta por defecto a las 1000 iteraciones (cte_max_recursion_depth); PostgreSQL seguirá hasta chocar con sus límites, así que con datos poco fiables conviene añadir un seguro del tipo WHERE level < 100.

Siguientes pasos

• la sintaxis básica de WITH, con ejercicios, en la lección La cláusula WITH;
• cómo funcionan las subconsultas con las que hemos comparado las CTE, en la lección Consultas SQL anidadas;
• puedes practicar las CTE con problemas reales en la Práctica SQL.