CROSS APPLY and OUTER APPLY Operator

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&nbsp; ,
       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';

CROSS APPLY and OUTER APPLY Operator Step2

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&nbsp; ,
       C.PersonID,C.JobTitle,C.BusinessEntityType
FROM Person.Person P
CROSS APPLY dbo.ufnGetContactInformation(P.BusinessEntityID) AS C
WHERE P.LastName = 'Shoop';

CROSS APPLY and OUTER APPLY Operator Step3

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';

CROSS APPLY and OUTER APPLY Operator Step4