Sql With ¿Que es?

La funcionalidad Expresiones de Tablas Comunes  (CTE´s) es una forma de definir un conjunto de datos temporales, su ciclo de vida es solamente mientras se ejecute la consulta.

Aunque no se almacena en ningún sitio permite trabajarla y consultarla como si fuera una tabla real.

Usos

• Crear una consulta recursiva.
• Filtrar por campos que no existan.
• Evitar usar vistas, cuando no son necesarias.
• Hacer referencia a la misma tabla varias veces en la misma instrucción.
• Agrupar o filtrar por campos de subconsultas.

Sintaxis

WITH
NombreCTE (Campos Devueltos)  AS
(
Subconsulta
),
NombreCTE2 AS
(
SELECT
FROM Cte1
WHERE …
)
SELECT * FROM NombreCTE2

Ejemplo

Necesitamos consultar los empleados vendedores de una Proyecto.

select c.NombreProyecto,h.empleadoId from proyecto c
INNER JOIN empleado h
ON c.vendedorID = h.[Id]

En este caso nos regresa la venta y el numero del empleado que es vendedor.

pero ademas necesito el nombre del empleado que se encuentra en otra tabla entonces podemos realizar lo siguiente:

WITH Empleado AS
(
SELECT c.Alias AS Venta,h.[Identity]AS ID,h.EmployeeId AS vendedor
FROM crm.quotations c
INNER JOIN [HR].[Employees] h
ON c.PMIdentity = h.[Identity]
),
Parte AS (
SELECT e.Venta AS Venta, e.vendedor AS vendedor, p.[Identity] AS IDP
FROM Empleado e
INNER JOIN SYST.Partners p on e.ID = p.[Identity]
)
SELECT pa.Venta,pa.vendedor,pa.IDP, u.Name
FROM Parte pa
INNER JOIN ADM.Users u on up.UserID = u.[ID]

La consulta dentro de el primer WITH nos devuelve los datos de la venta y el empleado, basandonos en la información que nos regresa en la siguiente subcoculta llamada Parte la utilizamos como si fuera una tabla:

Empleado.Venta es una expresion posible dentro de nuetra segunda subconsulta, donde obtenemos un dato mas que es el ID del usuario para obtener el nombre.

En el ultimo SELECT utilizamos la segunda «tabla temporal» donde tenemos la informacion cruzada de las dos subconsultas anteriores y la relacionamos con una tercera tabla para obtener el nombre del empleado.

Esta funcionalidad me ha sido de utilidad cuando necesito obtener un dato el cual es resultado de varias relaciones de tablas, ya que colocar tantos INNER JOIN para generar una consulta no es tan psositvo.

Cuando genero reportes que se pueden consultar online, usar este tipo de funcionalidad en lugar de muchos INNER permite que la consulta se ha mas rapida, asicional si alguien requiere modificar la consulta es mas sencilla de entender.