Using T-SQL CROSS APPLY and OUTER APPLY
However, there are limitations. You can't use a table valued function passing in an outer query parameter, and you can't use a nested subquery that returns multiple rows. For example, given a function GetParents (see Listing 3) that accepts PersonID and returns mom and dad information, you cannot use the outer query PersonID to invoke the function (see Listing 4).
Listing 3: A table-value function that returns mom and dad information.
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;
Listing 4: This code won't work because the outer query value cannot be passed to the function.
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.
About the Author
Paul Kimmel is the VB Today columnist for www.codeguru.com and has written several books on object-oriented programming and .NET. Check out his upcoming book LINQ Unleashed for C#, now available on Amazon.com and at fine bookstores everywhere. Look for his upcoming book Teach Yourself the ADO.NET Entity Framework in 24 Hours. Paul is a consultant to Developer Express, Inc. You may contact him for technology questions at firstname.lastname@example.org.
Copyright © 2009 by Paul T. Kimmel. All Rights Reserved.
Page 2 of 2