Using T-SQL CROSS APPLY and OUTER APPLY

USE [Genealogy]  GO  /****** Object:  UserDefinedFunction [dbo].[GetParents]          Script Date: 01/22/2009 14:29:00 ******/  SET ANSI_NULLS ON  GO  SET QUOTED_IDENTIFIER ON  GO  ALTER FUNCTION [dbo].[GetParents](@PersonID int)  RETURNS @Parents TABLE  (     [PersonID] [int] PRIMARY KEY NOT NULL,     [Self] [varchar](25),     [Mother] [varchar](25) NULL,         [Father] [varchar](25) NULL  )  AS  BEGIN     INSERT INTO @Parents     SELECT        p1.PersonID,        p1.Name AS [Self],        p2.[Name] AS Mother,        p3.[Name] AS Father FROM        People p1 INNER JOIN People p2 ON        p1.MotherID = p2.PersonID INNER JOIN        People p3 ON p1.FatherID = p3.PersonID     WHERE        p1.PersonID = @PersonID;     RETURN;  END;  
select p1.PersonID, p1.Name, GetParents(p1.PersonID)  FROM People p1  
  
There are a lot of reasons you might prefer a function—the function already exists, code reuse, and isolation. Here is where CROSS APPLY shines. In the presence of a function, you can use CROSS APPLY to invoke the GetParents function, passing in the PersonID from the select to the function (see Listing 5).
  
Listing 5: CROSS APPLY returns the same data as the multiple inner joins and call the function with a value from the select statement.
  
select p1.PersonID, p1.Name, p2.Mother, p2.Father  FROM People p1  CROSS APPLY GetParents(p1.PersonID) p2  
  
Listing 5 returns the hierarchy where there is a father and mother. If you change CROSS APPLY to OUTER APPLY, you will get rows without parents. In short, CROSS APPLY responds similarly to an INNER JOIN and OUTER APPLY responds similarly to a LEFT JOIN. Listing 6 contains a complete solution with CROSS APPLY and no function, and Listing 7 shows the OUTER APPLY with no function.
  
Listing 6: Assembling the self, mother, and father data where there are mothers and fathers using CROSS APPLY.
  
SELECT p1.PersonID, p1.[Name], M.Name as Mother,     F.Name As Father FROM PEOPLE p1  CROSS APPLY  (SELECT p2.PersonID, p2.[Name] FROM PEOPLE p2     WHERE p1.MotherID = p2.PersonID) M  CROSS APPLY  (SELECT PersonID, [Name] FROM PEOPLE p3     WHERE p1.FatherID = p3.PersonID) F  
  
Listing 7: Assembling the self, father, and mother data where either the father and/or mother data is null using OUTER APPLY.
  
SELECT p1.PersonID, p1.[Name], M.Name as Mother,     F.Name As Father FROM PEOPLE p1  OUTER APPLY  (SELECT p2.PersonID, p2.[Name] FROM PEOPLE p2     WHERE p1.MotherID = p2.PersonID) M  OUTER APPLY  (SELECT PersonID, [Name] FROM PEOPLE p3     WHERE p1.FatherID = p3.PersonID) F  
  
Because there is no function call in Listings 6 and 7, you could implement the same result set using multiple INNER JOINs in Listing 6 and multiple LEFT JOINs in Listing 7. If you introduced a function, the joins will not work; use APPLY instead.
  
  
 
  
The big picture here is that values from one query can't be used as parameters to join queries or sub-queries if those queries return more than one result. If you need data from one query as input to a function or want to allow multiple rows to return, you want to use APPLY. Use CROSS APPLY to get only rows where a correlated value exists and use OUTER APPLY if you want to permit nulls in the result set.