`WITH` y CTE: nombrar un resultado intermedio

Una consulta extensa puede repetir cálculos, mezclar demasiados niveles o esconder la intención principal. Una expresión de tabla común, conocida como CTE, permite dar nombre a un resultado intermedio y utilizarlo inmediatamente después.

La CTE mejora la organización de la consulta, pero no crea por sí sola una tabla temporal ni garantiza que el resultado se almacene físicamente.

La idea

La palabra WITH introduce el nombre de la CTE, sus columnas opcionales y la consulta que la define. Ese nombre queda disponible para una sola instrucción posterior: SELECT, INSERT, UPDATE, DELETE o MERGE.

Sintaxis

;WITH NombreCTE AS
(
    SELECT Columna1, Columna2
    FROM dbo.Tabla
    WHERE Condicion
)
SELECT Columna1, Columna2
FROM NombreCTE;

El punto y coma anterior termina una posible instrucción previa. Es una forma segura de evitar que WITH sea interpretado como parte de ella.

Ejemplo: calcular totales antes de filtrar

Primero resumimos los pedidos por cliente. Después consultamos ese resultado como si fuera una tabla:

;WITH VentasPorCliente AS
(
    SELECT
        p.IdCliente,
        SUM(
            pd.Cantidad
            * pd.PrecioUnitario
            * (1 - pd.Descuento / 100.0)
        ) AS TotalVendido
    FROM dbo.Pedido AS p
    INNER JOIN dbo.PedidoDetalle AS pd
        ON pd.IdPedido = p.IdPedido
    WHERE p.Estado <> 'Cancelado'
    GROUP BY p.IdCliente
)
SELECT
    c.IdCliente,
    c.Nombre,
    v.TotalVendido
FROM VentasPorCliente AS v
INNER JOIN dbo.Cliente AS c
    ON c.IdCliente = v.IdCliente
WHERE v.TotalVendido >= 500
ORDER BY v.TotalVendido DESC;

La consulta exterior ya no necesita conocer cada detalle del cálculo. Trabaja con un conjunto llamado VentasPorCliente.

Definir varias CTE

Podemos declarar varias expresiones bajo el mismo WITH, separadas por comas:

;WITH PedidosValidos AS
(
    SELECT IdPedido, IdCliente
    FROM dbo.Pedido
    WHERE Estado <> 'Cancelado'
),
CantidadPorCliente AS
(
    SELECT
        IdCliente,
        COUNT(*) AS CantidadPedidos
    FROM PedidosValidos
    GROUP BY IdCliente
)
SELECT
    c.Nombre,
    cp.CantidadPedidos
FROM CantidadPorCliente AS cp
INNER JOIN dbo.Cliente AS c
    ON c.IdCliente = cp.IdCliente;

Una CTE posterior puede referirse a otra definida antes dentro del mismo bloque.

CTE recursivas

Una CTE también puede referirse a sí misma para recorrer jerarquías o generar secuencias. Esa capacidad requiere un miembro inicial, otro recursivo y una condición que permita terminar. No debe utilizarse sin controlar profundidad, duplicaciones y costo.

Qué conviene comprobar

La CTE existe únicamente durante la instrucción que sigue. Si necesitamos consultar el mismo resultado varias veces en instrucciones separadas, agregar índices o conservarlo durante un proceso, una tabla temporal puede ser más apropiada.

Dividir una consulta en varias CTE mejora su lectura, pero no corrige automáticamente un plan costoso. Debemos seguir revisando filtros, uniones, estimaciones y número de veces que se utiliza cada conjunto.

Relacionado con

Subconsultas: expresan resultados internos sin asignarles un nombre reutilizable.
Tablas temporales: conservan datos durante varias instrucciones y pueden indexarse.
ROW_NUMBER y OVER: suelen combinarse con CTE para elegir filas por grupo.
UPDATE y DELETE: pueden operar sobre resultados identificados mediante una CTE.