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:
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.
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.
