Traducido de:
http://weblogs.sqlteam.com/jeffs/archive/2007/10/18/sql-server-cross-apply.aspx
Pienso que la manera mas fácil de pensar la sentencia CROSS APPLY es que es semejante a hacer un CROSS JOIN con un sub-select correlativo en vez de una tabla derivada.
Digamos, una tabla derivada es "auto-contenida" de modo que las tablas y columnas que referencia no son accesibles por el select principal, aunque variables y parámetros pueden ser referenciados.
Por ejemplo, veamos:
select A.*, b.X
from A
cross join (select B.X from B where B.Val=A.Val) b
Esta sentencia no es válida, porque A.Val está fuera del alcance al estar dentro de la tabla derivada. Esto es porque la tabla derivada es evaluada de manera independiente de las otras tablas en el Select.
Para limitar los registros en Tabla B de manera de cumplir la condición B.Val = A.Val, nosotros tenemos que hacerlo "fuera" de la tabla derivada por medio de un join o en el criterio.
select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val
(por supuesto, lo de arriba es equivalente a hacer un inner join en la tabla derivada o simplemente uniendo a la tabla B).
También, tengamos en cuenta que el alcance de las tablas derivadas no rige solamente para los CROSS JOINS sino que también se aplica a todos los otros JOINS (CROSS, INNER, OUTER) e incluso para UNION. Todas estas sentencias usan tablas derivadas "auto-contenidas".
Esto es diferente a un sub-select correlativo en donde el SELECT principal está en el alcance para el sub-query. El sub-select es evaluado por cada registro en el query, de manera que otras tablas y columnas en el SELECT están disponibles.
select A.*, (select B.X from B where B.Val=A.Val) as X
from A
(obviamente el subquery tiene que regresar solo un registro).
Esta es una manera facil de pensar las diferencia entre CROSS JOIN y CROSS APPLY. CROSS JOIN, tal como vimos, une a una tabla derivada, sin embargo, CROSS APPLY, a pesar de lucir como un JOIN en realidad es aplicado al subselect correlativo. Esto impone las ventajas de subselects correlativos y además tiene implicaciones a nivel de performance.
Ahora si, podemos reescribir nuestro primer ejemplo usando CROSS APPLY y quedaría de la siguiente forma:
select A.*, b.X
from A
cross apply (select B.X from B where B.Val=A.Val) b
Como ahora aplicamos un APPLY y no un JOIN, A.Val está en alcance y esto funciona ok.
Table Valued User Defined Functions
Las mismas reglas aplican cuando usamos Table-Valued User-Defined Functions:
select A.*, B.X
from A
cross join dbo.UDF(A.Val) B
Esto es no válido nuevamente. A.Val no está en alcance para ser usado por la UDF.
Lo mejor que podíamos hacer antes de SQL 2005 era usar un subselect correlativo:
select A.*, (select X from dbo.UDF(A.Val)) X
from A
Sin embargo esto no es funcionalmente equivalente. La UDF no regresa mas de un registro o eso sería un error.
Con SQL 2005 podemos también en este caso usar CROSS APPLY y todo funcionará perfectamente:
select A.*, b.X
from A
cross apply dbo.UDF(A.Val) b
Esta es una manera de pensar las diferencias entre JOIN y APPLY, JOIN combina dos resultsets separados, pero APPLY es mas que un loop que evalua un resultset una y otra vez por cada registro.
Esto significa que en general APPLY será menos eficiente que un JOIN, del mismo modo que sub-selects correlativos son menos eficientes que tablas derivadas.
Entonces, cual es la ventaja de usar CROSS APPLY en vez de un sub-select correlacionado?
Bueno, muchas ventajas, por empezar...es mucho mas poderoso !.
CROSS APPLY puede regresar múltiples registros.
Esto nos permite hacer cosas como "joinear" una tabla a una función que parsear una columna csv en esa misma tabla en multiples registros.
select A.ID, b.Val
from A
cross apply dbo.ParseCSV(A.CSV) b
Cuando la funcion ParseCSV regresa multiples registros, simplemente actua como si hubiesemos joineado una tabla, duplicando los registros en tabla A por cada registro en la tabla de join.
Esto no se puede hacer con un sub-select correlativo, porque tira error.
CROSS APPLY puede retornar múltiples columnas.
De nuevo, en un sub-select correlativo podemos devolver un solo valor.
Si escribimos un script que regrese una sumatoria, podemos usar un subselect como el siguiente:
select o.*,
(select sum(Amount) from Order o
where p.OrderDate <= o.OrderDate) as RunningSum
from Order o
Sin embargo, que pasa si queremos regresar sumas adicionales de pedidos basados en algún otro criterio (pedidos con el mismo "ordercode")?.
Necesitaríamos otro subselect correlacionado, reduciendo la eficacia de nuestro select.
select o.*,
(select sum(Amount) from Order o
where p.OrderDate <= o.OrderDate) as RunningSum,
(select sum(Amount) from Order o
where p.OrderCode = o.OrderCode and p.OrderDate <= o.OrderDate) as SameCode
from Order o
Pero con un CROSS-APPLY seria mucho mas sencillo.
select o.*, rs.RunningSum, rs.SameCode
from Order o
cross apply
(
select
sum(Amount) as RunningSum,
sum(case when p.OrderCode = o.OrderCode then Amount else 0 end) as SameCode
from Order P
where P.OrderDate <= O.OrderDate
) rs
Entonces, tenemos el beneficio de regresar multiples columnas como si fuese una tabla derivada, y también tenemos la habilidad de referenciar valores en nuestro select.
Bastante poderoso.
Con CROSS APPLY podemos facilmente recuperar columnas del registro anterior en una tabla.
select o.*, prev.*
from Order o
cross apply
(
select top 1 *
from Order P where P.OrderDate < O.OrderDate
order by OrderDate DESC
) prev
Note que el script anterior no regresará pedidos que no tengan un pedido anterior, debemos usar OUTER APPLY para asegurarnos que todos los pedidos serán regresados, incluso si no existen pedidos previos.
select o.*, prev.*
from Order o
outer apply
(
select top 1 *
from Order P where P.OrderDate < O.OrderDate
order by OrderDate DESC
) prev
Twitter: @bernachea
