The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

WITH and CTE: naming an intermediate result

A long query can repeat calculations, mix up too many levels, or hide the main intent. A common table expression, known as CTE, allows you to name an intermediate result and use it immediately afterwards.

The CTE improves the organization of the query, but it does not create a temporary table on its own or guarantee that the result is materialized—that it is calculated once and physically stored for reuse.

The idea

The word WITH introduces the name of the CTE, its optional columns, and the query that defines it. That name is available for a single subsequent instruction: SELECT, INSERT, UPDATE, DELETE, or MERGE.

Syntax

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

The preceding semicolon terminates a possible previous statement. This is a safe way to prevent WITH from being interpreted as part of it.

Example: Calculate totals before filtering

We first summarize the orders by customer. Then we consult that result as if it were a table:

Within the CTE, SUM calculates the total amount of the items and GROUP BY produces one row per customer. The outer query thus receives a set that already contains 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;

The external consultation no longer needs to know every detail of the calculation. It works with a set called VentasPorCliente.

Define multiple CTEs

We can declare several expressions under the same WITH, separated by commas:

;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;

A later CTE may refer to another defined earlier within the same block.

Recursive CTEs

A CTE can also refer to itself to traverse hierarchies or generate sequences. That capability requires an anchor member, which produces the first rows, and a recursive member, which uses preceding rows to produce the next ones. It also needs a terminating condition. It should not be used without controlling depth, duplication, and cost.

What should be checked

A CTE exists only during the statement that follows. If we need to query the same result several times in separate statements, add indexes—structures that organize values to make rows easier to access—or keep it during a process, a temporary table may be more appropriate.

Splitting a query into several CTEs improves readability but does not automatically correct an expensive execution plan. The plan is the strategy chosen by SQL Server; its estimates are the row counts it expects to process. We must still review filters, joins, and the number of times each set is used.