Common Table Expressions CTE SQL Server

Common Table Expressions – CTE

Una expresión de tabla común (CTE) es un conjunto de resultados temporal definido en la ejecución de una instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Es como asignar un nombre a una consulta pero sin almacenarla en la base de datos como el caso de las vistas. (Ver Vistas)

Una CTE es similar a una tabla derivada (Ver Subconsultas como tablas derivadas) en que no se almacena como un objeto y dura sólo el tiempo que dura la consulta. A diferencia de una tabla derivada, una CTE puede hacer referencia a sí misma y se puede hacer referencia a ella varias veces en la misma consulta.

Una CTE se puede usar para:

Crear una consulta recursiva.
Sustituir la creación de una vista cuando el uso de una vista no sea necesario; es decir, cuando no se tenga que almacenar la definición de la vista en la base de datos.
Hacer referencia a la tabla resultante varias veces en la misma instrucción.
Las CTE tiene ventajas de legibilidad mejorada y facilidad de mantenimiento de consultas complejas.
Las CTE se pueden definir en rutinas definidas por el usuario, como funciones, procedimientos almacenados, desencadenadores o vistas.

Estructura de una CTE

Las CTE tienen un nombre de expresión que representa la CTE, una lista de columnas opcional y una consulta que define la CTE.
Después de definir una CTE, se puede hacer referencia a ella como una tabla o vista en una instrucción SELECT, INSERT, UPDATE o DELETE.

La estructura de las CTE es:

WITH NombreCTE [ ( NombreColumna [,…n] ) ]
AS
( Consulta compleja )
Select <column_list> FROM NombreCTE

Ejercicios

Usando Northwind
use Northwind
go

Ejercicio 01

Mostrar los productos, su categoría y su proveedor. Mostrar los que no están descontinuados ordenados por precio descendentemente. Para entender las relaciones entre las tablas Ver Joins en SQL

La consulta sin CTE es como sigue

select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,
C.CategoryName, S.CompanyName
from Products As P
join Categories As C on P.CategoryID = C.CategoryID
join Suppliers As S on P.SupplierID = S.SupplierID
where P.Discontinued = 0
order by P.UnitPrice desc
go

Usando CTE

with ListaProductos (Codigo, Descripción, Precio, Stock, Categoría, Proveedor) As
(select P.ProductID, P.ProductName, P.UnitPrice, P.UnitsInStock,
C.CategoryName, S.CompanyName
from Products As P
join Categories As C on P.CategoryID = C.CategoryID
join Suppliers As S on P.SupplierID = S.SupplierID
where P.Discontinued = 0 )
Select * from ListaProductos order by Precio
go

La imagen muestra el resultado

— En la orden anterior se pueden presentar solamente algunos campos

La imagen muestra únicamente el Codigo, la Descripción y la Categoría.

Ejercicio 02

Mostrar los empleados y las órdenes generadas.

Es conveniente que la consulta tenga sus alias correctos. Ver Alias

La consulta sin CTE

select E.EmployeeID As ‘Codigo’, E.LastName As ‘Apellido’ , E.FirstName As ‘Nombre’,
O.OrderID As ‘Nº Orden’, Format(O.OrderDate,’dd/MMM/yyyy’) As ‘Fecha’
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID
go

Asignando la CTE

with OrdenesEmpleados As
(select E.EmployeeID As ‘Codigo’, E.LastName As ‘Apellido’ ,
E.FirstName As ‘Nombre’, O.OrderID As ‘Nº Orden’,
Format(O.OrderDate,’dd/MMM/yyyy’) As ‘Fecha’
from Employees As E
join Orders As O on E.EmployeeID = O.EmployeeID)
select * from OrdenesEmpleados
go

— Usando la CTE con una consulta diferente, incluye el total de órdenes. Ver agrupamientos

select Codigo, Apellido, Nombre, COUNT([Nº Orden]) As ‘Total Órdenes’
from OrdenesEmpleados group by Codigo, Apellido, Nombre
go

Ejercicio 03

Mostrando los datos de una relación recursiva. En la tabla Empleados (Employees) existe en campo ReportsTo que indica que un empleado debe reportar su trabajo al que se indica en ese campo.

— Visualizar los datos
select E.EmployeeID, E.FirstName, E.LastName, E.ReportsTo from Employees As E
go

En la imagen se puede mostrar que el empleado Nancy Davolio debe reportar al empleado
con código 2 que es Andrew Fuller.

Para un listado de los empleados y sus jefes podemos usar la siguiente instrucción.

with Jefe As
(select EmployeeID, LastName, FirstName from Employees)
Select J.EmployeeID As ‘Cód. Jefe’, [Empleado Jefe] = J.LastName + Space(1) + J.FirstName,
E.EmployeeID As ‘Cód. Empleado’, Subordinado = E.LastName + Space(1) + E.FirstName
from Jefe As J
join Employees As E on J.EmployeeID = E.ReportsTo
go