The SQL Booklet

Buscar en SQL Server

Escribe dos caracteres o más.

English

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 materialice, es decir, que se calcule una vez y se almacene físicamente para reutilizarlo.

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:

Dentro de la CTE, SUM calcula el importe total de las partidas y GROUP BY produce una fila por cliente. La consulta exterior recibe así un conjunto que ya contiene TotalVendido.

;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, que produce las primeras filas, y otro recursivo, que utiliza las filas anteriores para producir las siguientes. También necesita 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 —estructuras que organizan valores para facilitar el acceso a filas— 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 de ejecución costoso. El plan es la estrategia elegida por SQL Server; sus estimaciones son las cantidades de filas que espera procesar. Debemos seguir revisando filtros, uniones y número de veces que se utiliza cada conjunto.

Relacionado con