Cross Apply y Outer Apply SQL Server

Uso de Cross Apply y Outer Apply

La cláusula CROSS APPLY de la instrucción select se comporta de manera similar a una subconsulta correlacionada, con la diferencia que nos permite usar la cláusula ORDER BY dentro de la subconsulta.
Esto es muy útil cuando se requiere registros superiores o inferiores de una subconsulta para usarlo en una subconsulta externa.
La subconsulta de la cláusula CROSS APPLY se puede reemplazar por una función definida por el usuario que reporta una tabla.

Para más información se recomienda leer los astículos siguientes:
Subconsultas
Funciones definidas por el usuario
Consultas desde varias tablas con Joins
Subconsultas correlacionadas
Uso de With Ties en Consultas
Funciones de fecha y hora

Usando la base de datos Northwind
use Northwind
go

Ejercicios

Ejercicio 1

Listado de las compras de un cliente. Las tres compras con mayor cantidad.
Primero se va a listar las compras de un cliente, esta consulta servirá como ejemplo para el uso de Cross Apply.

Select TOP 3 WITH TIES
c.CustomerID As ‘Cód. Cliente’, C.CompanyName As ‘Cliente’,
O.OrderID As ‘Nº Orden’, D.Quantity As ‘Cantidad’
From Orders As O
join Customers AS c ON O.CustomerID = c.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
WHERE c.CompanyName = ‘Alfreds Futterkiste’
ORDER BY D.Quantity DESC
go
El resultado se muestra en la siguiente figura.

Note que se muestran 4 registros ya que los dos últimos tienen el mismo valor y se ha usado With Ties

Ahora las tres órdenes por cada Cliente, la consulta previa se usará como la subconsulta para aplicar la clásula Cross Apply

Select c1.CustomerID As ‘Cód. Cliente’ , C1.CompanyName As ‘Cliente’,
Superiores.[Nº Orden], Superiores.Cantidad
From Customers AS c1
CROSS APPLY
(Select TOP 3 WITH TIES
c.CustomerID , C.CompanyName ,
O.OrderID As ‘Nº Orden’, D.Quantity As ‘Cantidad’
From Orders As O
join Customers AS c ON O.CustomerID = c.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
WHERE c.CompanyName = c1.CompanyName
ORDER BY D.Quantity DESC) As Superiores
WHERE c1.CustomerID is not null
ORDER BY c1.CustomerId, Superiores.Cantidad DESC;
go
El resultado se muestra en la siguiente figura.

Creando una FDU que retorna una tabla para la subconsulta
Create or alter function fduRetornaTop3VentasClientes (@NombreCliente nvarchar(40))
Returns Table
As
Return
(Select TOP 3 WITH TIES
c.CustomerID , C.CompanyName ,
O.OrderID As ‘Nº Orden’, D.Quantity As ‘Cantidad’
From Orders As O
join Customers AS c ON O.CustomerID = c.CustomerID
join [Order Details] As D on O.OrderID = D.OrderID
WHERE c.CompanyName = @NombreCliente
ORDER BY D.Quantity DESC)
go

Usando la FDU
Select c1.CustomerID As ‘Cód. Cliente’ , C1.CompanyName As ‘Cliente’,
Superiores.[Nº Orden], Superiores.Cantidad
From Customers AS c1
CROSS APPLY
(Select * from dbo.fduRetornaTop3VentasClientes(C1.CompanyName)) As Superiores
WHERE c1.CustomerID is not null
ORDER BY c1.CustomerId, Superiores.Cantidad DESC;
go
El resultado se muestra en la siguiente figura.

Listamos todas las órdenes del cliente BLAUS, podemos ver 14 órdenes, en las que se pidió más cantidad son las mismas que se muestran en la instrucción usando Cross Apply. Se muestran 5 órdenes porque se ha incluido la opción with ties.

select C.CompanyName, D.Quantity
from [Order Details] As D
join Orders As O on D.OrderID = O.OrderID
join Customers As C on O.CustomerID = C.CustomerID
where C.CustomerID = ‘BLAUS’
order by D.Quantity desc
go
El resultado se muestra en la siguiente figura.

Ejercicio 2

