[SQL 개념 정리 13] 계층형 질의와 셀프 조인

SQL에서는 데이터의 계층적 구조나 자기 참조 관계를 처리하기 위해 계층형 질의와 셀프 조인을 자주 사용합니다. 두 기법은 상위-하위 관계를 다루거나 같은 테이블 내에서 행 간의 관계를 정의하는 데 유용합니다.

---

계층형 질의(Hierarchical Query)

계층형 질의는 부모-자식 관계와 같은 계층 구조 데이터를 조회할 때 사용됩니다. 데이터의 상위-하위 관계를 표현하고 탐색하기 위해 주로 재귀적 CTE(MySQL)나 CONNECT BY(오라클)를 사용합니다.

---

계층형 질의의 구성 요소와 키워드

1. 부모-자식 관계:
   • 데이터의 상위 계층과 하위 계층이 명확히 정의된 구조.
   • 예: 직원-관리자 관계, 부서-상위 부서 관계.
2. 재귀적 CTE:
   • MySQL 8.0 이상에서 지원.
   • WITH RECURSIVE와 UNION ALL을 사용해 재귀적으로 데이터를 탐색.
3. CONNECT BY:
   • 오라클 전용 구문.
   • START WITH와 CONNECT BY PRIOR를 사용해 계층적 관계를 정의.

---

재귀적 CTE (MySQL 기준)

재귀적 CTE를 사용하면 계층적 데이터를 재귀적으로 탐색할 수 있습니다.

 

기본 구문

WITH RECURSIVE cte_name AS (
    -- Anchor member: 최상위 계층
    SELECT column1, column2, ..., 1 AS level
    FROM table_name
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive member: 하위 계층 탐색
    SELECT t.column1, t.column2, ..., cte.level + 1
    FROM table_name t
    JOIN cte_name cte ON t.parent_id = cte.column_id
)
SELECT * FROM cte_name;

 

예시: 직원-관리자 계층 구조

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- 최고 관리자

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

• 설명:
  • 최상위 관리자(manager_id가 NULL)를 기준으로 하위 계층을 재귀적으로 탐색합니다.
  • level은 계층의 깊이를 나타냅니다.

---

CONNECT BY (오라클 기준)

오라클에서는 CONNECT BY 구문을 사용하여 계층 구조를 표현합니다.

 

기본 구문

SELECT column1, column2, LEVEL AS level
FROM table_name
START WITH parent_id IS NULL
CONNECT BY PRIOR column_id = parent_id;

 

예시: 직원-관리자 계층 구조

SELECT employee_id, name, LEVEL AS level
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

• 설명:
  • START WITH는 최상위 계층을 정의합니다.
  • CONNECT BY PRIOR는 부모-자식 관계를 정의하며, 하위 계층으로 내려갑니다.
  • LEVEL은 각 행의 계층 깊이를 나타냅니다.

---

셀프 조인 (Self Join)

셀프 조인은 같은 테이블을 스스로와 조인하여 테이블의 행 간 관계를 표현할 때 사용합니다. 주로 자기 참조 관계를 다룰 때 유용합니다.

---

셀프 조인의 특징

1. 테이블의 두 인스턴스 참조:
   • 동일한 테이블에 두 개의 별칭을 부여하여 서로 다른 관점에서 조회.
2. 자기 참조 관계:
   • 테이블 내에서 한 행이 다른 행을 참조하는 구조.
   • 예: 직원-관리자 관계, 제품-상위 제품 관계.

---

셀프 조인의 구문

기본 구문

SELECT a.column1, a.column2, b.column1 AS related_column
FROM table_name a
JOIN table_name b ON a.foreign_key = b.primary_key;

---

셀프 조인 예시

직원-관리자 관계 조회

SELECT e.employee_id AS employee_id, e.name AS employee_name,
       m.employee_id AS manager_id, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

• 설명:
  • employees 테이블을 두 개의 별칭(e와 m)으로 참조.
  • e.manager_id와 m.employee_id를 매칭하여 직원과 관리자를 연결.

제품-상위 제품 관계 조회

SELECT p.product_id AS product_id, p.product_name AS product_name,
       sp.product_id AS parent_product_id, sp.product_name AS parent_product_name
FROM products p
LEFT JOIN products sp ON p.parent_product_id = sp.product_id;

• 설명:
  • 제품 테이블에서 각 제품과 상위 제품 간의 관계를 조회.

---

계층형 질의와 셀프 조인의 차이점

 

특징	계층형 질의	셀프 조인
목적	상위-하위 계층 구조 탐색	테이블 내 행 간 관계 표현
사용 방식	재귀적 CTE (WITH RECURSIVE), CONNECT BY	동일 테이블을 두 번 조인
주요 키워드	WITH RECURSIVE, START WITH, CONNECT BY	JOIN, ON, 테이블 별칭
적용 사례	직원-관리자 계층, 부서 구조	직원-관리자, 제품-상위 제품 관계

 

계층형 질의는 트리 구조나 다단계 관계를 탐색할 때 사용되며, 재귀적 탐색이 필요한 경우 유용합니다. 반면, 셀프 조인은 단일 테이블에서 자기 참조 관계를 나타낼 때 적합하며, 부모-자식 관계와 같은 간단한 구조에 유용합니다.

---

계층형 질의와 셀프 조인은 SQL에서 데이터의 상위-하위 관계를 다루거나 자기 참조 관계를 처리할 때 매우 유용한 도구입니다. 두 기법의 차이점과 활용 방안을 잘 이해하면, 복잡한 계층 구조 데이터를 효과적으로 탐색하고 간단한 자기 참조 관계를 명확하게 표현할 수 있습니다.