Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Outer apply Expand / Collapse
Author
Message
Posted Wednesday, April 27, 2011 10:16 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, August 12, 2012 1:29 AM
Points: 99, Visits: 470
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)

Post #1099616
Posted Wednesday, April 27, 2011 10:55 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, July 26, 2014 8:57 AM
Points: 7,081, Visits: 12,575
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
Post #1099646
Posted Wednesday, April 27, 2011 11:56 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 4:32 PM
Points: 4,006, Visits: 6,069
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? -- Stephen Stills
Post #1099694
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse