APPLY 사용
APPLY 연산자를 사용하면 쿼리의 외부 테이블 식에서 반환한 각 행에 대해 테이블 반환 함수를 호출할 수 있습니다. 테이블 반환 함수는 오른쪽 입력이 되고 외부 테이블 식은 왼쪽 입력이 됩니다. 오른쪽 입력은 왼쪽 입력의 각 행에 대해 평가된 후 생성된 행이 조합되어 최종 출력에 표시됩니다. APPLY 연산자를 통해 생성되는 열 목록은 왼쪽 입력의 열 목록 뒤에 오른쪽 입력에서 반환한 열 목록을 추가한 것입니다.
[!참고]
APPLY를 사용하려면 데이터베이스 호환성 수준이 적어도 90이어야 합니다.
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 D.deptid, D.deptname, D.deptmgrid
,ST.empid, ST.empname, ST.mgrid
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 값이 표시됩니다.