계층형 쿼리란?

• 한 테이블에 저장되어 있는 데이터들이 상하(부모, 자식) 관계를 가질 때, 이 구조를 이용해서 테이블을 조회하는 SQL이다.

• 계층형 쿼리를 이용해서 트리 구조의 형태로 결과를 조회할 수 있다.

• 일반적으로 조직도나 카테고리, 메뉴 등의 정보가 트리구조를 가지고 있다.

형식

SELECT [LEVEL,] 컬럼1, 컬럼2
FROM 테이블명
[WHERE 조건식]
START WITH 조건식
CONNECT BY PRIOR 조건식
-- []안의 SQL은 생략 가능

• LEVEL은 조회되는 각 행에 대한 레벨을 출력하는 의사컬럼이다. 예를 들어, 최상위행은1, 그 하위행은 2, 그 하위의 하위행은 3을 출력한다.

• START WITH절은 계층 검색의 시작점을 지정한다.

• CONNECT BY절은 계층 검색의 방향을 지정한다.

  • 상위에서 하위로 검색 : CONNECT BY PRIOR 부모컬럼 = 자식컬럼
  • 하위에서 상위로 검색 : CONNECT BY PRIOR 자식컬럼 = 부모컬럼

상위행에서 하위행으로 조회

-- 100번 직원 및 부하직원을 조회하기
-- MANAGER_ID가 EMPLOYEE_ID를 참조한다.
SELECT LEVEL , EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 100
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;

100번 직원부터 검색을 시작하고 LEVEL컬럼의 숫자가 해당 레코드가 어느 계층에 있는지 나타낸다. MANAGER_ID는 EMPLOYEE_ID를 참조하므로 EMPLOYEE_ID가 부모컬럼, MANAGER_ID가 자식컬럼이다. 상위에서 하위로 검색한다.

하위행에서 상위행으로 조회

-- 206번직원의 상사을 조회하기
SELECT LEVEL, EMPLOYEE_ID, FIRST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 206
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
ORDER BY LEVEL DESC;