MySQL 셀프 조인으로 상위 부서 트리 구하기

데이터베이스에서 부서 정보를 담은 테이블은 보통 부서와 상위 부서로 구성되어 있습니다. 그리고 상위 부서는 또 다른 상위 부서가 있어서 트리로 이어지는 구조입니다. 이때 전체 계층 구조를 펼치기 위해서는 동일한 테이블을 여러 번 참조하고 각 부서의 상위 부서와 연결하는 방식을 쓸 수 있습니다. 이걸 셀프 조인이라고 합니다.

예를 들어 부서코드, 부서명, 부모 부서코드가 있는 테이블로 부서 계층 구조를 확인해보겠습니다.

아래와 같이 4단계로 이어지는 계층 구조에서 똑같은 department 테이블을 각각 다른 명칭을 붙이면서 (lv0, lv1, lv2, lv3) 자기 자신을 조인합니다. 이때 자기 자신의 parent_id와 dept_id가 같다는 조건으로 조인하면 계층 구조를 확인할 수 있습니다.

SELECT
    lv3.dept_id,
    lv0.dept_name AS lv0_root,
    lv1.dept_name AS lv1,
    lv2.dept_name AS lv2,
    lv3.dept_name AS lv3
FROM departments AS lv3
LEFT JOIN departments AS lv2 ON lv3.parent_id = lv2.dept_id
LEFT JOIN departments AS lv1 ON lv2.parent_id = lv1.dept_id
LEFT JOIN departments AS lv0 ON lv1.parent_id = lv0.dept_id
ORDER BY lv0_root, lv1, lv2, lv3;