[SQL] 오라클 Oracle 계층형 쿼리 Connect By Prior 사용하기

최근 하나의 테이블에 특정 컬럼이 값이 서로 연결되어있는 데이터를 한번에 나열해야하는 쿼리를 만들어야 했다. 그래서 연결된 깊이 만큼 UNION ALL 쿼리를 이용해 상당히 긴 쿼리로 작성을 했었다. 그런데 직장 동료가 Connect By Prior를 사용하면 쉽게 만들 수 있다고 해서 쿼리를 다시 만들어보았다.

예를들어 냉장고에 들어가있는 모든 물건을 보고 싶다고 가정하자. 냉장고A에는 여러개의 반찬통과 반찬통 속에 반찬들 그리고 과일, 음료수 등등 다양한 음식과 식료품들이 들었다고 생각해보자. 그리고 우리는 냉장고A에 들어있는 반찬통부터 반찬통 안에 들어있는 반찬 정보까지 알고 싶다면 Connect By Prior 구문을 사용하면 계층형으로 데이터를 조회해올 수 있다.

1. 테이블 및 데이터 생성

테이블과 데이터를 생성하자. 테이블은 부모 아이템 컬럼, 자식 아이템 컬럼으로 구성되어있다.

냉장고A 안에 케찹은 parent_item = '냉장고A', child_item = '케찹'
냉장고A 안에 반찬통1은 parent_item = '냉장고A', child_item = '반찬통1'
반찬통1 안에 배추김치는 parent_item = '반찬통1', child_item = '배추김치'

이런 개념으로 데이터를 만들기위해서 테이블과 데이터를 생성한다.

DROP TABLE t1;

CREATE TABLE t1
  (
     parent_item VARCHAR2(100),
     child_item  VARCHAR2(100),
     CONSTRAINT t1 PRIMARY KEY (parent_item, child_item)
  );

INSERT INTO t1
SELECT '냉장고A', '반찬통1' FROM   dual
UNION ALL
SELECT '냉장고A', '반찬통2' FROM   dual
UNION ALL
SELECT '냉장고A', '반찬통3' FROM   dual
UNION ALL
SELECT '냉장고A', '반찬통4' FROM   dual
UNION ALL
SELECT '반찬통1', '배추김치' FROM   dual
UNION ALL
SELECT '반찬통2', '깍두기' FROM   dual
UNION ALL
SELECT '반찬통3', '잡채' FROM   dual
UNION ALL
SELECT '반찬통3', '동그랑땡' FROM   dual
UNION ALL
SELECT '반찬통4', '미니반찬통1' FROM   dual
UNION ALL
SELECT '반찬통4', '미니반찬통2' FROM   dual
UNION ALL
SELECT '미니반찬통1', '젓갈' FROM   dual
UNION ALL
SELECT '미니반찬통2', '된장' FROM   dual
UNION ALL
SELECT '냉장고A', '케찹' FROM   dual
UNION ALL
SELECT '냉장고A', '우유' FROM   dual
UNION ALL
SELECT '냉장고B', '치즈' FROM   dual;

COMMIT;

2. 테이블 조회

생성된 T1 테이블을 조회해보자

SELECT * FROM t1 ORDER BY parent_item;

아래와 같이 데이터가 조회된다. 위에 설명했던대로 냉장고 속 물품 정보가 있는걸 확인할 수 있다.

이제 우리가 원하는건 냉장고A에 들어있는 모든 물건들이다. 냉장고A에는 반찬통1도 있고 반찬통1에 있는 배추김치도 있고 우유도 있다.

3. 계층형 쿼리 작성

SELECT t1.parent_item,
       t1.child_item,
       LEVEL
FROM   t1
START WITH parent_item = '냉장고A'
CONNECT BY PRIOR child_item = parent_item;

위 쿼리 용어에 대한 설명이다.

START WITH : 트리 구조의 최상위 행을 지정하는 구문이다.
CONNECT BY : 부모, 자식의 관계를 지정하는 구문이다.
PRIOR : PRIOR에 지정된 컬럼이 맞은편 컬럼을 찾아간다.
CONNECT BY PRIOR : 자식컬럼 = 부모컬럼일 경우 부모 → 자식 순서로 전개한다.
CONNECT BY PRIOR : 부모컬럼 = 자식컬럼의 경우 자식 → 부모 순서로 전개한다.

4. 계층형 쿼리 조회 결과

조회된 데이터 중에서 child_item 컬럼 값을 보면 냉장고A 들어있는 모든 물품들이 조회된 걸 확인할 수 있다. LEVEL 컬럼은 냉장고A 기준으로 탐색한 계층 깊이라고 보면 될 것 같다.

냉장고A에 반찬통4가 있다.(1단계)
그리고 반찬통4에는 미니반찬통1, 2가 있다.(2단계)
그리고 미니반찬통1, 2에는 각각 젓갈과 된장이 있다.(3단계)

START WITH 조건으로 냉장고B는 조회되지 않은걸 확인할 수 있다.