APPLY 사용

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 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 값이 표시됩니다.