sqld

self join과 계층형질의

자격증원톱 2026. 2. 26. 08:42
반응형

self join과 계층형질의

반응형

 

1) 셀프 조인(Self-Join) 요약

개념

  • 같은 테이블을 2번(또는 그 이상) 불러와서 조인하는 방식입니다.
  • 이유: 한 테이블 안에서 “나 ↔ 다른 행”의 관계를 비교해야 할 때가 많기 때문입니다.
    • 대표 예: 사원(EMP) 테이블에서 ‘내 관리자 이름’ 찾기
    • EMP 안에 EMPNO(사원번호)와 MGR(관리자 사원번호)가 같이 들어있음

예시: “사원과 그 사원의 관리자 이름 같이 출력”

  • 직원 행(e1)과 관리자 행(e2)을 같은 EMP에서 매칭
  • 조건: e1.MGR = e2.EMPNO
SELECT
e1.empno AS employee_id,
e1.ename AS employee_name,
e2.empno AS manager_id,
e2.ename AS manager_name
FROM emp e1
LEFT JOIN emp e2
ON e1.mgr = e2.empno;
 
쉽게 말하면
  • e1은 “직원”
  • e2는 “관리자”
  • 직원의 MGR(관리자 번호)로 관리자 행을 찾아서 이름을 붙이는 겁니다.

예) BLAKE의 MGR이 7839라면, EMPNO=7839인 사람(KING)이 BLAKE의 관리자입니다.

2) 계층형 질의(Hierarchical Query) 요약 (Oracle)

계층형 데이터란?

  • 트리 구조 데이터: 부모-자식 관계가 반복되는 구조
    • 회사 조직도(CEO → 팀장 → 팀원)
    • 제품 카테고리(대분류 → 중분류 → 소분류)
    • 사원-관리자 관계(관리자 아래 여러 직원)

셀프 조인으로도 여러 단계를 따라갈 수는 있는데,
계층이 깊어지면 쿼리가 복잡해져서 오라클은 전용 문법을 제공합니다.

3) 오라클 계층형 질의 기본 문법(이미지 핵심)

SELECT ...
FROM 테이블
START WITH 시작조건
CONNECT BY [PRIOR] 연결조건
ORDER SIBLINGS BY 정렬컬럼;
 

각 요소 의미

  • START WITH: 트리의 시작점(루트) 지정
    • 예: mgr IS NULL (최상위 관리자)
  • CONNECT BY PRIOR: 부모-자식 연결 규칙
    • 예: PRIOR empno = mgr
  • LEVEL: 깊이(루트=1, 그 아래=2, …)
  • ORDER SIBLINGS BY: 같은 부모를 가진 “형제 노드끼리” 정렬

4) 계층 조회 예시 1: “조직도 전체 출력(위에서 아래로)”

SELECT empno, ename, mgr, LEVEL
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 

해석

  • mgr IS NULL인 사람이 루트(최상위: KING 같은)
  • 그 다음 행들은 “부모 empno = 자식 mgr”로 연결됨
    • 즉, “내 empno가 누군가의 mgr로 들어가 있으면 그 사람이 내 부하”

5) SYS_CONNECT_BY_PATH: “루트부터 현재까지 경로 문자열”

이미지에 나온 PATH 컬럼이 이겁니다.

SELECT
empno, ename, mgr, LEVEL,
SYS_CONNECT_BY_PATH(ename, '/') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 
결과 예(느낌)
  • /KING
  • /KING/JONES
  • /KING/JONES/SCOTT
    이런 식으로 조직 경로를 한 눈에 보여줍니다.

6) CONNECT_BY_ROOT: “내 루트(최상위 관리자) 표시”

 
SELECT
CONNECT_BY_ROOT ename AS root_manager,
ename,
SYS_CONNECT_BY_PATH(ename, '/') AS path,
LEVEL
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 
 

의미

  • 각 행이 “최종적으로 누구 라인(루트)인지” 표시됩니다.
  • 조직에서 “이 사람은 CEO 누구 라인” 같은 걸 쉽게 확인 가능.

7) ORDER SIBLINGS BY: “형제끼리만 정렬”

계층 출력은 기본적으로 LEVEL 순으로 내려가며 출력되는데,
같은 부모 아래 형제들(같은 팀원들)을 이름순/급여순으로 정렬하고 싶을 때 씁니다.

ORDER SIBLINGS BY ename;
 

주의: 그냥 ORDER BY ename을 쓰면 트리 구조가 깨질 수 있습니다.
“형제끼리만” 정렬해야 트리 모양이 유지됩니다.

8) CONNECT_BY_ISLEAF: “리프(말단) 노드인지”

  • 자식이 없는 노드(부하가 없는 사원)면 리프(leaf)
  • 오라클은 이를 확인하는 값을 제공합니다.
SELECT ename, LEVEL, CONNECT_BY_ISLEAF AS isleaf
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
 
  • isleaf = 1 : 말단(더 내려갈 자식 없음)
  • isleaf = 0 : 자식이 있음

9) 순방향/역방향 전개(PRIOR 위치가 핵심)

이미지의 가장 중요한 포인트 중 하나가 이거예요.

(1) 순방향(부모 → 자식, 위에서 아래)

CONNECT BY PRIOR empno = mgr
 
(2) 역방향(자식 → 부모, 아래에서 위로)
CONNECT BY empno = PRIOR mgr
예: 특정 사원(ADAMS)부터 위로 “관리자 체인” 타고 올라가기
SELECT ename, mgr, LEVEL
FROM emp
START WITH empno = 7876
CONNECT BY empno = PRIOR mgr;
 
 

전체 한 줄 요약

  • 셀프 조인: 한 테이블을 별칭 두 개로 불러서 “직원 ↔ 관리자” 같은 관계를 한 번에 붙여 출력.
  • 계층형 질의(오라클): START WITH + CONNECT BY PRIOR로 트리 구조를 쉽게 조회하고, LEVEL / PATH / ROOT / ISLEAF / SIBLINGS 정렬로 조직도를 분석.

 

반응형