How to inner join an inline table valued function in SQL
When faced with the task of creating an inline table valued function in T-SQL, sometimes you'll want to inner join this function with other tables. It seems like it should be straight forward to do, but it really isn't. There's a variety of workarounds, including options such as creating a view that references the new user defined table valued function. If performance isn't a tremendous concern to your application, the "cross apply" method is probably the most straight forward. Keep in mind that this method was introduced as of SQL Server 2005. So if you're using SQL 2000 or an earlier version, you're out of luck. Honestly, I've found that the performance hit with using cross apply is greatly exaggerated. Here's how it would work.
If you REALLY want this:
SELECT *
FROM Table1
INNER JOIN dbo.fn_TableValuedFunction(Table1.ID) AS TVF ON TVF.Table1ID = Table1.ID
Do this:
SELECT *
FROM Table1
CROSS APPLY dbo.fn_TableValuedFunction(Table1.ID)
Similarly, if you're looking for an outer join, or left join, with a user defined inline table function, replace "CROSS APPLY" in the previous example with "OUTER APPLY".
Filed Under Database
Tags: 2005, cross apply, inline, inner join, left join, outer apply, outer join, query, select, sql, table valued function, user defined
Comments
Got something to say?