I think the easiest way to use of CROSS APPLY is that it is like a CROSS JOIN with a correlated sub-query instead of a derived table. Let's see if I can explain the bit of cross apply.... A derived table is "self-contained", in that all tables and columns in the parent SELECT are not accessible. For example, consider:
Select A.*, b.X
from A
cross join
(select B.X from B where B.Val=A.Val) b
That is not legal because A.Val is out of scope within the derived table; this is because the derived table is evaluated independently of the other tables in the SELECT. To limit the rows in table B so that B.Val = A.Val, we must do that outside of the derived table via a join or in the criteria:
select A.*, b.X
from A
cross join (select * from B) b
where A.Val = b.Val
(The above example is equivalent to doing an INNER JOIN to the derived table)
Also want you keep updated that, the scope-of-derived-tables rule isn't just for CROSS JOINS, it's for all JOINS -- CROSS, INNER, OUTER and even UNION; they all use "self-contained" derived tables.
This is an easy way to think of the difference between CROSS JOIN and CROSS APPLY. CROSS JOIN, as we saw, joins to a derived table; however, CROSS APPLY, despite looking like a JOIN, actually is applying a correlated sub-query. This imposes both the advantages of a correlated sub-query but also the performance implications.
So, we can simply rewrite our first example using CROSS APPLY like this:
Select A.*, b.X
from A
cross apply (select B.X from B where B.Val=A.Val) b
Since we are performing an APPLY and not a JOIN, A.Val is in scope and it works just fine. Please comment if having any query or want to know some more more topics related
SQL..... Thanks DevB......
