Utilisation de APPLY

L'opérateur APPLY vous permet d'appeler une fonction table pour chaque ligne retournée par l'expression de table externe d'une requête. La fonction table agit en tant qu'entrée droite et l'expression de table externe en tant qu'entrée gauche. L'entrée droite est évaluée pour chaque ligne de l'entrée gauche, les lignes produites étant combinées dans l'entrée finale. La liste des colonnes produites par l'opérateur APPLY correspond au jeu de colonnes de l'entrée gauche suivi de la liste des colonnes retournées par l'entrée droite.

Il existe deux formes d'opérateurs APPLY : CROSS APPLY et OUTER APPLY. L'opérateur CROSS APPLY retourne uniquement les lignes de la table externe produisant un jeu de résultats à partir de la fonction table. L'opérateur OUTER APPLY retourne les lignes produisant un jeu de résultats ainsi que les lignes ne produisant pas un tel ensemble, des valeurs NULL étant indiquées dans les colonnes produites à partir de la fonction table.

Par exemple, prenons les deux tables suivantes : Employees et Departments.

--Create Employees table and insert values.
CREATE TABLE Employees
(
    empid   int         NOT NULL
    ,mgrid   int         NULL
    ,empname varchar(25) NOT NULL
    ,salary  money       NOT NULL
    CONSTRAINT PK_Employees PRIMARY KEY(empid)
);
GO
INSERT INTO Employees VALUES(1 , NULL, 'Nancy'   , $10000.00);
INSERT INTO Employees VALUES(2 , 1   , 'Andrew'  , $5000.00);
INSERT INTO Employees VALUES(3 , 1   , 'Janet'   , $5000.00);
INSERT INTO Employees VALUES(4 , 1   , 'Margaret', $5000.00);
INSERT INTO Employees VALUES(5 , 2   , 'Steven'  , $2500.00);
INSERT INTO Employees VALUES(6 , 2   , 'Michael' , $2500.00);
INSERT INTO Employees VALUES(7 , 3   , 'Robert'  , $2500.00);
INSERT INTO Employees VALUES(8 , 3   , 'Laura'   , $2500.00);
INSERT INTO Employees VALUES(9 , 3   , 'Ann'     , $2500.00);
INSERT INTO Employees VALUES(10, 4   , 'Ina'     , $2500.00);
INSERT INTO Employees VALUES(11, 7   , 'David'   , $2000.00);
INSERT INTO Employees VALUES(12, 7   , 'Ron'     , $2000.00);
INSERT INTO Employees VALUES(13, 7   , 'Dan'     , $2000.00);
INSERT INTO Employees VALUES(14, 11  , 'James'   , $1500.00);
GO
--Create Departments table and insert values.
CREATE TABLE Departments
(
    deptid    INT NOT NULL PRIMARY KEY
    ,deptname  VARCHAR(25) NOT NULL
    ,deptmgrid INT NULL REFERENCES Employees
);
GO
INSERT INTO Departments VALUES(1, 'HR',           2);
INSERT INTO Departments VALUES(2, 'Marketing',    7);
INSERT INTO Departments VALUES(3, 'Finance',      8);
INSERT INTO Departments VALUES(4, 'R&D',          9);
INSERT INTO Departments VALUES(5, 'Training',     4);
INSERT INTO Departments VALUES(6, 'Gardening', NULL);

La plupart des services de la table Departments possèdent un ID de responsable correspondant à un employé de la table Employees. La fonction table suivante accepte l'ID d'employé en tant qu'argument et retourne cet employé ainsi que tous ses subordonnés.

CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) 
    RETURNS @TREE TABLE
(
    empid   INT NOT NULL
    ,empname VARCHAR(25) NOT NULL
    ,mgrid   INT NULL
    ,lvl     INT NOT NULL
)
AS
BEGIN
  WITH Employees_Subtree(empid, empname, mgrid, lvl)
  AS
  ( 
    -- Anchor Member (AM)
    SELECT empid, empname, mgrid, 0
    FROM Employees
    WHERE empid = @empid

    UNION all
    
    -- Recursive Member (RM)
    SELECT e.empid, e.empname, e.mgrid, es.lvl+1
    FROM Employees AS e
      JOIN Employees_Subtree AS es
        ON e.mgrid = es.empid
  )
  INSERT INTO @TREE
    SELECT * FROM Employees_Subtree;

  RETURN
END
GO

Pour retourner tous les subordonnés du responsable de chaque service, à quelque niveau que ce soit, utilisez la requête suivante.

SELECT D.deptid, D.deptname, D.deptmgrid
    ,ST.empid, ST.empname, ST.mgrid
FROM Departments AS D
    CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST;

Voici l'ensemble des résultats.

deptid      deptname   deptmgrid   empid       empname    mgrid       lvl
----------- ---------- ----------- ----------- ---------- ----------- ---
1           HR         2           2           Andrew     1           0
1           HR         2           5           Steven     2           1
1           HR         2           6           Michael    2           1
2           Marketing  7           7           Robert     3           0
2           Marketing  7           11          David      7           1
2           Marketing  7           12          Ron        7           1
2           Marketing  7           13          Dan        7           1
2           Marketing  7           14          James      11          2
3           Finance    8           8           Laura      3           0
4           R&D        9           9           Ann        3           0
5           Training   4           4           Margaret   1           0
5           Training   4           10          Ina        4           1

Comme vous pouvez le constater, chaque ligne de la table Departments est dupliquée autant de fois qu'il y a de lignes retournées à l'aide de fn_getsubtree pour chaque responsable de service.

Par ailleurs, le service Gardening ne figure pas dans les résultats. Étant donné que ce service est dépourvu de responsable, fn_getsubtree a retourné un ensemble vide. En revanche, si vous utilisez l'opérateur OUTER APPLY, le service Gardening apparaît dans le jeu de résultats avec des valeurs nulles dans le champ deptmgrid ainsi que dans les champs retournés par fn_getsubtree.