We may need to use a table valued function with our other tables in a given query and invoke for each row. We can’t use a join statement for this purpose instead we need to use Apply operators either Cross Apply or Outer Apply. Cross Apply acts similar to inner join it returns if there is a matching record returned by the function while Outer Apply is similar to outer join.
Let us see we could leverage both operators for table valued functions in action:
Let us first check our Person table whose last name is “shoop”. We have two records.
--Check table SELECT p.BusinessEntityID AS PersonID, P.FirstName,P.LastName,P.PersonType FROM Person.Person P WHERE P.LastName = 'Shoop'; |
Now let us try to join our table valued function with our table using inner join. It fails because it is not legal operation.
--Inner join SELECT P.FirstName,P.LastName,P.PersonType , C.PersonID,C.JobTitle,C.BusinessEntityType FROM Person.Person P INNER JOIN dbo.ufnGetContactInformation(P.BusinessEntityID) AS C ON P.BusinessEntityID = C.PersonID WHERE P.LastName = 'Shoop'; |
Let us now try to use the table valued function with cross apply. This time it will only return only one record that there is a matching record in the table valued function.
--Cross Apply SELECT P.FirstName,P.LastName,P.PersonType , C.PersonID,C.JobTitle,C.BusinessEntityType FROM Person.Person P CROSS APPLY dbo.ufnGetContactInformation(P.BusinessEntityID) AS C WHERE P.LastName = 'Shoop'; |
Outer apply will return all records of the left side table and for those records that there is not matching record of a table valued function it will return null.
--Outer Apply SELECT P.FirstName,P.LastName,P.PersonType , C.PersonID,C.JobTitle,C.BusinessEntityType FROM Person.Person P OUTER APPLY dbo.ufnGetContactInformation(P.BusinessEntityID) AS C WHERE P.LastName = 'Shoop'; |
Read more
http://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx