Common Table Expressions (CTE) en MySQL

MySQL · Bases de Datos II · TUP – UTN Mar del Plata

¿Qué es una CTE?

Una Common Table Expression (CTE) es una tabla temporal con nombre, definida dentro de una consulta mediante la cláusula WITH. Existe únicamente durante la ejecución de esa consulta y no se almacena de forma permanente en la base de datos.

Las CTE permiten estructurar consultas complejas dividiéndolas en bloques lógicos con nombre, facilitando la lectura, el mantenimiento y la reutilización de resultados intermedios dentro de la misma consulta.

Compatibilidad: Las CTE (cláusula WITH) están disponibles a partir de MySQL 8.0. En versiones anteriores, la alternativa eran las subconsultas en el FROM o las vistas temporales.

Tipos de CTE

CTE simples (no recursivas): definen un resultado temporal que puede referenciarse una o más veces dentro de la consulta principal. No se autoreferencian.
CTE recursivas: se autoreferencian mediante WITH RECURSIVE, lo que permite recorrer estructuras jerárquicas (organigramas, categorías anidadas, árboles genealógicos).

CTE simples

Sintaxis básica

WITH nombre_cte AS (
    SELECT columnas
    FROM tabla
    WHERE condicion
)
SELECT *
FROM nombre_cte;

CTE con múltiples bloques

Se pueden definir varias CTE separadas por coma antes de la consulta principal:

WITH
    cte1 AS (
        SELECT ...
    ),
    cte2 AS (
        SELECT ...
        FROM cte1     -- cte2 puede referenciar a cte1
        JOIN ...
    )
SELECT *
FROM cte2;

Ejemplos prácticos: CTE simples

Ejemplo 1 – Filtrado y agregación

Calcular el promedio de salario de los empleados con sueldo mayor a $3.000:

WITH empleados_altos AS (
    SELECT empleado_id, salario
    FROM empleados
    WHERE salario > 3000
)
SELECT AVG(salario) AS promedio_salario
FROM empleados_altos;

Ejemplo 2 – CTE con JOIN

Listar empleados junto con el nombre de su departamento:

WITH empleados_departamentos AS (
    SELECT e.empleado_id, e.nombre, d.nombre_departamento
    FROM empleados e
    JOIN departamentos d ON e.departamento_id = d.departamento_id
)
SELECT *
FROM empleados_departamentos
ORDER BY nombre_departamento;

Ejemplo 3 – Reutilización de la CTE

La misma CTE se puede referenciar más de una vez dentro de la misma consulta:

WITH ventas_mensuales AS (
    SELECT
        YEAR(fecha)  AS anio,
        MONTH(fecha) AS mes,
        SUM(total)   AS total_mes
    FROM ventas
    GROUP BY YEAR(fecha), MONTH(fecha)
)
SELECT
    anio,
    mes,
    total_mes,
    (SELECT AVG(total_mes) FROM ventas_mensuales) AS promedio_general
FROM ventas_mensuales
ORDER BY anio, mes;

Ejemplo 4 – Cliente con mayor facturación

Usando una CTE en lugar de una subconsulta anidada:

WITH ventas_totales AS (
    SELECT cliente_id, SUM(total) AS total_ventas
    FROM ventas
    GROUP BY cliente_id
)
SELECT c.cliente_id, c.nombre, v.total_ventas
FROM clientes c
JOIN ventas_totales v ON c.cliente_id = v.cliente_id
ORDER BY v.total_ventas DESC
LIMIT 1;

CTE recursivas

¿Cuándo usar una CTE recursiva?

Las CTE recursivas son la herramienta adecuada cuando los datos tienen una estructura jerárquica o de árbol, es decir, cuando cada fila puede referenciar a otra fila de la misma tabla como su "padre":

Organigramas (empleado → jefe → gerente → director).
Categorías de productos anidadas.
Comentarios con respuestas anidadas.
Árbol genealógico.

Estructura de una CTE recursiva

Toda CTE recursiva tiene dos partes obligatorias, unidas con UNION ALL:

WITH RECURSIVE nombre_cte AS (

    -- 1. PARTE ANCLA (punto de partida, sin autorreferencia)
    SELECT columnas
    FROM tabla
    WHERE condicion_inicial

    UNION ALL

    -- 2. PARTE RECURSIVA (se autorreferencia a nombre_cte)
    SELECT t.columnas
    FROM tabla t
    JOIN nombre_cte n ON t.columna_padre = n.columna_id

)
SELECT * FROM nombre_cte;

¿Cómo funciona?

Se ejecuta la parte ancla → genera el conjunto inicial de filas.
La parte recursiva toma las filas del paso anterior y busca sus hijos en la tabla.
El proceso se repite hasta que la parte recursiva no genera nuevas filas.
Todos los resultados se unen (UNION ALL) en el resultado final.

Ejemplo 1 – Jerarquía de empleados

WITH RECURSIVE jerarquia AS (

    -- Ancla: empleados sin jefe (nivel más alto)
    SELECT id, nombre, id_jefe, 1 AS nivel
    FROM empleados
    WHERE id_jefe IS NULL

    UNION ALL

    -- Recursivo: empleados cuyo jefe ya está en la CTE
    SELECT e.id, e.nombre, e.id_jefe, h.nivel + 1
    FROM empleados e
    JOIN jerarquia h ON e.id_jefe = h.id

)
SELECT
    REPEAT('  ', nivel - 1) AS sangria,
    nombre,
    nivel
FROM jerarquia
ORDER BY nivel, nombre;

Ejemplo 2 – Secuencia numérica con CTE recursiva

Las CTE recursivas también permiten generar series de valores sin necesidad de tablas auxiliares:

