Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Outer apply Expand / Collapse
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



Group: General Forum Members
Last Login: Sunday, May 22, 2016 7:33 PM
Points: 7,916, Visits: 14,251
OUTER APPLY is very different from OUTER JOIN which is where you would expect to see "the condition to join the tables".


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



Group: General Forum Members
Last Login: Friday, April 29, 2016 3:18 PM
Points: 2,811, Visits: 6,182
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