[SQLD] SQL 활용 : 3절 계층형 질의와 셀프 조인

3절 계층형 질의와 셀프 조인

---

계층형 질의

계층형 (Hierarchical) 데이터를 조회하기 위해 사용

한 테이블에서 부모-자식 관계를 가지는 데이터를 재귀적으로 조회

 

계층형 데이터

트리(tree) 구조를 가지는 데이터로, 엔터티가 자기자신과 순환(recursive)관계를 가짐

ex) 직원과 상사의 관계

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,      -- 직원 ID
    name VARCHAR(50),            -- 직원 이름
    manager_id INT NULL          -- 상사의 ID (자기 자신을 가리킬 수 있음)
);

---

CONNECT BY

SELECT LEL, LPAD(' ', LEVEL * 2) || name,
	CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, SYS_CONNECT_BY_PATH(name, ' -> ')
	CONNECT_BY_ISCYCLE
FROM Employees
START WITH manager_id IS NULL  -- 루트 노드(CEO)부터 시작
CONNECT BY [NOCYCLE] PRIOR emp_id = manager_id;  -- 부모-자식 관계 설정

Oracle에서 계층형 데이터를 조회할 때 사용

트리 형태의 구조로 쿼리 수행 (루트 노드부터 하위 노드의 쿼리를 실행함)

START WITH

시작 조건 지정

CONNECT BY PRIOR

조인 조건 지정, NOCYCLE 옵션 사용시 순환구조 발생지점까지만 전개

 

SELECT 조건

• LEVEL : 검색 항목의 깊이, 최상위 계층의 레벨은 1
• LPAD : 계층형 조회 결과를 명확히 하기 위해 사용 (LEVEL 값을 이용하여 결과 데이터 정렬)
• CONNECT_BY_ROOT : 최상위 계층 값 표시
• CONNECT_BY_ISLEAF : 최하위 노드 여부 표시 (1이면 최하위)
• CONNECT_BY_ISCYCLE : 순환구조 발생지점 표시 (1이면 발생)
• LPAD : 가시성을 위해 계층구조 시각화
• SYS_CONNECT_BY_PATH : 계층 구조의 전개경로 표시

---

CTE (Common Table Expression)

WITH RecursiveCTE (emp_id, name, manager_id, LEVEL, path) AS (
    -- 최상위 노드 (CEO) 선택
    SELECT emp_id, name, manager_id, 1 AS LEVEL, 
           CAST(name AS VARCHAR(MAX)) AS path
    FROM Employee
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 호출
    SELECT E.emp_id, E.name, E.manager_id, C.LEVEL + 1, 
           C.path + ' -> ' + E.name
    FROM Employee E
    JOIN RecursiveCTE C ON E.manager_id = C.emp_id
)
SELECT * FROM RecursiveCTE;

.SQL Server 계층형 질의 방법으로, 재귀호출 사용

 

셀프 조인

SELECT E1.emp_id AS Employee_ID, E1.name AS Employee_Name,
       E2.emp_id AS Manager_ID, E2.name AS Manager_Name
FROM Employee E1
LEFT JOIN Employee E2 ON E1.manager_id = E2.emp_id;

같은 테이블을 두번 조인하여 부모-자식 관계를 표현하는 방법

한 테이블 내에서 두 칼럼이 연관 관계가 있는 경우

JOIN을 사용하며, 반드시 테이블 별칭이 필요

LEFT JOIN을 사용하여 최상위 노드는 NULL 처리

 

최종 수정일 : 2025/03/07

틀린 사항이 있다면 댓글로 알려주시면 감사하겠습니다.