WITH RECURSIVE numeros AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numeros WHERE n < 10
)
SELECT n FROM numeros;
-- Resultado: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Ejemplo 3 – Categorías anidadas

-- Estructura de la tabla:
-- categorias(id, nombre, id_padre)  -- id_padre NULL indica categoría raíz

WITH RECURSIVE arbol_categorias AS (

    -- Ancla: categorías raíz
    SELECT id, nombre, id_padre, 0 AS profundidad, CAST(nombre AS CHAR(500)) AS ruta
    FROM categorias
    WHERE id_padre IS NULL

    UNION ALL

    -- Recursivo: subcategorías
    SELECT c.id, c.nombre, c.id_padre, a.profundidad + 1,
           CONCAT(a.ruta, ' > ', c.nombre)
    FROM categorias c
    JOIN arbol_categorias a ON c.id_padre = a.id

)
SELECT ruta, profundidad
FROM arbol_categorias
ORDER BY ruta;

Precaución con recursividad infinita: Si los datos tienen referencias circulares (A → B → A), la CTE recursiva puede entrar en un bucle infinito. MySQL tiene una variable de sistema cte_max_recursion_depth (por defecto 1.000 iteraciones) que detiene la recursión y lanza un error. Se puede ajustar con SET SESSION cte_max_recursion_depth = 5000;

Subconsultas vs CTE

Las CTE y las subconsultas resuelven problemas similares, pero con enfoques diferentes. La siguiente tabla muestra sus diferencias clave:

Característica	Subconsultas	CTE (Common Table Expressions)
Definición	Consultas anidadas dentro de SELECT, FROM o WHERE	Bloques temporales con nombre, definidos con WITH
Sintaxis	Embebida en la cláusula que la usa	Definida antes de la consulta principal
Legibilidad	Puede ser difícil de leer con anidación profunda	Mayor legibilidad y organización del código
Reutilización	No se puede referenciar dos veces en la misma consulta	Se puede referenciar múltiples veces
Recursividad	No soporta recursividad	Sí, mediante WITH RECURSIVE
Compatibilidad	Todas las versiones de MySQL	Requiere MySQL 8.0 o superior
Depuración	Más difícil de aislar y probar por partes	Cada CTE puede ejecutarse de forma independiente

Ejemplo comparativo: cliente con mayor facturación

Con subconsulta anidada:

SELECT cliente_id, nombre
FROM clientes
WHERE cliente_id = (
    SELECT cliente_id
    FROM ventas
    GROUP BY cliente_id
    ORDER BY SUM(total) DESC
    LIMIT 1
);

Con CTE equivalente:

WITH ventas_totales AS (
    SELECT cliente_id, SUM(total) AS total_ventas
    FROM ventas
    GROUP BY cliente_id
)
SELECT c.cliente_id, c.nombre
FROM clientes c
JOIN ventas_totales v ON c.cliente_id = v.cliente_id
ORDER BY v.total_ventas DESC
LIMIT 1;

Ambas consultas producen el mismo resultado. La versión CTE es más fácil de leer, especialmente cuando la lógica de cálculo es compleja o se reutiliza en varios puntos de la consulta.

¿Cuándo usar cada una?

Usá CTE cuando necesités legibilidad, reutilizar resultados intermedios, o manejar datos jerárquicos con recursividad.
Usá subconsultas para casos muy simples, cuando la consulta es de una sola línea, o cuando necesitás compatibilidad con MySQL < 8.0.

CTE en combinación con otros objetos de MySQL

CTE con UDF (funciones definidas por el usuario)

Una UDF puede ser invocada dentro del bloque de una CTE, combinando la legibilidad de las CTE con la lógica encapsulada en la función:

-- Suponiendo que existe la función calcular_descuento(precio)
WITH precios_finales AS (
    SELECT
        producto_id,
        nombre,
        precio,
        calcular_descuento(precio) AS precio_con_descuento
    FROM productos
    WHERE activo = 1
)
SELECT *
FROM precios_finales
WHERE precio_con_descuento < 1000
ORDER BY precio_con_descuento;

CTE dentro de procedimientos almacenados

Las CTE también pueden usarse dentro del cuerpo de un procedimiento almacenado:

DELIMITER //

CREATE PROCEDURE reporte_ventas_top(IN top_n INT)
BEGIN
    WITH ranking_clientes AS (
        SELECT
            c.nombre,
            SUM(v.total) AS total_compras,
            COUNT(*) AS cantidad_compras,
            RANK() OVER (ORDER BY SUM(v.total) DESC) AS posicion
        FROM clientes c
        JOIN ventas v ON c.cliente_id = v.cliente_id
        GROUP BY c.cliente_id, c.nombre
    )
    SELECT *
    FROM ranking_clientes
    WHERE posicion <= top_n;
END //

DELIMITER ;

CALL reporte_ventas_top(5);

Ventajas de las CTE

Ventaja	Descripción
Legibilidad	Dividen consultas complejas en bloques lógicos con nombre, facilitando la comprensión del código.
Reutilización	La misma CTE puede referenciarse múltiples veces en la consulta, evitando repetir subconsultas.
Recursividad	Con WITH RECURSIVE permiten recorrer estructuras jerárquicas que serían imposibles con subconsultas comunes.
Mantenibilidad	Al estar nombradas y separadas, es más fácil modificar o depurar cada parte de la consulta.
No persisten	No crean objetos permanentes en la base de datos; desaparecen al finalizar la ejecución de la consulta.

Conclusión: Las CTE representan una herramienta fundamental del SQL moderno (MySQL 8.0+). Junto con las UDF bien diseñadas, permiten escribir consultas más claras, mantenibles y poderosas. Incorporar CTE en el flujo de trabajo es especialmente valioso en consultas analíticas, reportes y cualquier escenario que involucre datos jerárquicos o resultados intermedios reutilizables.