Suchen...
Generic filters
Exact matches only
Search in title
Search in excerpt
Search in content

ParentChild-Tabellen „flachklopfen“ - Cross Apply

In diesem Blogbeitrag stellen wir Ihnen die Verwendung von CROSS und OUTER APPLY vor. Seit SQL Server 2005 existiert der Apply Operator und ist in der Tat eine tolle Erweiterung in T-SQL.

Implementierungsansatz

Sollten Sie die Hilfe von SQL Server zur Rate ziehen, wird es im ersten Blick nicht ganz deutlich, was der Apply Operator denn wirklich für einen Vorteil bringt. Dort heißt es wörtlich:

“Der APPLY-Operator ermöglicht Ihnen das Aufrufen einer Tabellenwertfunktion für sämtliche Zeilen, die von einem äußeren Ausdruck einer Tabelle einer Abfrage zurückgegeben werden. Die Tabellenwertfunktion dient als rechte Eingabe, der äußere /Ausdruck der Tabelle agiert als linke Eingabe. Die rechte Eingabe wird für jede Zeile aus der linken Eingabe ausgewertet, und die erstellten Zeilen werden für die endgültige Ausgabe kombiniert. Bei der Liste der vom APPLY-Operator erstellten Spalten handelt es sich um den Satz von Spalten in der linken Eingabe, gefolgt von der Liste der von der rechten Eingabe zurückgegebenen Spalten.”

In dieser Veröffentlichung stellen wir Ihnen eine der Möglichkeiten der Nutzung von APPLY vor und zwar in unseren Augen das meistbenutzte Szenarium.

ParentChild-Tabellen „flachklopfen“

Eine übliche Schwierigkeit in relationalen Tabellen ist die Darstellung von Hierarchien. Eine klassischer Weg der Darstellung der Hierarchie ist das Abstammungsprinzip. Großvater und Großmutter, Vater und Mutter, Sohn und Tochter. Im folgenden Beispiel legen wir zuerst die erforderlichen Tabellen und Inhalte an:

CREATE TABLE T_Familie
(
   PersonID   int         NOT NULL,
   MutterID   int         NULL,
   VaterID    int         NULL,
   Name       varchar(25) NOT NULL,
   CONSTRAINT PK_T_Familie PRIMARY KEY(PersonID),
)
GO

INSERT INTO T_Familie VALUES(1  , NULL, NULL, 'Thomas Müller')
INSERT INTO T_Familie VALUES(2  , NULL, NULL, 'Markus Müller')
INSERT INTO T_Familie VALUES(3  , NULL, NULL, 'Anna Meier')
INSERT INTO T_Familie VALUES(4  , NULL, NULL, 'Frank Meier')
INSERT INTO T_Familie VALUES(5  , 2, 1,       'Silke Schröder')
INSERT INTO T_Familie VALUES(6  , 3, 4,       'Gerald Meier')
INSERT INTO T_Familie VALUES(7  , 5, 6,       'Katja Wagner')
INSERT INTO T_Familie VALUES(8  , 5, 6,       'Daniel Meier')
INSERT INTO T_Familie VALUES(9  , 5, 6,       'Horst Meier')
INSERT INTO T_Familie VALUES(10 , 5, 7,       'Chris Schröder')
INSERT INTO T_Familie VALUES(11 , 5, 7,       'Hand Schröder')
INSERT INTO T_Familie VALUES(12 , 5, 6,       'Johann Meier')
INSERT INTO T_Familie VALUES(13 , 5, 6,       'Paul Meier')
INSERT INTO T_Familie VALUES(14 , NULL, NULL, 'Sandra Meier')
INSERT INTO T_Familie VALUES(15 , NULL, NULL, 'Jörg Schröder')
INSERT INTO T_Familie VALUES(16 , 14, 13,     'Alexander Meier')
INSERT INTO T_Familie VALUES(17 , 14, 13,     'Konrad Meier')

Die Tabelle hat folgende Inhalte:

2011-09-23_Crew_T_Familie

Abb. 1: T_Familie

Als Beispiel sehen wir uns die Person mit der ID 6 (Gerald Meier) an. Gerald hat die Anna Meier (PersonID 3) als Mutter und den Frank Meier (PersonID 4) als Vater. Er ist aber selbst in den Fällen (PersonID 7, 8, 9, 12 und 13) der Vater.

Die Herausforderung ist nun eine Datenbankabfrage zu definieren, welche in korrekter Form die Hierarchie der Abstammung in einer flachen Tabelle zurückgibt.

