CTE (Common Table Expressions)

Una CTE (Common Table Expression) es una consulta con nombre que defines al inicio de una sentencia SQL usando la cláusula WITH. Funciona como una tabla temporal que solo existe durante la ejecución de esa consulta. Las CTEs resuelven los mismos problemas que las tablas derivadas pero con una sintaxis más clara y una ventaja clave: puedes referenciar la misma CTE varias veces sin tener que repetir la subconsulta.

MySQL soporta CTEs desde la versión 8.0.

Sintaxis

WITH nombre_cte AS (
    SELECT ...
)
SELECT columnas
FROM nombre_cte;

La CTE se define antes de la consulta principal. El nombre que le asignes funciona como un alias de tabla que puedes usar en el SELECT, JOIN, WHERE o cualquier otra parte de la consulta principal.

CTE básica

El uso más directo: dar nombre a un resultado intermedio para trabajar con él después. Clientes cuyo gasto total supera los 1000 euros:

WITH gastos_cliente AS (
    SELECT
        cl.nombre,
        SUM(p.total) AS gasto_total
    FROM clientes cl
    JOIN pedidos p ON cl.id = p.cliente_id
    GROUP BY cl.id, cl.nombre
)
SELECT nombre, gasto_total
FROM gastos_cliente
WHERE gasto_total > 1000
ORDER BY gasto_total DESC;

La CTE gastos_cliente calcula el gasto total por cliente. La consulta principal filtra los que superan 1000 euros. Este mismo resultado se puede conseguir con una tabla derivada, pero la CTE separa la definición del uso, lo que facilita la lectura.

Reutilizar una CTE

La ventaja más importante de las CTEs sobre las tablas derivadas: puedes referenciar la misma CTE varias veces en la consulta principal. Clientes cuyo gasto supera la media de gasto entre clientes:

WITH gastos_cliente AS (
    SELECT
        cl.nombre,
        SUM(p.total) AS gasto_total
    FROM clientes cl
    JOIN pedidos p ON cl.id = p.cliente_id
    GROUP BY cl.id, cl.nombre
)
SELECT nombre, gasto_total
FROM gastos_cliente
WHERE gasto_total > (SELECT AVG(gasto_total) FROM gastos_cliente)
ORDER BY gasto_total DESC;

La CTE gastos_cliente se referencia dos veces: una en el FROM y otra en el WHERE para calcular la media. Con tablas derivadas tendrías que escribir la misma subconsulta dos veces (como vimos en el artículo anterior). Con la CTE, la defines una vez y la usas las veces que necesites.

La media de gasto entre los 19 clientes con pedidos es aproximadamente 596.09 euros. Los 7 clientes que aparecen la superan. Javier entra por poco con 599.99 euros.

Múltiples CTEs

Puedes definir varias CTEs separadas por comas. Cada una puede referenciar a las anteriores. Resumen de ventas por categoría con unidades vendidas y productos distintos:

WITH ventas_producto AS (
    SELECT
        pr.id AS producto_id,
        pr.categoria_id,
        SUM(dp.cantidad) AS unidades_vendidas
    FROM detalle_pedidos dp
    JOIN productos pr ON dp.producto_id = pr.id
    GROUP BY pr.id, pr.categoria_id
),
resumen_categoria AS (
    SELECT
        categoria_id,
        COUNT(*) AS productos_vendidos,
        SUM(unidades_vendidas) AS total_unidades
    FROM ventas_producto
    GROUP BY categoria_id
)
SELECT
    c.nombre AS categoria,
    rc.productos_vendidos,
    rc.total_unidades
FROM resumen_categoria rc
JOIN categorias c ON rc.categoria_id = c.id
ORDER BY rc.total_unidades DESC;

La primera CTE calcula las unidades vendidas de cada producto. La segunda CTE referencia a la primera para agregar por categoría. La consulta principal une con la tabla de categorías para obtener los nombres. Este encadenamiento de CTEs es imposible con tablas derivadas, donde cada subconsulta es independiente.

Accesorios electrónicos lidera con 7 unidades vendidas, aunque solo 2 de sus 3 productos se han vendido (el Cargador USB-C 65W no tiene ventas).

CTE con JOIN

Las CTEs funcionan como tablas normales en los JOINs. Comparar las ventas de cada empleado con la media del equipo:

WITH ventas_empleado AS (
    SELECT
        e.id AS empleado_id,
        e.nombre,
        COUNT(*) AS pedidos,
        SUM(p.total) AS ingresos
    FROM empleados e
    JOIN pedidos p ON e.id = p.empleado_id
    GROUP BY e.id, e.nombre
)
SELECT
    ve.nombre,
    ve.pedidos,
    ROUND(ve.ingresos, 2) AS ingresos,
    ROUND(AVG(ve.ingresos) OVER (), 2) AS media_equipo,
    ROUND(ve.ingresos - AVG(ve.ingresos) OVER (), 2) AS diferencia
FROM ventas_empleado ve
ORDER BY ve.ingresos DESC;

Solo 3 empleados gestionan pedidos (Natalia, Daniel y Patricia). Natalia destaca con más del doble de ingresos que Patricia, generando casi el 57% del total.

Informe con porcentajes

Las CTEs son especialmente útiles para informes que necesitan porcentajes sobre el total. Distribución de pedidos por estado:

WITH pedidos_por_estado AS (
    SELECT
        estado,
        COUNT(*) AS cantidad,
        SUM(total) AS importe
    FROM pedidos
    GROUP BY estado
)
SELECT
    estado,
    cantidad,
    ROUND(importe, 2) AS importe,
    ROUND(100.0 * cantidad / (SELECT SUM(cantidad) FROM pedidos_por_estado), 1) AS porcentaje
FROM pedidos_por_estado
ORDER BY cantidad DESC;

La CTE se referencia dos veces: una en el FROM principal y otra en la subconsulta del porcentaje. Los pedidos entregados y pendientes empatan en cantidad (7 cada uno), pero los entregados representan mucho más importe. Los cancelados son solo el 12% y suman menos de 900 euros.

CTEs vs tablas derivadas

Las CTEs y las tablas derivadas resuelven los mismos problemas, pero cada una tiene ventajas distintas.

Las CTEs son mejores cuando necesitas referenciar el mismo resultado intermedio más de una vez, cuando tienes varios niveles de transformación encadenados (una CTE que referencia a otra), o cuando la legibilidad es prioritaria. Además, las CTEs permiten recursión, algo imposible con tablas derivadas.

Las tablas derivadas son más concisas para casos simples donde solo necesitas un resultado intermedio una vez. También tienen una ligera ventaja: al estar integradas en el FROM, el optimizador de MySQL puede fusionarlas con la consulta exterior en algunos casos.

En la práctica, la recomendación es usar CTEs como primera opción para consultas complejas y reservar las tablas derivadas para transformaciones simples y puntuales.

Practica con CTE

Usa el editor para definir expresiones de tabla comunes con WITH:

En el siguiente artículo exploraremos las CTEs recursivas, que permiten resolver problemas de jerarquías y secuencias que no se pueden abordar con SQL convencional.