WITH common_table_expression (Transact-SQL)

Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Se deriva de una consulta simple y se define en el ámbito de ejecución de una sola instrucción SELECT, INSERT, UPDATE o DELETE. Esta cláusula también se puede utilizar en una instrucción CREATE VIEW como parte de la instrucción SELECT que la define. Una expresión de tabla común puede incluir referencias a ella misma. Esto se conoce como expresión de tabla común recursiva.

Convenciones de sintaxis de Transact-SQL

Sintaxis

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

Argumentos

• expression_name
  Es un identificador válido de la expresión de tabla común. expression_name debe ser diferente del nombre de cualquier otra expresión de tabla común definida en la misma cláusula WITH <common_table_expression>, pero expression_name puede coincidir con el nombre de una vista o tabla base. Cualquier referencia a expression_name en la consulta utiliza la expresión de tabla común y no el objeto base.

• column_name
  Especifica un nombre de columna en la expresión de tabla común. No se permiten nombres duplicados en una misma definición de CTE. El número de nombres de columna especificado debe coincidir con el número de columnas del conjunto de resultados de CTE_query_definition. La lista de nombres de columna es opcional solamente si en la definición de la consulta se suministran nombres diferentes para todas las columnas resultantes.

• CTE_query_definition
  Especifica una instrucción SELECT cuyo conjunto de resultados llena la expresión de tabla común. La instrucción SELECT de CTE_query_definition debe cumplir los mismos requisitos que en la creación de una vista, excepto que una expresión CTE no puede definir otra expresión CTE. Para obtener más información, vea la sección Notas y el tema CREATE VIEW (Transact-SQL).

  Si se definen varios parámetros CTE_query_definition, las definiciones de consulta deben combinarse mediante uno de estos operadores de conjunto: UNION ALL, UNION, EXCEPT o INTERSECT. Para obtener más información sobre cómo utilizar definiciones de consulta de CTE recursivas, vea la siguiente sección Notas y el tema Consultas recursivas mediante expresiones de tabla comunes.

Notas

Directrices para crear y usar expresiones CTE

Estas directrices se aplican únicamente a las expresiones CTE no recursivas. Las directrices aplicables a las expresiones CTE recursivas se encuentran en la sección "Directrices para definir y usar expresiones CTE recursivas", a continuación.

• Una expresión CTE debe ir seguida de una única instrucción SELECT, INSERT, UPDATE o DELETE que haga referencia a una parte o a la totalidad de sus columnas. Una expresión CTE también se puede especificar en una instrucción CREATE VIEW como parte de la instrucción SELECT de definición de la vista.
• Se pueden especificar varias definiciones de consulta de CTE en una CTE no recursiva. Las definiciones deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT.
• Una expresión CTE puede hacer referencia a ella misma y a otras expresiones CTE previamente definidas en la misma cláusula WITH. No se permite la referencia adelantada.
• No se permite especificar más de una cláusula WITH en una expresión CTE. Por ejemplo, si un argumento CTE_query_definition contiene una subconsulta, ésta no puede contener una cláusula WITH anidada que defina otra expresión CTE.
• No se pueden utilizar las cláusulas siguientes en la definición de CTE_query_definition:
  • COMPUTE o COMPUTE BY
  • ORDER BY (excepto cuando se especifica una cláusula TOP)
  • INTO
  • Cláusula OPTION con sugerencias de consulta
  • FOR XML
  • FOR BROWSE
• Cuando se utiliza una expresión CTE en una instrucción que forma parte de un lote, la instrucción que la precede debe ir seguida de punto y coma.
• Una consulta que haga referencia a una CTE se puede utilizar para definir un cursor.
• En la expresión CTE se puede hacer referencia a tablas de servidores remotos.
• Cuando se ejecuta una CTE, todas las sugerencias que hagan referencia a ella pueden entrar en conflicto con otras sugerencias detectadas cuando la CTE tiene acceso a sus tablas subyacentes, de la misma manera que las sugerencias que hacen referencia a vistas en las consultas. En ese caso, la consulta devuelve un error. Para obtener más información, vea Resolución de vistas.

Directrices para definir y usar expresiones CTE recursivas

Estas directrices se aplican a la definición de expresiones CTE recursivas.

• La definición de la CTE recursiva debe contener al menos dos definiciones de consulta de CTE, un miembro delimitador y un miembro recursivo. Se pueden definir varios miembros delimitadores y recursivos, aunque todas las definiciones de consultas de miembros delimitadores deben colocarse delante de la primera definición de miembro recursivo. Todas las definiciones de consulta de CTE**son miembros delimitadores a menos que hagan referencia a la propia CTE.
• Los miembros delimitadores deben combinarse mediante uno de estos operadores de conjuntos: UNION ALL, UNION, INTERSECT o EXCEPT. UNION ALL es el único operador de conjuntos permitido entre el último miembro delimitador y el primer miembro recursivo, y si se combinan varios miembros recursivos.
• El número de columnas de los miembros delimitador y recursivo debe coincidir.
• El tipo de datos de una columna del miembro recursivo debe ser igual al tipo de datos de la columna correspondiente en el miembro delimitador.
• La cláusula FROM de un miembro recursivo sólo debe hacer referencia una vez a expression_name de CTE.
• No se permiten los siguientes elementos en el parámetro CTE_query_definition de un miembro recursivo:
  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Agregación escalar
  • TOP
  • LEFT, RIGHT, OUTER JOIN (se permite INNER JOIN)
  • Subconsultas
  • Una sugerencia aplicada a una referencia recursiva a una CTE dentro de CTE_query_definition.

