MSSQL LEVEL쿼리 계층형 쿼리 이해하기

MSSQL LEVEL쿼리  계층형 쿼리 이해하기

MSSQL에서는 Oracle의 CONNECT BY와 직접적으로 동일한 구문은 없지만, **재귀 CTE (Recursive Common Table Expression)**를 사용하여 훨씬 더 강력하고 유연하게 계층형 데이터를 다룰 수 있습니다.

실무에서 바로 적용할 수 있도록 가장 쉬운 개념부터 다양한 활용법까지 단계별로 설명해 드리겠습니다.

핵심개념: 재귀 CTE의 두가지 구성요소

재귀 CTE를 'level 쿼리'를 만드는 도구라고 생각하시면 쉽습니다. 이것은 두 부분으로 나뉩니다.

1. 앵커 멤버 (Anchor Member): 시작점입니다. 계층 구조의 최상위(ROOT) 또는 우리가 탐색을 시작하고 싶은 지점을 지정합니다. 이 부분이 LEVEL = 1이 됩니다.
2. 재귀 멤버 (Recursive Member): 확장 규칙입니다. 앵커 멤버에서 찾은 결과를 바탕으로, 그 다음 자식(하위) 노드를 어떻게 찾아 나갈지 정의하는 부분입니다. 이 부분이 반복되면서 LEVEL이 2, 3, 4...로 증가합니다.

이 두 부분을 UNION ALL로 연결하면 재귀 CTE가 완성됩니다.

---

예제 1: 가장 기본적인 조직도 계층쿼리 (LEVEL 이해하기)

가장 흔한 예제인 직원-관리자 관계를 통해 LEVEL을 이해해 보겠습니다.

1. 샘플 테이블 및 데이터 생성

codeSQL

-- 샘플 테이블 생성
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    EmpName NVARCHAR(50),
    ManagerID INT -- 관리자 ID, 최상위 관리자는 NULL
);

-- 샘플 데이터 삽입
INSERT INTO Employees (EmpID, EmpName, ManagerID) VALUES
(1, 'A사장', NULL),
(2, 'B상무', 1),
(3, 'C전무', 1),
(4, 'D팀장', 2),
(5, 'E팀장', 2),
(6, 'F과장', 3),
(7, 'G사원', 4),
(8, 'H사원', 4),
(9, 'I사원', 6);

2. 재귀 CTE를 사용한 계층형 쿼리 작성

codeSQL

-- 재귀 CTE를 사용하여 조직도 계층 조회
WITH EmployeeHierarchy AS (
    -- 1. 앵커 멤버 (Anchor Member): 시작점 정의
    -- 최상위 관리자(ManagerID가 NULL인 사람)를 찾고, LEVEL을 1로 설정합니다.
    SELECT
        EmpID,
        EmpName,
        ManagerID,
        1 AS Level -- 계층의 깊이를 나타내는 Level 컬럼
    FROM
        Employees
    WHERE
        ManagerID IS NULL -- 여기가 시작점! 'A사장'

    UNION ALL

    -- 2. 재귀 멤버 (Recursive Member): 확장 규칙 정의
    -- 바로 위 단계에서 찾은 직원(eh)을 관리자로 삼는 직원(e)을 찾습니다.
    SELECT
        e.EmpID,
        e.EmpName,
        e.ManagerID,
        eh.Level + 1 -- 부모 Level에 1을 더해 자식의 Level을 계산
    FROM
        Employees AS e
    INNER JOIN
        EmployeeHierarchy AS eh ON e.ManagerID = eh.EmpID -- 핵심 조인 조건!
)
-- 3. 최종 결과 조회
SELECT
    REPLICATE('    ', Level - 1) + EmpName AS OrgChart, -- Level에 따라 들여쓰기
    Level,
    EmpID,
    ManagerID
FROM
    EmployeeHierarchy
ORDER BY
    Level, EmpID;

쿼리 해설:

• WITH EmployeeHierarchy AS (...): EmployeeHierarchy라는 이름의 CTE를 정의합니다.
• 앵커 멤버 (첫 번째 SELECT):
  • WHERE ManagerID IS NULL: 조직도의 뿌리인 'A사장'을 찾습니다.
  • 1 AS Level: 시작점이므로 Level을 1로 지정합니다.
• UNION ALL: 앵커 멤버와 재귀 멤버의 결과를 합칩니다.
• 재귀 멤버 (두 번째 SELECT):
  • INNER JOIN EmployeeHierarchy AS eh ON e.ManagerID = eh.EmpID: 이 부분이 마법입니다.
    1. 첫 번째 반복: EmployeeHierarchy에는 'A사장'(Level 1)이 들어있습니다. 이 사람을 관리자(eh.EmpID)로 하는 직원들('B상무', 'C전무')을 Employees 테이블에서 찾습니다.
    2. 찾은 직원들의 Level은 부모인 'A사장'의 Level(1) + 1 = 2가 됩니다.
    3. 두 번째 반복: 이제 EmployeeHierarchy에는 'B상무', 'C전무'(Level 2)가 추가되었습니다. 이들을 관리자로 하는 직원들('D팀장', 'E팀장', 'F과장')을 찾습니다. 이들의 Level은 3이 됩니다.
    4. 이 과정이 더 이상 하위 직원을 찾을 수 없을 때까지 반복됩니다.
