Common Table Expressions (CTE) en SQL

Una CTE (Common Table Expression) es un conjunto de resultados temporal y con nombre que se define y existe únicamente durante la ejecución de una sola consulta.

Imagina que has escrito una consulta con tres subconsultas anidadas dentro del FROM, dos en el WHERE y un JOIN a otra subconsulta.

Si intentas volver a leer o modificar ese script una semana después… probablemente no entiendas absolutamente nada de lo que hace.

Para solucionar este caos SQL Server introdujo las CTEs. Su principal propósito no es mejorar el rendimiento (que suele ser igual al de una subconsulta), sino mejorar la legibilidad y organización de nuestras consultas.

Sintaxis básica: La cláusula WITH

Una CTE se define antes de tu consulta principal usando la palabra clave WITH.

WITH VentasTotales AS (
    -- Aquí va tu consulta compleja
    SELECT ClienteID, SUM(Total) as TotalGastado
    FROM Pedidos
    GROUP BY ClienteID
)

-- Aquí empieza la consulta real que usa la CTE
SELECT 
    C.Nombre,
    CTE.TotalGastado
FROM Clientes C

INNER JOIN VentasTotales CTE ON C.ClienteID = CTE.ClienteID

WHERE CTE.TotalGastado > 1000;

Encadenando CTEs

La verdadera gracia surge cuando defines múltiples CTEs, donde la segunda lee de la primera, la tercera de la segunda, etc. Esto nos permite programar de forma “procedimental” y lógica.

Imagina que queremos:

Con subconsultas sería un infierno. Con CTEs es un paseo:

WITH VentasPorEmpleado AS (
    -- Paso 1: Datos crudos agrupados
    SELECT EmpleadoID, SUM(Importe) as Total
    FROM Ventas
    GROUP BY EmpleadoID
),

PromedioGlobal AS (
    -- Paso 2: Usamos el paso 1 para calcular el promedio
    SELECT AVG(Total) as MediaEmpresa
    FROM VentasPorEmpleado
)

-- Paso 3: Cruzamos ambos resultados
SELECT 
    V.EmpleadoID, 
    V.Total,
    P.MediaEmpresa
FROM VentasPorEmpleado V
CROSS JOIN PromedioGlobal P
WHERE V.Total > P.MediaEmpresa;

CTEs Recursivas

Hasta aquí, las CTEs son “azúcar sintáctico” para ordenar código. Pero hay algo que las subconsultas normales no pueden hacer y las CTEs sí: llamarse a sí mismas.

Estructura de una recursión

Se compone de dos partes unidas por un UNION ALL:

Vamos a verlo con un ejemplo de un organigrama de una empresa,

Tenemos una tabla Empleados con ID y JefeID. Queremos listar a todos los empleados y saber en qué nivel de la jerarquía están.

Sería algo así

WITH Organigrama AS (
    -- 1. ANCLA: Buscamos al CEO (El que no tiene jefe o JefeID es NULL)
    SELECT EmpleadoID, Nombre, JefeID, 1 AS Nivel
    FROM Empleados
    WHERE JefeID IS NULL
    
    UNION ALL
    
    -- 2. RECURSIVO: Buscamos a los empleados cuyo Jefe está en el paso anterior
    SELECT 
        E.EmpleadoID, 
        E.Nombre, 
        E.JefeID, 
        O.Nivel + 1 -- Aumentamos el nivel
    FROM Empleados E
    INNER JOIN Organigrama O ON E.JefeID = O.EmpleadoID
)
-- Usamos el resultado final
SELECT * FROM Organigrama ORDER BY Nivel;