SQL
kruis toepassen, buiten toepassen

CROSS APPLY en OUTER APPLY basics

Toepassen wordt gebruikt wanneer de functie met de tabelwaarde in de juiste uitdrukking wordt gebruikt.

maak een Afdelingstabel om informatie over afdelingen te bewaren. Maak vervolgens een Medewerkertabel met informatie over de medewerkers. Let op: elke werknemer behoort tot een afdeling, vandaar dat de tabel Werknemer referentiële integriteit heeft met de tabel Afdeling.

De eerste query selecteert gegevens uit de tabel Afdeling en gebruikt CROSS APPLY om de tabel Werknemer voor elk record van de tabel Afdeling te evalueren. Tweede query sluit eenvoudigweg aan op de Afdelingstabel met de Medewerkertabel en alle overeenkomende records worden geproduceerd.

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

Als je kijkt naar de resultaten die ze produceerden, is het exact dezelfde resultaatset; Waarin verschilt het van een JOIN en hoe helpt het bij het schrijven van efficiëntere vragen.

De eerste query in Script # 2 selecteert gegevens uit de tabel Afdeling en gebruikt BUITEN TOEPASSING om de tabel Werknemer voor elk record van de tabel Afdeling te evalueren. Voor die rijen waarvoor er geen overeenkomst is in de tabel Werknemer, bevatten die rijen NULL-waarden zoals u kunt zien in het geval van rij 5 en 6. De tweede query gebruikt gewoon een LINKER BUITENLIJST tussen de afdelingslijst en de werknemertabel. Zoals verwacht retourneert de query alle rijen uit de tabel Afdeling; zelfs voor die rijen waarvoor er geen overeenkomst is in de tabel Werknemer.

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

Hoewel de bovenstaande twee query's dezelfde informatie retourneren, zal het uitvoeringsplan enigszins verschillen. Maar qua kosten zal er niet veel verschil zijn.

Nu komt het moment om te zien waar de APPLY-operator echt nodig is. In Script # 3 maak ik een functie met tabelwaarde die DepartmentID als parameter accepteert en alle werknemers retourneert die bij deze afdeling horen. De volgende query selecteert gegevens uit de tabel Afdeling en gebruikt CROSS APPLY om samen te werken met de functie die we hebben gemaakt. Het geeft de Afdeling-ID door voor elke rij uit de buitenste tabelexpressie (in ons geval Afdelingstabel) en evalueert de functie voor elke rij vergelijkbaar met een gecorreleerde subquery. De volgende query gebruikt de BUITEN TOEPASSING in plaats van CROSS TOEPASSING en daarom, in tegenstelling tot CROSS TOEPASSING die alleen gecorreleerde gegevens heeft geretourneerd, retourneert de BUITEN TOEPASSING ook niet-gecorreleerde gegevens, waarbij NULL's in de ontbrekende kolommen worden geplaatst.

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

Dus als u zich nu afvraagt, kunnen we een eenvoudige join gebruiken in plaats van de bovenstaande vragen? Dan is het antwoord NEE, als u CROSS / OUTER APPLY in de bovenstaande query's vervangt door INNER JOIN / LEFT OUTER JOIN, geeft u de clausule ON op (zoiets als 1 = 1) en voert u de query uit, dan krijgt u "The multi-part identifier" D.DepartmentID "kon niet worden gebonden." fout. Dit komt omdat met JOINs de uitvoeringscontext van de buitenste query verschilt van de uitvoeringscontext van de functie (of een afgeleide tabel) en u een waarde / variabele van de buitenste query niet als parameter kunt binden aan de functie. Daarom is de APPLY-operator vereist voor dergelijke vragen.