Las siguientes directrices se aplican al uso de expresiones CTE recursivas.

• Todas las columnas devueltas por la expresión CTE recursiva aceptan valores NULL independientemente de si las columnas devueltas por las instrucciones SELECT participantes aceptan o no valores NULL.
• Una expresión CTE formada incorrectamente puede generar un bucle infinito. Por ejemplo, si la definición de la consulta del miembro recursivo devuelve los mismos valores para las columnas primaria y secundarias, se crea un bucle infinito. Para evitar que se genere un bucle infinito, se puede limitar el número de niveles de recursividad permitidos para una instrucción determinada mediante el uso de la sugerencia MAXRECURSION y un valor de 0 a 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT. De esta manera, se puede controlar la ejecución de la instrucción hasta que se resuelva el problema de código que genera el bucle. El valor predeterminado para todo el servidor es 100. Si se especifica 0, no se aplica ningún límite. Sólo se puede especificar un valor de MAXRECURSION por instrucción. Para obtener más información, vea Query Hint (Transact-SQL).
• No se puede utilizar una vista que contenga una expresión de tabla común recursiva para actualizar datos.
• Se pueden definir cursores en las consultas que utilicen expresiones CTE. La expresión CTE es el argumento de select_statement que define el conjunto de resultados del cursor. En el caso de las CTE recursivas únicamente se permiten los cursores de sólo avance rápido y estáticos (de instantánea). Si se especifica otro tipo de cursor en una CTE recursiva, el tipo de cursor se convierte a estático.
• En la expresión CTE se puede hacer referencia a tablas de servidores remotos. Si se hace referencia al servidor remoto en el miembro recursivo de la CTE, se crea una cola para cada tabla remota de manera que se pueda tener acceso local a las tablas repetidas veces.

Ejemplos

A. Crear una expresión de tabla común simple

En el ejemplo siguiente se muestra el número de empleados que dependen directamente de cada encargado de Adventure Works Cycles.

USE AdventureWorks;
GO
WITH DirReps(ManagerID, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) 
    FROM HumanResources.Employee AS e
    WHERE ManagerID IS NOT NULL
    GROUP BY ManagerID
)
SELECT ManagerID, DirectReports 
FROM DirReps 
ORDER BY ManagerID;
GO

B. Usar una expresión de tabla común para limitar recuentos y promedios de informes

En el ejemplo siguiente se muestra el número promedio de empleados que dependen de los encargados.

WITH DirReps (Manager, DirectReports) AS 
(
    SELECT ManagerID, COUNT(*) AS DirectReports
    FROM HumanResources.Employee
    GROUP BY ManagerID
) 
SELECT AVG(DirectReports) AS [Average Number of Direct Reports]
FROM DirReps 
WHERE DirectReports>= 2 ;
GO

C. Hacer referencia a una expresión de tabla común más de una vez

En el ejemplo siguiente se muestra el número total de pedidos de venta y la fecha del pedido de venta más reciente de la tabla SalesOrderHeader para cada vendedor. En la instrucción que se ejecuta, se hace referencia a la expresión CTE dos veces: una vez para devolver las columnas seleccionadas para el vendedor y de nuevo para recuperar detalles similares de su superior. Los datos del vendedor y de su superior se devuelven en una sola fila.

USE AdventureWorks;
GO
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

D. Usar una expresión de tabla común recursiva para mostrar varios niveles de recursividad

En el ejemplo siguiente se muestra la lista jerárquica de encargados y de los empleados que tienen a su cargo.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GO

E. Usar una expresión de tabla común recursiva para mostrar dos niveles de recursividad

En el ejemplo siguiente se muestran los encargados y los empleados que tienen a su cargo. El número de niveles devueltos está limitado a dos.

USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports 
WHERE EmployeeLevel <= 2 ;
GO

F. Usar una expresión de tabla común recursiva para mostrar una lista jerárquica

El ejemplo siguiente, que está basado en el ejemplo C, agrega los nombres del encargado y de los empleados, y sus cargos respectivos. La jerarquía de encargados y empleados se enfatiza de forma adicional mediante la aplicación de sangrías a cada nivel.

