SQL
Kreuz anwenden, außen anwenden

GRUNDLAGEN VON CROSS APPLY und OUTER APPLY

Anwenden wird verwendet, wenn bei der Tabellenwertfunktion der rechte Ausdruck verwendet wird.

Erstellen Sie eine Abteilungstabelle, die Informationen zu Abteilungen enthält. Erstellen Sie anschließend eine Employee-Tabelle, die Informationen zu den Mitarbeitern enthält. Bitte beachten Sie, dass jeder Mitarbeiter zu einer Abteilung gehört. Daher verfügt die Tabelle Employee über die referenzielle Integrität der Abteilungstabelle.

Die erste Abfrage wählt Daten aus der Abteilungstabelle aus und wertet CROSS APPLY aus, um die Employee-Tabelle für jeden Datensatz der Abteilungstabelle auszuwerten. Die zweite Abfrage verbindet einfach die Department-Tabelle mit der Employee-Tabelle, und alle passenden Datensätze werden erzeugt.

SELECT *
FROM Department D
CROSS APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
INNER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID

Wenn Sie sich die Ergebnisse ansehen, die sie produziert haben, ist es genau dieselbe Ergebnismenge. Wie unterscheidet es sich von einem JOIN und wie hilft es, effizientere Abfragen zu schreiben.

Die erste Abfrage in Skript Nr. 2 wählt Daten aus der Abteilungstabelle aus und wertet OUTER APPLY aus, um die Employee-Tabelle für jeden Datensatz der Abteilungstabelle auszuwerten. Für die Zeilen, für die in der Employee-Tabelle keine Übereinstimmung vorhanden ist, enthalten diese Zeilen NULL-Werte, wie Sie in den Zeilen 5 und 6 sehen können. Die zweite Abfrage verwendet lediglich einen LEFT-OUTER-JOIN zwischen der Department-Tabelle und der Employee-Tabelle. Wie erwartet gibt die Abfrage alle Zeilen aus der Department-Tabelle zurück. auch für die Zeilen, für die in der Employee-Tabelle keine Übereinstimmung vorhanden ist.

SELECT *
FROM Department D
OUTER APPLY (
    SELECT *
    FROM Employee E
    WHERE E.DepartmentID = D.DepartmentID
) A
GO
SELECT *
FROM Department D
LEFT OUTER JOIN Employee E
  ON D.DepartmentID = E.DepartmentID
GO

Obwohl die beiden obigen Abfragen dieselbe Information zurückgeben, ist der Ausführungsplan etwas anders. Aber aus Kostengründen wird es keinen großen Unterschied geben.

Jetzt kommt die Zeit, um zu sehen, wo der APPLY-Operator wirklich benötigt wird. In Skript Nr. 3 erstelle ich eine Tabellenwertfunktion, die DepartmentID als Parameter akzeptiert und alle Mitarbeiter zurückgibt, die dieser Abteilung angehören. Die nächste Abfrage wählt Daten aus der Abteilungstabelle aus und verwendet CROSS APPLY, um die von uns erstellte Funktion zu verbinden. Sie übergibt die DepartmentID für jede Zeile aus dem äußeren Tabellenausdruck (in unserem Fall Department-Tabelle) und wertet die Funktion für jede Zeile ähnlich einer korrelierten Unterabfrage aus. Die nächste Abfrage verwendet OUTER APPLY anstelle von CROSS APPLY. Daher gibt OUTER APPLY im Gegensatz zu CROSS APPLY, bei dem nur korrelierte Daten zurückgegeben wurden, auch nicht korrelierte Daten zurück, wodurch NULL-Werte in die fehlenden Spalten eingefügt werden.

CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (@DeptID AS int)
RETURNS TABLE
AS
  RETURN
  (
  SELECT
    *
  FROM Employee E
  WHERE E.DepartmentID = @DeptID
  )
GO
SELECT
  *
FROM Department D
CROSS APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO
SELECT
  *
FROM Department D
OUTER APPLY dbo.fn_GetAllEmployeeOfADepartment(D.DepartmentID)
GO

Wenn Sie sich also fragen, können wir anstelle der obigen Abfragen einen einfachen Join verwenden? Die Antwort lautet NEIN. Wenn Sie in den obigen Abfragen CROSS / OUTER APPLY durch INNER JOIN / LEFT OUTER JOIN ersetzen, die ON-Klausel angeben (etwas wie 1 = 1) und die Abfrage ausführen, erhalten Sie "Die mehrteilige Kennung". D.DepartmentID "konnte nicht gebunden werden." Error. Dies liegt daran, dass sich bei JOINs der Ausführungskontext der äußeren Abfrage vom Ausführungskontext der Funktion (oder einer abgeleiteten Tabelle) unterscheidet und Sie einen Wert / eine Variable nicht von der äußeren Abfrage als Parameter an die Funktion binden können. Daher ist der APPLY-Operator für solche Abfragen erforderlich.