April 24, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using T-SQL CROSS APPLY and OUTER APPLY, Page 2

  • January 26, 2009
  • By Paul Kimmel, Paul Kimmel
  • Send Email »
  • More Articles »

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.

Summary

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 pkimmel@softconcepts.com.

Copyright © 2009 by Paul T. Kimmel. All Rights Reserved.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel