CTE: Common Table Expressions – SQL Server 2008

Este es el primero de una serie de artículos que estaré escribiendo acerca de características de SQL Server 2008 para desarrolladores.

Un CTE es un artefacto que nos mantiene en memoria el resultado de una consulta, para que lo podamos llamar luego dentro de esa misma consulta. Es como una “variable” temporal que nos evita tener que declarar una vista no persistente, o una tabla temporal.

La sintaxis es muy sencilla:

WITH <nombre de su CTE> <nombre de columnas>
AS
(
<query de origen>
)
SELECT * FROM <nombre de su CTE>

Por ejemplo:

Para qué es útil?

Imagínense por ejemplo que tienen una tabla de ventas donde están todas las ventas hechas y el ID de los vendedores que las hicieron.

Piensen que necesitan un reporte donde aparezca el total de ventas y el nombre del vendedor.

Los datos extendidos del vendedor están en otra tabla. Además como el reporte totaliza la cantidad de ventas se requiere un query con una función de agregado ( count(*) ) Dado que existe este agregado no es posible usar un simple join, sino que tendríamos que usar una subconsulta o una tabla temporal:

 select Vendedores.FirstName, VentasAgrupadas.VentasTotales from
(
   SELECT Sales.SalesOrderHeader.SalesPersonID, COUNT(*) as VentasTotales
   FROM Sales.SalesOrderHeader
   WHERE Sales.SalesOrderHeader.SalesPersonID IS NOT NULL
   GROUP BY Sales.SalesOrderHeader.SalesPersonID
) as VentasAgrupadas
inner join Sales.vSalesPerson as Vendedores on Vendedores.SalesPersonID=VentasAgrupadas.SalesPersonID
order by VentasAgrupadas.VentasTotales

Como se aprecia esto es engorroso y poco claro.

Con un CTE es mucho más sencillo, ya que no existe la necesidad de la subconsulta sino que parece como si declaráramos una variable de tipo tabla o vista con los resultados que queremos:

 WITH VentasAgrupadas(IdVendedor, VentasTotales)
as
(
  SELECT Sales.SalesOrderHeader.SalesPersonID, COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE Sales.SalesOrderHeader.SalesPersonID IS NOT NULL
  GROUP BY Sales.SalesOrderHeader.SalesPersonID
)

 
SELECT Vendedores.FirstName, VentasAgrupadas.VentasTotales
FROM Sales.vSalesPerson as Vendedores
INNER JOIN VentasAgrupadas ON Vendedores.SalesPersonID=VentasAgrupadas.IdVendedor
ORDER BY VentasAgrupadas.VentasTotales

Un caso aún más dramático puede observarse cuando queremos detectar los duplicados de una tabla.

Por ejemplo si tengo una tabla de productos con Ids distintos pero mismo nombre, la consulta luce así:

 SELECT * FROM Products WHERE ProductID NOT IN
(SELECT MIN(ProductID) FROM Products as P
WHERE Products.ProductName=P.ProductName)

Exótico no?

Es lo que conocemos como Self-Join en SQL.

Eso me retorna todos los registros duplicados. Observen que se logra luego de extraer el mínimo id para los duplicados, selecciono todos los duplicados cuyo id no sea ese mínimo, lo que me da como resultado los registros redundantes:

Obviamente con un CTE la complejidad del query se reduce completamente:

 WITH MinProductRecords AS
(
  SELECT MIN(ProductID) AS ProductID, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
)

SELECT Products
FROM Products AS P
INNER JOIN MinProductRecords AS MP
ON P.ProductName = MP.ProductName AND P.ProductID > MP.ProductID

Aquí aunque hay más código, es más claro y administrable, pues vemos cómo primero seleccionamos los mínimos Ids y luego hacemos un join donde los Ids sean mayores a estos mínimos. Así se obtienen los mismos resultados.

Entonces si quisiéramos además eliminar de inmediato los duplicados bastaría con reemplazar el último SELECT por un DELETE