계층형 질의와 셀프 조인

계층형 데이터는 한 테이블 안에서 행이 같은 테이블의 다른 행을 부모로 가리키는 구조입니다. 사원 테이블의 MGR_ID가 상사의 EMP_ID를 가리키면 사장 → 매니저 → 사원으로 이어지는 트리가 됩니다. 이런 테이블을 자기 참조(순환 관계) 테이블이라고 합니다.

맨 위 부모가 없는 행을 루트, 자식이 없는 행을 리프, 같은 부모를 둔 행끼리를 형제라고 합니다. 계층형 질의는 이 트리를 여러 단계로 펼치고, 셀프 조인은 같은 테이블을 두 번 써서 바로 위 한 단계(사원 ↔ 상사)를 붙입니다.

Oracle 계층형 질의는 START WITH로 루트 행을 정하고, CONNECT BY로 부모·자식을 잇는 규칙을 정합니다. PRIOR가 붙은 쪽이 이전(부모) 행입니다. CONNECT BY PRIOR EMP_ID = MGR_ID는 '부모의 EMP_ID = 자식의 MGR_ID'라서 위에서 아래로 가는 순방향이고, CONNECT BY PRIOR MGR_ID = EMP_ID(= EMP_ID = PRIOR MGR_ID)는 아래에서 위로 가는 역방향입니다. CONNECT BY에 PRIOR가 없으면 부모·자식 관계가 정해지지 않아 트리가 펼쳐지지 않습니다.

결과는 깊이 우선으로 나옵니다. 매니저A 밑의 사원이 먼저 나온 뒤에 매니저B가 나옵니다. LEVEL은 루트가 항상 1이고 한 단계 내려갈 때마다 1씩 커지며, 시작 값을 바꿀 수 없습니다. 리프라고 LEVEL이 1인 것도 아닙니다. 참고로 SELECT COUNT(*) FROM DUAL CONNECT BY LEVEL <= 3처럼 쓰면 1~3단계가 만들어져 3행이 됩니다.

계층형 질의에서만 쓰는 의사 열·함수가 있습니다. CONNECT_BY_ISLEAF는 리프면 1, CONNECT_BY_ROOT 열은 그 행이 속한 루트의 값, SYS_CONNECT_BY_PATH(열, '/')는 루트부터 현재 행까지의 경로입니다. 데이터에 순환(A의 상사가 B, B의 상사가 A)이 있으면 CONNECT BY NOCYCLE로 무한 전개를 막고, CONNECT_BY_ISCYCLE로 순환을 일으키는 행을 1로 표시합니다.

조건을 어디에 두느냐가 자주 나옵니다. WHERE 조건은 트리를 모두 펼친 뒤 결과 행만 거르므로 그 행만 빠지고 자식은 남습니다. CONNECT BY에 AND로 붙인 조건은 펼치는 단계에서 적용되어 그 행과 아래 가지 전체가 빠집니다(가지치기). 형제끼리만 정렬하려면 ORDER SIBLINGS BY를 씁니다. 일반 ORDER BY는 트리 순서를 깨뜨립니다.

셀프 조인은 같은 테이블을 별칭을 달리해 두 번 쓰는 조인입니다. 사원(E)의 상사 이름을 붙이려면 '사원의 MGR_ID = 상사의 EMP_ID', 즉 E.MGR_ID = M.EMP_ID로 잇습니다. 반대로 쓰면(E.EMP_ID = M.MGR_ID) 내 부하를 찾는 조건이 됩니다.

일반 조인이면 상사가 없는 사장 행은 빠지고, 사장까지 보려면 LEFT OUTER JOIN을 씁니다. 셀프 조인은 바로 위 한 단계를 붙이는 데 알맞고, 여러 단계를 따라 내려가는 데는 계층형 질의가 알맞습니다.

참고: 표준 SQL의 재귀 쿼리는 WITH 이름 (열...) AS (루트 SELECT UNION ALL 자식 SELECT) 형태입니다. Oracle은 11gR2부터 지원하며 PostgreSQL·MySQL과 달리 RECURSIVE 키워드를 쓰지 않습니다.