Expresiones de tabla comunes (CTE) en SQL Server

Una expresión de tabla común o Common Table Expression (CTE) es un conjunto de resultados que se definen en tiempo de ejecución, tiene un concepto similar a una Tabla Derivada:

• Son creadas en tiempo de ejecución.
• Es necesario asignarle un nombre.
• Tienen una consulta interna.
• Hay una consulta externa hace uso de la consulta interna.

A pesar de esto, la sintaxis para declarar un CTE es diferente a una tabla derivada, y es la siguiente:

WITH <nombreCTE>
AS
(
<consulta_interna>
)
<consulta_externa>

Para ver esto en acción realizamos el mismo ejemplo que en la entrada anterior de Tablas derivaras aplicando estos nuevos conocimientos.

WITH VentasPosicion AS ( -- Declaracion del primer CTE
 SELECT Title AS Posicion, COUNT(OD.OrderID) CantidadPorPosicion
 FROM Employees AS E
 INNER JOIN Orders AS O
 ON E.EmployeeID = O.EmployeeID
 INNER JOIN [Order Details] AS OD
 ON O.OrderID = OD.OrderID
 GROUP BY Title
),
VentasEmpleados AS( --Declaracion del segundo CTE
 SELECT FirstName + ' '+LastName AS FullName,Title AS Posicion,COUNT(OD.OrderID) AS CantidadPorEmpleado
 FROM Employees AS E
 INNER JOIN Orders AS O
 ON E.EmployeeID = O.EmployeeID
 INNER JOIN [Order Details] AS OD
 ON O.OrderID = OD.OrderID
 GROUP BY FirstName, LastName, Title
)

--Consulta a los CTEs declarados
SELECT FullName, VentasPosicion.Posicion, VentasEmpleados.CantidadPorEmpleado, VentasPosicion.CantidadPorPosicion,
CAST(100.00 * VentasEmpleados.CantidadPorEmpleado / VentasPosicion.CantidadPorPosicion AS decimal(18,2)) AS PorcientoVentaEmpleado
FROM VentasPosicion
INNER JOIN VentasEmpleados
 ON VentasPosicion.Posicion = VentasEmpleados.Posicion
ORDER BY VentasPosicion.Posicion

Resultado consulta con CTEs

Como vimos en el ejemplo anterior,  primero se definen los CTEs (consulta interna) separados por coma en caso de que sean más de uno, luego es definida la consulta externa donde se puede hacer uso los CTEs. Esto hace que la consulta en general sea más clara al momento de su uso, especialmente si  queremos hacer referencia a desde un CTE a otro, veamos un ejemplo de la sintaxis:

WITH Cte1 AS
(
    SELECT ...
    FROM Tabla1
    WHERE ...
),
Cte2 AS
(
    SELECT...
    FROM Cte1
    WHERE ...
)
SELECT ...
FROM Cte2
WHERE ...

Cada uno puede hacer referencia al CTE anterior y la consulta externa puede hacer referencia a todos ellos.

Otra diferencia con las tablas derivadas es que los CTEs si pueden hacer referencia a ellos mismo, por ejemplo:

WITH Cte1 AS
(
    SELECT ...
    FROM Tabla1
)
SELECT ...
FROM Cte1 AS C1
INNER JOIN Cte1 AS C2
ON ...

Esto es debido a que el nombre del CTE es definido antes de la consulta externa.

Espero esto les sirva.

Happy Querying!