Outer apply

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Outer apply

Outer apply

Rate Topic

Display Mode

Topic Options

Author

Message

varunkum

Posted Wednesday, April 27, 2011 10:16 AM

SSC 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

opc.three

Posted Wednesday, April 27, 2011 10:55 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:57 PM
Points: 6,724, Visits: 11,771

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

The Dixie Flatline

Posted Wednesday, April 27, 2011 11:56 AM

Hall of Fame

Group: General Forum Members
Last Login: Today @ 3:05 PM
Points: 3,789, Visits: 5,547

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 »

Permissions