Usar APPLY

El operador APPLY permite invocar una función con valores de tabla para cada fila devuelta por una expresión de tabla externa de una consulta. La función con valores de tabla actúa como la entrada derecha y la expresión de tabla externa como la entrada izquierda. La entrada derecha se evalúa para cada fila de la entrada izquierda y las filas producidas se combinan en la salida final. La lista de columnas producidas por el operador APPLY corresponde al conjunto de columnas de la entrada izquierda, seguido de la lista de columnas devueltas por la entrada derecha.

Existen dos formas de APPLY: CROSS APPLY y OUTER APPLY. CROSS APPLY sólo devuelve las filas de la tabla externa que producen un conjunto de resultados de la función con valores de tabla. OUTER APPLY devuelve tanto las filas que producen un conjunto de resultados como las que no, con valores NULL en la columna producida por la función con valores de tabla.

Considere, por ejemplo, las siguientes tablas, Employees y 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 mayoría de los departamentos de la tabla Departments tienen un Id. de administrador que corresponde a un empleado de la tabla Employees. La siguiente función con valores de tabla acepta un Id. de empleado como argumento y devuelve el empleado con todos sus subordinados.

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

Para que se devuelvan todos los subordinados de todos los niveles para el administrador de cada departamento, utilice la consulta siguiente.

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;

El conjunto de resultados es el siguiente.

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

Observe que cada fila de la tabla Departments se duplica tantas veces como filas de fn_getsubtree se devuelven para el administrador del departamento.

Además, el departamento Gardening no aparece en los resultados. Como este departamento no tiene administrador, fn_getsubtree devolvió un conjunto vacío. Si se utiliza OUTER APPLY, el departamento Gardening también aparecerá en el conjunto de resultados, con valores NULL en el campo deptmgrid y en los campos devueltos por fn_getsubtree.