SQL :: 계층형 SQL

 

계층형질의
테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다
계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다

START WITH	- 어디부터 시작할 것인지를 정한다 ( 최상위 레코드 )
CONNECT BY PRIOR	- 계층 구조에서 각 행의 연결 관계를 설정한다
PRIOR	- 어디부터 시작할 것인지를 정해 준다.

 

START WITH  
계층 질의의 루트(부모행)로 사용될 행을 지정한다. 서브 쿼리를 사용할 수도 있다. START WITH 구문에서는 어떤 레코드를 최상위 레코드로 잡을지 지정한다.

 

CONNECT BY
계층적인 데이터를 쿼리하는 데 사용되는 Oracle SQL의 절이다. 이를 사용하여 부모-자식 관계를 가진 데이터를 조회할 수 있다.

ORDER SIBLINGS BY
CONNECT BY 절 다음에 사용되며, 같은 부모를 가지는 형제 노드들 간의 순서를 지정하는 데 사용된다.

 

--문제> 테이블에서 id는 각 부서 또는 직원의 고유 식별자,
--name은 부서 또는 직원의 이름, parent_id는 부모 부서 또는 상사의 id를 나타냄.

SELECT id, name
FROM organization
START WITH name = 'CEO' --CEO를 시작으로
CONNECT BY PRIOR id = parent_id --순방향 전개
ORDER SIBLINGS BY name; --형제 노드(동일 LEVEL) 사이 정렬 수행

--결과 출력
ID  | Name
------------
1   | CEO
2   | HR Manager
4   | HR Assistant
3   | IT Manager
6   | System Administrator
5   | Software Developer

---

 

 

계층형 질의에서 사용되는 가상 칼럼

 

LEVEL

루트 데이터면 1, 그 하위 데이터면 2, 하위 데이터가 있을 때마다 1씩 증가

 

CONNECT_BY_ISLEAF  
계층구조 쿼리에서 로우의 최하위 레벨(Leaf) 여부를 반환합니다. 최하위 레벨이면 1, 아니면 0

 

CONNECT_BY_ROOT
현재행 기준으로 자신의 최고 상위 ROOT를 출력함

 

--문제> employees 테이블. 각 직원은 자신의 직속 상사의 manager_id를 가지고 있음.

SELECT e1.employee_id,
       e1.employee_name,
       e2.employee_id AS manager_id,
       e2.employee_name AS manager_name
FROM employees e1 LEFT JOIN employees e2 --셀프 JOIN을 LEFT JOIN으로 하여 NULL까지 출력
ON e1.manager_id = e2.employee_id; --자기 자신의 칼럼과 연결지어서 계층형 데이터 출력

--결과 출력
employee_id | employee_name | manager_id | manager_name
------------------------------------------------------
1           | John          | NULL       | NULL
2           | Alice         | 1          | John
3           | Bob           | 1          | John
4           | Charlie       | 2          | Alice
5           | David         | 2          | Alice
6           | Eve           | 3          | Bob