• 최종 SELECT: 완성된 EmployeeHierarchy 결과를 보기 좋게 출력합니다. REPLICATE 함수로 들여쓰기를 해서 구조를 시각적으로 표현했습니다.

결과:

OrgChart	Level	EmpID	ManagerID
A사장	1	1	NULL
B상무	2	2	1
C전무	2	3	1
D팀장	3	4	2
E팀장	3	5	2
F과장	3	6	3
G사원	4	7	4
H사원	4	8	4
I사원	4	9	6

---

실무 활용법: 다양한 응용 예제

기본적인 LEVEL 개념을 이해했다면, 이제 실무에서 자주 쓰이는 패턴들을 알아봅시다.

활용 1: 특정 노드 하위의 모든 계층 조회하기

'B상무' 산하의 모든 직원을 조회하고 싶다면, 앵커 멤버의 시작 조건만 바꾸면 됩니다.

codeSQL

WITH SubHierarchy AS (
    -- 앵커 멤버: 시작점을 'B상무'로 변경
    SELECT EmpID, EmpName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmpID = 2 -- 'B상무'의 EmpID

    UNION ALL

    -- 재귀 멤버: 로직은 동일
    SELECT e.EmpID, e.EmpName, e.ManagerID, sh.Level + 1
    FROM Employees AS e
    INNER JOIN SubHierarchy AS sh ON e.ManagerID = sh.EmpID
)
SELECT * FROM SubHierarchy;

활용 2: 계층 경로(Path) 표시하기

보고서 등에서 전체 경로를 한눈에 보고 싶을 때 유용합니다.

codeSQL

WITH EmployeeHierarchy AS (
    SELECT
        EmpID,
        EmpName,
        ManagerID,
        1 AS Level,
        CAST(EmpName AS VARCHAR(MAX)) AS Path -- 경로를 저장할 컬럼 (시작점)
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        e.EmpID,
        e.EmpName,
        e.ManagerID,
        eh.Level + 1,
        eh.Path + ' > ' + e.EmpName -- 기존 경로에 현재 직원을 추가
    FROM Employees AS e
    INNER JOIN EmployeeHierarchy AS eh ON e.ManagerID = eh.EmpID
)
SELECT Level, Path, EmpID FROM EmployeeHierarchy ORDER BY Path;

결과:

Level	Path	EmpID
1	A사장	1
2	A사장 > B상무	2
3	A사장 > B상무 > D팀장	4
4	A사장 > B상무 > D팀장 > G사원	7
4	A사장 > B상무 > D팀장 > H사원	8
...	...	...

활용 3: 숫자/날짜 시퀀스 생성 (달력 테이블 동적 생성)

재귀 CTE는 계층 구조뿐만 아니라 연속된 데이터를 만드는 데에도 매우 유용합니다.

예: 1부터 10까지의 숫자 생성

codeSQL

WITH NumberSequence AS (
    -- 앵커: 시작 숫자 1
    SELECT 1 AS MyNumber

    UNION ALL

    -- 재귀: 이전 숫자에 1을 더함
    SELECT MyNumber + 1
    FROM NumberSequence
    WHERE MyNumber < 10 -- 종료 조건!
)
SELECT * FROM NumberSequence;

예: 특정 기간의 모든 날짜 생성

codeSQL

DECLARE @StartDate DATE = '2023-12-01';
DECLARE @EndDate DATE = '2023-12-31';

WITH DateSequence AS (
    -- 앵커: 시작 날짜
    SELECT @StartDate AS MyDate

    UNION ALL

    -- 재귀: 이전 날짜에 1일을 더함
    SELECT DATEADD(DAY, 1, MyDate)
    FROM DateSequence
    WHERE MyDate < @EndDate -- 종료 조건!
)
SELECT * FROM DateSequence;

이런 방식으로 루프(LOOP) 없이도 원하는 데이터를 쉽게 생성할 수 있습니다.

핵심 요약 및 팁

1. 쉽게 생각하기: MSSQL의 'level 쿼리'는 "시작점(앵커) + 확장 규칙(재귀)" 이 두 가지만 기억하세요.
2. 앵커가 핵심: 쿼리의 목적에 따라 앵커 멤버의 WHERE 절을 잘 정의하는 것이 가장 중요합니다. 전체를 볼 것인가, 특정 부분부터 볼 것인가를 결정합니다.
3. 조인 조건: 재귀 멤버의 JOIN 조건(e.ManagerID = eh.EmpID)은 부모-자식 관계를 연결하는 핵심 고리입니다.
4. 종료 조건:
   • 계층 쿼리: 더 이상 연결할 자식 노드가 없으면 자동으로 종료됩니다.
   • 숫자/날짜 생성: WHERE 절에 명시적인 종료 조건을 반드시 넣어줘야 무한 루프에 빠지지 않습니다.
5. MAXRECURSION 옵션: 기본적으로 재귀는 100번까지만 허용됩니다. 100단계보다 깊은 계층이 필요하면 쿼리 끝에 OPTION (MAXRECURSION 0) (0은 무제한) 또는 OPTION (MAXRECURSION 500)처럼 한도를 지정해야 합니다.

이처럼 재귀 CTE는 단순히 LEVEL을 표현하는 것을 넘어, 경로 추적, 데이터 생성 등 다양한 실무 시나리오에서 매우 유용하게 사용될 수 있습니다. 위 예제들을 직접 실행해보시고, 앵커 멤버의 조건을 바꿔보면서 연습하시면 금방 익숙해지실 겁니다.