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.
Nota
Para utilizar APPLY, el nivel de compatibilidad de la base de datos debe ser al menos 90.
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.