ORACLE CTE(Common Table Expression)와 계층형 쿼리(START WITH, CONNECT BY) 실전 예제

ORACLE CTE(Common Table Expression)와 계층형 쿼리(START WITH, CONNECT BY) 실전 예제

Oracle에서 CTE(Common Table Expression)와 계층형 쿼리(START WITH, CONNECT BY)는 복잡한 쿼리를 작성할 때 매우 유용한 기능입니다. CTE는 일시적인 결과 집합을 정의하여 쿼리를 간단하고 효율적으로 작성할 수 있도록 도와주며, 계층형 쿼리는 부모-자식 관계를 표현하여 트리 구조 데이터를 처리할 때 유용합니다. 이 글에서는 CTE와 계층형 쿼리의 개념과 실전 예제를 다루겠습니다.

1. CTE(Common Table Expression)란?

CTE는 쿼리 내에서 일시적인 결과 집합을 정의하는 방법으로, WITH 키워드를 사용하여 선언합니다. CTE는 주로 복잡한 쿼리에서 중복된 코드를 줄이고, 가독성을 높이는 데 유용합니다. CTE는 여러 번 참조할 수 있어 쿼리 성능을 최적화할 수 있습니다.

1.1. CTE 기본 문법

    WITH cte_name AS (
        SELECT column1, column2
        FROM table_name
        WHERE condition
    )
    SELECT *
    FROM cte_name;
    

CTE는 WITH 키워드 뒤에 cte_name을 지정하고, 해당 CTE에서 사용할 SQL 쿼리를 정의합니다. 이후 본 쿼리에서 CTE를 참조할 수 있습니다.

1.2. CTE 실전 예제

다음 예제는 직원 테이블에서 각 부서의 평균 급여를 계산하는 CTE 예제입니다.

    WITH avg_salary AS (
        SELECT department_id, AVG(salary) AS avg_salary
        FROM employees
        GROUP BY department_id
    )
    SELECT e.employee_id, e.employee_name, e.department_id, a.avg_salary
    FROM employees e
    JOIN avg_salary a ON e.department_id = a.department_id
    WHERE e.salary > a.avg_salary;
    

위 예제에서는 avg_salary라는 CTE를 사용하여 각 부서의 평균 급여를 구하고, 그 평균 급여보다 높은 급여를 받는 직원들을 조회합니다. CTE를 사용함으로써 쿼리를 더 깔끔하고 이해하기 쉽게 작성할 수 있습니다.

2. 계층형 쿼리(START WITH, CONNECT BY)란?

계층형 쿼리는 부모-자식 관계를 가진 데이터를 처리할 때 사용됩니다. Oracle에서는 START WITH와 CONNECT BY 구문을 사용하여 계층형 데이터를 처리할 수 있습니다. 이를 통해 트리 구조와 같은 데이터 관계를 표현할 수 있습니다.

2.1. START WITH와 CONNECT BY 기본 문법

    SELECT column1, column2
    FROM table_name
    START WITH condition
    CONNECT BY PRIOR column1 = column2;
    

START WITH는 트리 구조에서 루트 노드를 지정하는 데 사용되며, CONNECT BY는 부모-자식 관계를 정의하여 계층을 탐색합니다. PRIOR 키워드는 부모 노드를 참조할 때 사용됩니다.

2.2. 계층형 쿼리 실전 예제

다음 예제는 조직 구조 테이블을 사용하여 부서 간 상하 관계를 조회하는 예제입니다. 각 부서에는 상위 부서가 있을 수 있으며, START WITH와 CONNECT BY를 사용하여 계층을 탐색합니다.

    SELECT employee_name, manager_id
    FROM employees
    START WITH manager_id IS NULL
    CONNECT BY PRIOR employee_id = manager_id;
    

위 예제에서는 manager_id가 NULL인 직원(즉, 최고 관리자)을 루트로 시작하여, PRIOR를 사용해 상위 부서와 하위 부서 간의 관계를 나타냅니다. 이 쿼리는 조직 내 모든 직급의 상하 관계를 트리 구조로 반환합니다.

3. CTE와 계층형 쿼리의 차이점

CTE는 주로 쿼리 내에서 일시적인 결과 집합을 정의하여 쿼리의 가독성을 높이고, 중복된 계산을 피할 때 사용됩니다. 반면, 계층형 쿼리는 부모-자식 관계를 가진 데이터(예: 조직 구조, 트리 구조 등)를 처리할 때 유용합니다. CTE는 단순히 임시 결과를 저장하고 이를 사용하여 쿼리 작업을 수행하는 데 비해, 계층형 쿼리는 트리 구조의 탐색을 위한 기능입니다.

4. CTE와 계층형 쿼리 활용 사례

4.1. CTE 활용 사례

CTE는 복잡한 SQL 쿼리에서 중간 결과를 저장하고 이를 재사용하여 쿼리 구조를 단순화하는 데 유용합니다. 예를 들어, 여러 테이블을 조인하거나, 복잡한 집합 연산을 수행할 때 CTE를 사용하면 쿼리가 더 직관적이고 유지보수하기 쉬워집니다.

4.2. 계층형 쿼리 활용 사례

계층형 쿼리는 트리 구조 데이터를 다룰 때 강력한 기능을 제공합니다. 예를 들어, 조직 내 상사-부하 관계나 제품 카테고리 트리와 같은 데이터를 조회할 때 유용합니다. START WITH와 CONNECT BY를 사용하여 복잡한 트리 구조를 간단하게 쿼리할 수 있습니다.

5. 결론

Oracle에서 CTE와 계층형 쿼리는 각각 복잡한 쿼리 작성 시 가독성을 높이고, 계층적 데이터를 효율적으로 처리할 수 있는 강력한 도구입니다. CTE를 사용하면 쿼리 내에서 임시 결과 집합을 정의하고, 계층형 쿼리를 사용하면 트리 구조 데이터를 쉽게 다룰 수 있습니다. 각 기능을 적절히 활용하면 데이터 처리 및 쿼리 작성에 큰 도움이 될 것입니다.