Listar las órdenes y los días entre las órdenes del mismo cliente.
Note que al usar Cross Apply, cuando ya no existe un siguiente registro, es decir, cuando no hay una orden siguiente, no muestra otro registro, lo que si ocurre usando Outer Apply, resultado que se muestra en el ejercicio 3.

SELECT
O.OrderID As ‘Nº Orden’,
Format(O.OrderDate,’dd/MM/yyy’) As ‘Fecha’,
SO.OrderID As ‘Nº Orden Siguiente’,
Format(SO.OrderDate,’dd/MM/yyy’) As ‘Fecha sig. Orden’,
O.CustomerID As ‘Id. Cliente’,
C.CompanyName As ‘Cliente’,
DATEDIFF(DAY, O.OrderDate,SO.OrderDate) As ‘Días entre órdenes’
FROM Orders AS O
join Customers As C on O.CustomerID = C.CustomerID
CROSS APPLY
(SELECT TOP 1 OL.OrderDate, OL.OrderID
FROM Orders AS OL
WHERE OL.CustomerID = O.CustomerID
AND OL.OrderID > O.OrderID
ORDER BY OL.OrderID) As SO
ORDER BY O.CustomerID, O.OrderID
go
El resultado se muestra en la siguiente figura.

Note que la orden 11011 del cliente Alfreds Futterkiste es la última, no tiene una siguiente orden.

El uso de CROSS APPLY permite unir los registros de Ordenes (Orders As O) con la subconsulta de tabla derivada llamada SO (Siguiente Orden). Note que se usa el ordenamiento en la subconsulta (ORDER BY OL.OrderID) para poder identificar la primera orden (Top 1).

Usando OUTER APPLY

La cláusula OUTER APPLY produce un resultado similar al OUTER JOIN.

Ejercicio 3

El siguiente ejemplo muestra las órdenes de los clientes hasta llegar a la última registrada donde se muestran valores Null porque no registra una siguiente orden.

SELECT
O.OrderID As ‘Nº Orden’,
Format(O.OrderDate,’dd/MM/yyy’) As ‘Fecha’,
SO.OrderID As ‘Nº Orden Siguiente’,
Format(SO.OrderDate,’dd/MM/yyy’) As ‘Fecha sig. Orden’,
O.CustomerID As ‘Id. Cliente’,
C.CompanyName As ‘Cliente’,
DATEDIFF(DAY, O.OrderDate,SO.OrderDate) As ‘Días entre órdenes’
FROM Orders AS O
join Customers As C on O.CustomerID = C.CustomerID
Outer APPLY
(SELECT TOP 1 OL.OrderDate, OL.OrderID
FROM Orders AS OL
WHERE OL.CustomerID = O.CustomerID
AND OL.OrderID > O.OrderID
ORDER BY OL.OrderID) As SO
ORDER BY O.CustomerID, O.OrderID
go
El resultado se muestra en la siguiente figura.

Usando CROSS APPLY y FDU que retorna una tabla

Create or alter function dbo.fduObtenerDatosCliente(@Ciudad nvarchar(15))
Returns @DatosClientesPorCiudad Table
(
ClienteCodigo nchar(5),
ClienteNombre nvarchar(40),
ClienteContacto nvarchar(30),
ClienteDireccion nvarchar(60)
)
As
Begin
Insert into @DatosClientesPorCiudad
select C.CustomerId, C.CompanyName,
C.ContactName, C.Address
from Customers As C
where C.City = @Ciudad
Return
End
go

Para visualizar los clientes de la ciudad de London
Select * from dbo.fduObtenerDatosCliente(‘London’)
go
El resultado se muestra en la siguiente figura.

Para mostrar las órdenes, incluyendo fechas de las órdenes de cada cliente.
select O.OrderID As ‘Nº Orden’,
Format(O.OrderDate,’dd/MM/yyyy’) As ‘Fecha’ ,
C1.*
from Orders As O
join Customers As C on O.CustomerID = C.CustomerID
Cross Apply dbo.fduObtenerDatosCliente(C.City) As C1
go
El resultado se muestra en la siguiente figura.

Importante
– En muchos casos se puede usar join y cross apply retornando el mismo resultado, seleccione la que consume menos recursos, usando para esto el plan de ejecución estimado.
– Cuando se usa Join con muchas condiciones se recomienda el comparar el resultado con el uso de Cross Apply.
– Use Outer Apply cuando la función definida por el usuario retorna una tabla.