Nutzung von mehreren Joins (selfjoins)

Durch das „mit sich Joinen“ der Tabelle einmal über MutterID / PersonID und einmal über VaterID / PersonID können wir die gewünschte Hierarchie abbilden.

select  p1.Name as MyName,
p2.Name AS Mother,
p3.Name As Father
from        T_Familie p1
left join   T_Familie p2
on      p1.MutterID = p2.PersonID
left join   T_Familie p3
on      p1.VaterID = p3.PersonID

Diese Methode hat aber Einschränkungen. Sobald man daran denkt eine Funktion einzusetzen, um nur einmal Code zu schreiben und zu isolieren, werden die „selfjoins“ zu einem Problem. Es ist weder möglich in einer Funktion das Ergebnis einer “outer query” als Parameter zurückzugeben, noch kann eine verschachtelte Subquery (nested subquery) welche mehrere Zeilen zurückgibt, benutzt werden.

Im folgenden Beispiel wird es deutlich. Zuerst erstellen wir eine Funktion, die uns anhand der PersonID die Mutter und den Vater zurückgibt:

Create FUNCTION [dbo].[GetParents](@PersonID int)
RETURNS @Parents TABLE
(
   [PersonID] [int] PRIMARY KEY NOT NULL,
   [ICH] [varchar](25),
   [Mutter] [varchar](25) NULL,

   [Vater] [varchar](25) NULL
)
AS
BEGIN
   INSERT INTO @Parents
   SELECT
      p1.PersonID,
      p1.Name AS [Self],
      p2.[Name] AS Mutter,
      p3.[Name] AS Vater FROM
      T_Familie p1 INNER JOIN T_Familie p2 ON
      p1.MutterID = p2.PersonID INNER JOIN
      T_Familie p3 ON p1.VaterID = p3.PersonID
   WHERE
      p1.PersonID = @PersonID
   RETURN
END

Wenn man nun versucht mit dem folgenden Code die Funktion zu benutzen, führt es zu einem Fehler. Der Grund ist, wie schon erwähnt, dass der Wert der „Outer Query“ nicht an die Funktion weitergereicht werden kann. Hier kommt dann der CROSS APPLY zum Einsatz.

Nutzung von CROSS APPLY

Beim Einsatz einer Funktion wird der CROSS APPLY in dem SELECT-Code benutzt, um die Funktion „GetParents“ mit dem Parameter „PersonID“ aufzurufen.

Der CROSS APPLY gibt dieselben Datensätze wie der „multiple“ Inner Join zurück und ruft die Funktion mit einem Wert aus dem Select-Statement auf.

SELECT p1.PersonID, p1.Name, p2.Mutter, p2.Vater
FROM T_Familie p1
CROSS APPLY GetParents(p1.PersonID) p2

Der Obige SQL-Code gibt die Hierarchie mit der jeweiligen Person, die Mutter und den Vater zurück. Setzt man statt CROSS APPLY den OUTER APPLY ein, erhält man auch die Datensätze ohne Eltern. In anderen Worten ist der CROSS APPLY ähnlich wie der INNER JOIN und der OUTER APPLY ähnlich wie der LEFT JOIN.

2011-09-23_Crew_T_Familie flachgeklopft

Abb. 2: T_Familie „flachgeklopft“

Einsatz von CROSS APPLY ohne Funktion

Folgender Code kann benutzt werden, wenn man keine Funktion im Einsatz hat:

SELECT p1.PersonID, p1.[Name], M.Name as Mutter,
   F.Name As Vater FROM T_Familie p1
CROSS APPLY
(SELECT p2.PersonID, p2.[Name] FROM T_Familie p2
   WHERE p1.MutterID = p2.PersonID) M
CROSS APPLY
(SELECT PersonID, [Name] FROM T_Familie p3
   WHERE p1.VaterID = p3.PersonID) F

Sollte keine Funktion benutzt werden, dann kann man auch statt CROSS APPLY mehrere INNER JOINS einsetzen.

Zusammenfassung

Es können also Werte aus einer Abfrage nicht als Parameter zum Joinen von Queries oder Sub-Queries benutzt werden, wenn diese Queries mehr als nur ein Ergebnis zurückliefern.
Wenn man Daten aus einer Query als Input für eine Funktion braucht oder man mehrere Datensätze zurückerhalten will, muss der APPLY Operator in Anspruch genommen werden.