USE AdventureWorks;
GO
WITH DirectReports(Name, Title, EmployeeID, EmployeeLevel, Sort)
AS (SELECT CONVERT(varchar(255), c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        1,
        CONVERT(varchar(255), c.FirstName + ' ' + c.LastName)
    FROM HumanResources.Employee AS e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID 
    WHERE e.ManagerID IS NULL
    UNION ALL
    SELECT CONVERT(varchar(255), REPLICATE ('| ' , EmployeeLevel) +
        c.FirstName + ' ' + c.LastName),
        e.Title,
        e.EmployeeID,
        EmployeeLevel + 1,
        CONVERT (varchar(255), RTRIM(Sort) + '| ' + FirstName + ' ' + 
                 LastName)
    FROM HumanResources.Employee as e
    JOIN Person.Contact AS c ON e.ContactID = c.ContactID
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
    )
SELECT EmployeeID, Name, Title, EmployeeLevel
FROM DirectReports 
ORDER BY Sort;
GO

G. Usar MAXRECURSION para cancelar una instrucción

MAXRECURSION se puede utilizar para impedir que una CTE recursiva con formato incorrecto entre en un bucle infinito. En el ejemplo siguiente se crea un bucle infinito intencionadamente y se utiliza la sugerencia MAXRECURSION para limitar el número de niveles de recursividad a dos.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Después de corregir el error de código, ya no se requiere MAXRECURSION. En el siguiente ejemplo se muestra el código corregido.

USE AdventureWorks;
GO
WITH cte (EmployeeID, ManagerID, Title)
AS
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT  e.EmployeeID, e.ManagerID, e.Title
    FROM HumanResources.Employee AS e
    JOIN cte ON e.ManagerID = cte.EmployeeID
)
SELECT EmployeeID, ManagerID, Title
FROM cte;
GO

H. Usar una expresión de tabla común para recorrer selectivamente y paso a paso una relación recursiva en una instrucción SELECT

En el ejemplo siguiente se muestra la jerarquía de ensamblados y componentes de producto necesarios para fabricar la bicicleta para ProductAssemblyID = 800.

USE AdventureWorks;
GO
WITH Parts(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
SELECT AssemblyID, ComponentID, Name, PerAssemblyQty, EndDate,
        ComponentLevel 
FROM Parts AS p
    INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;
GO

I. Usar una CTE recursiva en una instrucción UPDATE

En el ejemplo siguiente se actualiza el valor de VacationHours un 25 por ciento para todos los empleados que dependen directa o indirectamente de ManagerID 12. La expresión de tabla común devuelve una lista jerárquica de los empleados que dependen directamente de ManagerID 12 y los empleados que dependen de esos empleados, y así sucesivamente. Sólo se modifican las filas devueltas por la expresión de tabla común.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

J. Usar varios miembros delimitadores y recursivos

En el ejemplo siguiente se utilizan varios miembros delimitadores y recursivos para devolver todos los antecesores de una persona especificada. Se crea una tabla y se insertan valores en ella para establecer la genealogía familiar devuelta por la CTE recursiva.

-- Genealogy table
IF OBJECT_ID('Person','U') IS NOT NULL DROP TABLE Person;
GO
CREATE TABLE Person(ID int, Name varchar(30), Mother int, Father int);
GO
INSERT Person VALUES(1, 'Sue', NULL, NULL);
INSERT Person VALUES(2, 'Ed', NULL, NULL);
INSERT Person VALUES(3, 'Emma', 1, 2);
INSERT Person VALUES(4, 'Jack', 1, 2);
INSERT Person VALUES(5, 'Jane', NULL, NULL);
INSERT Person VALUES(6, 'Bonnie', 5, 4);
INSERT Person VALUES(7, 'Bill', 5, 4);
GO
-- Create the recursive CTE to find all of Bonnie's ancestors.
WITH Generation (ID) AS
(
-- First anchor member returns Bonnie's mother.
    SELECT Mother 
    FROM Person
    WHERE Name = 'Bonnie'
UNION
-- Second anchor member returns Bonnie's father.
    SELECT Father 
    FROM Person
    WHERE Name = 'Bonnie'
UNION ALL
-- First recursive member returns male ancestors of the previous generation.
    SELECT Person.Father
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
UNION ALL
-- Second recursive member returns female ancestors of the previous generation.
    SELECT Person.Mother
    FROM Generation, Person
    WHERE Generation.ID=Person.ID
)
SELECT Person.ID, Person.Name, Person.Mother, Person.Father
FROM Generation, Person
WHERE Generation.ID = Person.ID;
GO

Vea también

Referencia

CREATE VIEW (Transact-SQL)
DELETE (Transact-SQL)
EXCEPT e INTERSECT (Transact-SQL)
INSERT (Transact-SQL)
SELECT (Transact-SQL)
UPDATE (Transact-SQL)

Otros recursos

Consultas recursivas mediante expresiones de tabla comunes
Usar expresiones de tabla comunes

Ayuda e información

Obtener ayuda sobre SQL Server 2005