April 27, 2011 at 10:16 am
Hi guys. Someone please explain me what this outer apply is doing. I mean outer apply is used to join the tables but where is the condition to join the tables. I am little confused over this.
select OrderID, orderlineID
, TearSheetCount, tref.value('(Address/text())[1]', 'varchar(100)') as TearSheetAddress
from [Order].[Orderline] oln
outer apply TearSheetSpec.nodes('/TearSheetSpec/ShippingInstruction/TearSheetShipping') as Tearsheet(tref)
April 27, 2011 at 10:55 am
OUTER APPLY is very different from OUTER JOIN which is where you would expect to see "the condition to join the tables".
From http://msdn.microsoft.com/en-us/library/ms175156.aspx
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.
APPLY is very useful (and awesome) and worth the time to get to know.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 27, 2011 at 11:56 am
I find it helpful to think of OUTER APPLY as being similar to a LEFT JOIN.
When you use a CROSS APPLY, if the applied function (or query) returns no rows, the rows from the primary table get dropped from the result set. If you use OUTER APPLY, you see the data from the primary table whether or not the applied function returns any rows.
In your example, the OUTER APPLY makes sure that whatever data you have is displayed whether or not the node specified exists in the XML>
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply