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: Today @ 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1099646
Posted Wednesday, April 27, 2011 11:56 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:45 AM
Points: 3,949, Visits: 5,944
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