[SQL/2과목] 계층형 질의와 셀프조인

슬슬 2과목의 끝이 보인다.. 이번 포스팅은 계층형 질의와 셀프조인에 대해 정리해보겠다.

---

계층형 질의

 

계층형 질의는 데이터간에 부모-자식 관계가 있는 트리 구조를 조회할 때 특화된 쿼리이다. 

셀프조인은 한단계 위만 보여주는데, 계층형 질의는 최상위 계층부터 최하위 계층까지 전체 구조를 한 번에 볼 수 있다.

 

❓왜 셀프조인은 한단계 위를 보여줄까

-> 내가 바보라그런지, 셀프조인은 같은 테이블끼리 조인하는건데 왜 윗단계지? 생각했는데 같은 테이블을 논리적으로 두개로, 다르게 보면서 쪼인하기때문에 한단계 확인이 가능하다!! 두개로 다르게 보면서 그 사이 관계를 연결해서 보기때문에 한단계 위를 확인할 수 있다!

 

얘 또한 벤더별로 구현 방식이 다르기때문에, 차이를 보자

---

ORACLE

계층형 질의를 매우 직관적으로 강력한 전용 구문을 쓴다.

• START WITH : 계층 구조의 탐색을 시작할 루트 데이터를 지정
• CONNECT BY PRIOR : 부모-자식 관계 정의. PRIOR 쪽이 부모
  • PRIOR 자식 = 부모 : 계층구조에서 부모 -> 자식방향으로 순방향 전개!
  • PRIOR 부모 = 자식 : 계층구조에서 자식 -> 부모 방향으로 역방향 전개!
• NOCYCLE : 사이클이 발생한 이후의 데이터는 전개하지 않음. (사이클이 발생하면 런타임 오류가 발생
• ORDER SIBLINGS BY : 형제 노드(동일레벨) 사이에서 정렬 수행
• WHERE : 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출.

오라클은 계층형 질의를 사용할 때 다음과 같은 가상컬럼 제공한다. 가상컬럼은 테이블에 물리적으로 저장되어 있지는 않지만, 쿼리 실행시 컬럼처럼 사용할 수 있는 값이다. 예를들어 은행에서 대기번호표마냥 나한테 가상으로 번호를 매기는 그런거!

• LEVEL : 루트데이터는 1, 그 하위데이터는 2 이렇게 부여되며 리프까지 1씩 증가한다
• CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프데이터면 1, 그렇지 않으면 0이다
• CONNECT_BT_ISCYCLE : CYCLE 옵션을 사용했을 때 사용할 수 있다. 전개과정에서 자식을 갖는데 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0이다

뭐라는지 잘 모르겠으므로 바로 예시로 봐보겠다. 아래와 같은 테이블과 쿼리문이 있다고 하자.

emp_id	emp_name	manager_id
100	김사장	NULL
101	박부장	100
102	정부장	100
201	이과장	101
202	최대리	101
301	강사원	202

SELECT
    LEVEL,
    emp_name,
    manager_id
FROM
    employees
START WITH
    manager_id IS NULL
CONNECT BY
    PRIOR emp_id = manager_id;​

우선 manager_id가 널인애부터 시작한다돼있다. 그럼 김사장 데이터가 최상위이다. 그럼 김사장은 LEVEL이 1일 것이다. 

CONNECT BY를 보면, PROPR emp_id = manager_id니까 부모인 emp_id가 현재행의 manager_od와 같은 행을 찾으라는 것이다. 그럼 이제 김사장을 기준으로 emp_id가 100인 박부장, 정부장이 자식으로 찾아진다. 그럼 얘네는 LEVEL 2이다. 그럼 이제 박부장을 기준으로, 또 자식을 찾으면 이과장, 최대리가 나온다 이걸 반복반복....
이 과정을 통해 나온 행에 대해 SELECT하면 된다. 그럼 아래와 같은 테이블이 만들어질것이다.

LEVEL	EMP_NAME	MANAGER_ID
1	김사장	NULL
2	박부장	100
3	이과장	101
3	최대리	101
4	강사원	202
2	정부장	100

 

이건 부모 -> 자식방향이므로 순방향이다! 이제 자식-> 부모방향으로 가버리면 역방향 인 것이다. 

---

 

MSSQL

MSSQL에는 CONNECT BY 구문이 없으며 대신 WITH 절을 이용한 재귀 CTE 방식으로 구조를 조회한다.

• WITH 가상테이블명 as (..) : 가상테이이블을 설정!
• 앵커멤버 : 재귀의 시작점이 되는 최상의 데이터 조회 ( UNION ALL 윗 쿼리, START WITH 랑 유사)
• 재귀 멤버 : 앵커 멤버의 결과와 원본테이블을 조인하여 하위 계층을 반복적으로 찾음 ( CONNECT BY 랑 유사)

WITH OrgChart AS (
    -- 1. 앵커 멤버 (시작점 정의)
    SELECT
        emp_id,
        emp_name,
        manager_id,
        1 AS Level -- 계층 깊이를 1로 초기화
    FROM
        employees
    WHERE
        manager_id IS NULL -- Oracle의 START WITH와 동일

    UNION ALL

    -- 2. 재귀 멤버 (관계 정의 및 반복)
    SELECT
        e.emp_id,
        e.emp_name,
        e.manager_id,
        o.Level + 1 -- 부모 레벨에 1을 더해 자식 레벨 계산
    FROM
        employees e
    INNER JOIN
        OrgChart o ON e.manager_id = o.emp_id -- Oracle의 CONNECT BY와 동일
)
-- 3. 최종 결과 조회
SELECT Level, emp_name, manager_id FROM OrgChart;

 

보면 UNION ALL 위, 앵커멤버에서 계층 깊이를 1로 정해두고, 최상위를 찾는다.

그리고 아래엔, 조건을 걸어서 쪼인으로 찾는다. 그리고 select으로 직접 LEVEL에 1을 더해서 자식레벨을 계산한다.

이러면 아까 위의 테이블과 같은 결과가 나온다~

---

말로 설명하기 힘들다.. 지쳤다 암튼 끝