{"id":2229,"date":"2020-10-25T15:59:34","date_gmt":"2020-10-25T15:59:34","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2229"},"modified":"2021-11-02T13:53:40","modified_gmt":"2021-11-02T13:53:40","slug":"cross-apply-y-outer-apply-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2229","title":{"rendered":"Cross Apply y Outer Apply SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply__D.png\" alt=\"\" class=\"wp-image-2230\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Uso de Cross Apply y Outer Apply<\/h2>\n\n\n\n<p>La cl\u00e1usula CROSS APPLY de la instrucci\u00f3n select se comporta de manera similar a una subconsulta correlacionada, con la diferencia que nos permite usar la cl\u00e1usula ORDER BY dentro de la subconsulta. <br>Esto es muy \u00fatil cuando se requiere registros superiores o inferiores de una subconsulta para usarlo en una subconsulta externa.<br>La subconsulta de la cl\u00e1usula CROSS APPLY se puede reemplazar por una funci\u00f3n definida por el usuario que reporta una tabla.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Para m\u00e1s informaci\u00f3n se recomienda leer los ast\u00edculos siguientes:<br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=219\" target=\"_blank\">Subconsultas<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=335\" target=\"_blank\">Funciones definidas por el usuario<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\">Consultas desde varias tablas con Joins<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=1757\" target=\"_blank\" rel=\"noreferrer noopener\">Subconsultas correlacionadas<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=580\" target=\"_blank\" rel=\"noreferrer noopener\">Uso de With Ties en Consultas<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=185\" target=\"_blank\" rel=\"noreferrer noopener\">Funciones de fecha y hora<\/a><\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p>Usando la base de datos Northwind<br>use Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p>Listado de las compras de un cliente. Las tres compras con mayor cantidad.<br>Primero se va a listar las compras de un cliente, esta consulta servir\u00e1 como ejemplo para el uso de Cross Apply.<\/p>\n\n\n\n<p>Select TOP 3 WITH TIES<br>c.CustomerID As &#8216;C\u00f3d. Cliente&#8217;, C.CompanyName As &#8216;Cliente&#8217;,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, D.Quantity As &#8216;Cantidad&#8217;<br>From Orders As O<br>join Customers AS c ON O.CustomerID = c.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>WHERE c.CompanyName = &#8216;Alfreds Futterkiste&#8217;<br>ORDER BY D.Quantity DESC<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_01.png\" alt=\"\" class=\"wp-image-2231\" width=\"674\" height=\"239\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_01.png 490w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_01-300x107.png 300w\" sizes=\"auto, (max-width: 674px) 100vw, 674px\" \/><figcaption>Note que se muestran 4 registros ya que los dos \u00faltimos tienen el mismo valor y se ha usado With Ties<\/figcaption><\/figure>\n\n\n\n<p>Ahora las tres \u00f3rdenes por cada Cliente, la consulta previa se usar\u00e1 como la subconsulta para aplicar la cl\u00e1sula Cross Apply<\/p>\n\n\n\n<p>Select c1.CustomerID As &#8216;C\u00f3d. Cliente&#8217; , C1.CompanyName As &#8216;Cliente&#8217;,<br>Superiores.[N\u00ba Orden], Superiores.Cantidad<br>From Customers AS c1<br>CROSS APPLY<br><strong>(Select TOP 3 WITH TIES<br>c.CustomerID , C.CompanyName ,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, D.Quantity As &#8216;Cantidad&#8217;<br>From Orders As O<br>join Customers AS c ON O.CustomerID = c.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>WHERE c.CompanyName = c1.CompanyName<br>ORDER BY D.Quantity DESC)<\/strong> As Superiores<br>WHERE c1.CustomerID is not null<br>ORDER BY c1.CustomerId, Superiores.Cantidad DESC;<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_02.png\" alt=\"\" class=\"wp-image-2232\" width=\"640\" height=\"778\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_02.png 604w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_02-247x300.png 247w\" sizes=\"auto, (max-width: 640px) 100vw, 640px\" \/><\/figure>\n\n\n\n<p><strong>Creando una FDU que retorna una tabla para la subconsulta<\/strong><br>Create or alter function fduRetornaTop3VentasClientes (@NombreCliente nvarchar(40))<br>Returns Table<br>As<br>Return<br>(Select TOP 3 WITH TIES<br>c.CustomerID , C.CompanyName ,<br>O.OrderID As &#8216;N\u00ba Orden&#8217;, D.Quantity As &#8216;Cantidad&#8217;<br>From Orders As O<br>join Customers AS c ON O.CustomerID = c.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>WHERE c.CompanyName = @NombreCliente<br>ORDER BY D.Quantity DESC)<br>go<\/p>\n\n\n\n<p><strong>Usando la FDU<br><\/strong>Select c1.CustomerID As &#8216;C\u00f3d. Cliente&#8217; , C1.CompanyName As &#8216;Cliente&#8217;,<br>Superiores.[N\u00ba Orden], Superiores.Cantidad<br>From Customers AS c1<br>CROSS APPLY<br>(Select * from dbo.<strong>fduRetornaTop3VentasClientes<\/strong>(C1.CompanyName)) As Superiores<br>WHERE c1.CustomerID is not null<br>ORDER BY c1.CustomerId, Superiores.Cantidad DESC;<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_02.png\" alt=\"\" class=\"wp-image-2232\" width=\"658\" height=\"799\"\/><\/figure>\n\n\n\n<p>Listamos todas las \u00f3rdenes del cliente BLAUS, podemos ver 14 \u00f3rdenes, en  las que se pidi\u00f3 m\u00e1s cantidad son las mismas que se muestran en la  instrucci\u00f3n usando Cross Apply. Se muestran 5 \u00f3rdenes porque se ha incluido la opci\u00f3n with ties.<\/p>\n\n\n\n<p>select C.CompanyName, D.Quantity<br>from [Order Details] As D<br>join Orders As O on D.OrderID = O.OrderID<br>join Customers As C on O.CustomerID = C.CustomerID<br>where C.CustomerID = &#8216;BLAUS&#8217;<br>order by D.Quantity desc<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_03.png\" alt=\"\" class=\"wp-image-2233\" width=\"680\" height=\"522\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_03.png 532w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_03-300x230.png 300w\" sizes=\"auto, (max-width: 680px) 100vw, 680px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p>Listar las \u00f3rdenes y los d\u00edas entre las \u00f3rdenes del mismo cliente.<br>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.<\/p>\n\n\n\n<p>SELECT<br>O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MM\/yyy&#8217;) As &#8216;Fecha&#8217;,<br>SO.OrderID As &#8216;N\u00ba Orden Siguiente&#8217;,<br>Format(SO.OrderDate,&#8217;dd\/MM\/yyy&#8217;) As &#8216;Fecha sig. Orden&#8217;,<br>O.CustomerID As &#8216;Id. Cliente&#8217;,<br>C.CompanyName As &#8216;Cliente&#8217;,<br>DATEDIFF(DAY, O.OrderDate,SO.OrderDate) As &#8216;D\u00edas entre \u00f3rdenes&#8217;<br>FROM Orders AS O<br>join Customers As C on O.CustomerID = C.CustomerID<br>CROSS APPLY<br><strong>(SELECT TOP 1 OL.OrderDate, OL.OrderID<br>FROM Orders AS OL<br>WHERE OL.CustomerID = O.CustomerID<br>AND OL.OrderID > O.OrderID<br>ORDER BY OL.OrderID)<\/strong> As SO<br>ORDER BY O.CustomerID, O.OrderID<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04-1024x624.png\" alt=\"\" class=\"wp-image-2234\" width=\"700\" height=\"426\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04-1024x624.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04-300x183.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04-768x468.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04-795x484.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_04.png 1051w\" sizes=\"auto, (max-width: 700px) 100vw, 700px\" \/><\/figure>\n\n\n\n<p>Note que la orden 11011 del cliente Alfreds Futterkiste es la \u00faltima, no tiene una siguiente orden.<\/p>\n\n\n\n<p>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).<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Usando OUTER APPLY<\/strong><\/p>\n\n\n\n<p>La cl\u00e1usula OUTER APPLY produce un resultado similar al OUTER JOIN.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p>El siguiente ejemplo muestra las \u00f3rdenes de los clientes hasta llegar a la \u00faltima registrada donde se muestran valores Null porque no registra una siguiente orden.<\/p>\n\n\n\n<p>SELECT<br>O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MM\/yyy&#8217;) As &#8216;Fecha&#8217;,<br>SO.OrderID As &#8216;N\u00ba Orden Siguiente&#8217;,<br>Format(SO.OrderDate,&#8217;dd\/MM\/yyy&#8217;) As &#8216;Fecha sig. Orden&#8217;,<br>O.CustomerID As &#8216;Id. Cliente&#8217;,<br>C.CompanyName As &#8216;Cliente&#8217;,<br>DATEDIFF(DAY, O.OrderDate,SO.OrderDate) As &#8216;D\u00edas entre \u00f3rdenes&#8217;<br>FROM Orders AS O<br>join Customers As C on O.CustomerID = C.CustomerID<br>Outer APPLY<br><strong>(SELECT TOP 1 OL.OrderDate, OL.OrderID<br>FROM Orders AS OL<br>WHERE OL.CustomerID = O.CustomerID<br>AND OL.OrderID > O.OrderID<br>ORDER BY OL.OrderID)<\/strong> As SO<br>ORDER BY O.CustomerID, O.OrderID<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05-1024x601.png\" alt=\"\" class=\"wp-image-2235\" width=\"744\" height=\"436\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05-1024x601.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05-300x176.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05-768x451.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05-795x467.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_05.png 1225w\" sizes=\"auto, (max-width: 744px) 100vw, 744px\" \/><\/figure>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usando CROSS APPLY y FDU que retorna una tabla<\/strong><\/p>\n\n\n\n<p>Create or alter function dbo.fduObtenerDatosCliente(@Ciudad nvarchar(15))<br>Returns @DatosClientesPorCiudad Table<br>(<br>ClienteCodigo nchar(5),<br>ClienteNombre nvarchar(40),<br>ClienteContacto nvarchar(30),<br>ClienteDireccion nvarchar(60)<br>)<br>As<br>Begin<br>Insert into @DatosClientesPorCiudad<br>select C.CustomerId, C.CompanyName,<br>C.ContactName, C.Address<br>from Customers As C<br>where C.City = @Ciudad<br>Return<br>End<br>go<\/p>\n\n\n\n<p><strong>Para visualizar los clientes de la ciudad de London<br><\/strong>Select * from dbo.fduObtenerDatosCliente(&#8216;London&#8217;)<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_06.png\" alt=\"\" class=\"wp-image-2236\" width=\"733\" height=\"209\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_06.png 748w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_06-300x86.png 300w\" sizes=\"auto, (max-width: 733px) 100vw, 733px\" \/><\/figure>\n\n\n\n<p>Para mostrar las \u00f3rdenes, incluyendo fechas de las \u00f3rdenes de cada cliente.<br>select O.OrderID As &#8216;N\u00ba Orden&#8217;,<br>Format(O.OrderDate,&#8217;dd\/MM\/yyyy&#8217;) As &#8216;Fecha&#8217; ,<br>C1.*<br>from Orders As O<br>join Customers As C on O.CustomerID = C.CustomerID<br>Cross Apply <strong>dbo.fduObtenerDatosCliente(C.City) <\/strong>As C1<br>go<br><strong>El resultado se muestra en la siguiente figura.<\/strong><\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_07.png\" alt=\"\" class=\"wp-image-2237\" width=\"751\" height=\"639\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_07.png 1001w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_07-300x255.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_07-768x654.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/10\/ManualSQL_CrossOuterApply_07-795x677.png 795w\" sizes=\"auto, (max-width: 751px) 100vw, 751px\" \/><\/figure>\n\n\n\n<p class=\"has-pale-cyan-blue-background-color has-background has-medium-font-size\"><strong>Importante<br> &#8211; 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\u00f3n estimado.<br> &#8211; Cuando se usa Join con muchas condiciones se recomienda el comparar el resultado con el uso de Cross Apply.<br> &#8211; Use Outer Apply cuando la funci\u00f3n definida por el usuario retorna una tabla.<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Uso de Cross Apply y Outer Apply La cl\u00e1usula CROSS APPLY de la instrucci\u00f3n select se comporta de manera similar a una subconsulta correlacionada, con la diferencia que nos permite usar la cl\u00e1usula ORDER BY dentro de la subconsulta. Esto es muy \u00fatil cuando se requiere registros superiores o inferiores de una subconsulta para usarlo &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2229\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2230,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,236,231],"tags":[257,258,55],"class_list":["post-2229","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-consultasdedatos","category-udffunciones","category-subconsultas","tag-cross-apply","tag-outer-apply","tag-subconsultas","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2229","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2229"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2229\/revisions"}],"predecessor-version":[{"id":2600,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2229\/revisions\/2600"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2230"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2229"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2229"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2229"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}