SQL
kors applicera, yttre applicera

CROSS APPLY och OUTER APPLY grunderna

Ansökan kommer att användas när tabellvärderad funktion i rätt uttryck.

skapa en avdelningstabell för att hålla information om avdelningar. Skapa sedan en medarbetartabell som innehåller information om de anställda. Observera att varje anställd tillhör en avdelning, varför medarbetartabellen har referensintegritet med avdelningstabellen.

Den första frågan väljer data från avdelningstabellen och använder CROSS APPLY för att utvärdera medarbetartabellen för varje post i avdelningstabellen. Den andra frågan går helt enkelt samman med avdelningstabellen med medarbetartabellen och alla matchande poster produceras.

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

Om du tittar på resultaten de producerade är det exakt samma resultatuppsättning; Hur skiljer det sig från en JOIN och hur hjälper det att skriva mer effektiva frågor.

Den första frågan i skript nr 2 väljer data från avdelningstabellen och använder YTRE APPLY för att utvärdera medarbetartabellen för varje post i avdelningstabellen. För de rader som det inte finns någon matchning i medarbetartabellen innehåller de raderna NULL-värden som du kan se i fall 5 och 6. Den andra frågan använder helt enkelt en VÄNSTERFÖRGÅNG mellan avdelningstabellen och medarbetartabellen. Som förväntat returnerar frågan alla rader från avdelningstabellen; även för de rader för vilka det inte finns någon matchning i medarbetartabellen.

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

Även om ovanstående två frågor returnerar samma information, kommer genomförandeplanen att vara lite annorlunda. Men kostnadsmässigt blir det inte mycket skillnad.

Nu är det dags att se var APPLY-operatören verkligen krävs. I skript nr 3 skapar jag en tabellvärderad funktion som accepterar DepartmentID som sin parameter och returnerar alla anställda som tillhör denna avdelning. Nästa fråga väljer data från avdelningstabellen och använder CROSS APPLY för att gå med i den funktion vi skapade. Det passerar DepartmentID för varje rad från det yttre tabelluttrycket (i vårt fall avdelningstabellen) och utvärderar funktionen för varje rad som liknar en korrelerad underkälla. Nästa fråga använder YTTERA APPLI istället för CROSS APPLY och därmed till skillnad från CROSS APPLY som bara returnerade korrelerade data, returnerar OUTER APPLY icke korrelerade data också och placerar NULLs i de saknade kolumnerna.

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

Så om du undrar, kan vi använda en enkel koppling istället för ovanstående frågor? Då är svaret NEJ, om du ersätter CROSS / OUTER APPLY i ovanstående frågor med INNER JOIN / LEFT OUTER JOIN, ange ON-klausul (något som 1 = 1) och kör frågan får du "Multidel-identifieraren" D.DepartmentID "kunde inte vara bundet." fel. Detta beror på att med JOINs exekverar sammanhanget för den yttre frågan skiljer sig från exekveringskonteksten för funktionen (eller en härledd tabell), och du kan inte binda ett värde / variabel från den yttre frågan till funktionen som en parameter. Därför krävs applikatören APPLY för sådana frågor.