Usando APPLY

O operador APPLY permite que você invoque uma função com valor de tabela para cada linha retornada por uma expressão de tabela externa de uma consulta. A função com valor de tabela age como a entrada à direita e a expressão de tabela exterior age como a entrada à esquerda. A entrada à direita é avaliada para cada linha da entrada à esquerda e as linhas produzidas são combinadas na saída final. A lista de colunas produzida pelo operador APPLY é o conjunto de colunas na entrada à esquerda, seguido pela lista de colunas retornadas pela entrada à direita.

Observação
Para usar APPLY, o nível de compatibilidade do banco de dados deve ser no mínimo 90.

Há duas formas de APPLY: CROSS APPLY e OUTER APPLY. CROSS APPLY só retorna linhas da tabela exterior que produzem um conjunto de resultados da função com valor de tabela. OUTER APPLY retorna linhas que produzem um conjunto de resultados e linhas que não o fazem, com valores NULL nas colunas produzidas pela função com valor de tabela.

Como exemplo, considere as tabelas seguintes, Employees e 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);

A maioria dos departamentos na tabela Departments tem uma ID de gerente que corresponde a um funcionário na tabela Employees. A seguinte função com valor de tabela aceita uma ID de funcionário como um argumento e retorna esse funcionário e todos os seus 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 retornar todos os subordinados em todos os níveis para o gerente de cada departamento, use a consulta a seguir.

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;

Aqui está o conjunto de resultados.

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 linha da tabela Departments é duplicada tantas vezes quantas forem as linhas retornadas de fn_getsubtree para o gerente do departamento.

Além disso, o departamento Gardening não aparece nos resultados. Como esse departamento não tem gerente, fn_getsubtree retornou um conjunto vazio. Ao usar OUTER APPLY, o departamento Gardening também aparecerá no conjunto de resultados, com valores nulos no campo deptmgrid, bem como nos campos retornados por fn_getsubtree.