APPLY 연산자를 사용하면 쿼리의 외부 테이블 식에서 반환한 각 행에 대해 테이블 값 함수를 호출할 수 있습니다. 테이블 값 함수는 오른쪽 입력이 되고 외부 테이블 식은 왼쪽 입력이 됩니다. 오른쪽 입력은 왼쪽 입력의 각 행에 대해 평가된 후 생성된 행이 조합되어 최종 출력에 표시됩니다. APPLY 연산자가 생성하는 열 목록은 왼쪽 입력의 열 목록 뒤에 오른쪽 입력에서 반환한 열 목록을 추가한 것입니다.
APPLY 연산자에는 CROSS APPLY와 OUTER APPLY의 두 형태가 있습니다. CROSS APPLY는 테이블 값 함수로부터 결과 집합을 생성하는 외부 테이블의 행만 반환합니다. OUTER APPLY는 결과 집합을 생성하는 행과 그렇지 않은 행을 모두 반환하고, 테이블 값 함수에 의해 생성된 열에는 NULL 값을 표시합니다.
예를 들면 다음의 Employees와 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)
Departments 테이블에 있는 대부분의 부서는 Employees 테이블의 직원에 해당하는 관리자 ID를 가지고 있습니다. 다음 테이블 값 함수는 직원 ID를 인수로 사용하여 직원 및 해당 직원의 모든 하위 수준을 반환합니다.
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
각 부서의 관리자에 대해 모든 수준에 있는 모든 하위 수준을 반환하려면 다음 쿼리를 사용합니다
SELECT *
FROM Departments AS D
CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
결과 집합은 다음과 같습니다.
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
Departments 테이블의 각 행은 부서 관리자의 fn_getsubtree에서 반환한 행 수만큼 중복됩니다.
또한 Gardening 부서는 결과에 나타나지 않습니다. 이 부서에는 관리자가 없으므로 fn_getsubtree가 빈 집합을 반환했습니다. OUTER APPLY를 사용하면 Gardening 부서도 결과 집합에 나타나며 deptmgrid 필드와 fn_getsubtree가 반환한 필드에는 NULL 값이 표시됩니다.