CONNECT BY 사용하기

CONNECT BY 란 계층형 쿼리를 조회하기 위한 쿼리이다.

다음과 같이 회사 조직도가 있다고 해보자. 조직에는 각 계층이 있다. 

https://grandma-coding.tistory.com/entry/Oracle-%EA%B3%84%EC%B8%B5%ED%98%95-%EC%BF%BC%EB%A6%AC-%EC%89%AC%EC%9A%B4-%EC%A0%95%EB%A6%ACSTART-WITH-CONNECT-BY

각 계층을 A레벨, B레벨 ,C 레벨이라 하겠다. 

각 레벨들의 정보들을 기록한 테이블이 다음과 같다고 해보자.

https://grandma-coding.tistory.com/entry/Oracle-%EA%B3%84%EC%B8%B5%ED%98%95-%EC%BF%BC%EB%A6%AC-%EC%89%AC%EC%9A%B4-%EC%A0%95%EB%A6%ACSTART-WITH-CONNECT-BY

각 row 에는 상위 부서 번호와 해당 부서의 번호가 있고, 부서번호가 만약에 시퀸스값으로 insert 가 된다고 할 때

만약에 조회를 할 때 A레벨부터 B레벨, C레벨 순으로 나오게끔 하고 싶다면 어떻게 해야될까? 

단순히 order by 를 하면 레벨별로 나온다고 무조건 장담할 수 없다. 이럴때 사용하는게 계층형 쿼리이다. 

---

1. 계층형 쿼리

오라클에서 계층형 쿼리의 예약어는 START WITH 이다.

• START WITH 

- 계층의 루트노드(처음행)로 사용될  행을 지정한다.

- 서브쿼리를 사용할 수 있다.

위의 표를 예로 든다면 최상위 행은 '회사' 가 될것이며, 상위 부서번호는 NULL 이 될것이다.

SELECT * FROM EMP START WITH 부서번호 IS NULL;

• CONNCT BY [PRIOR]

- 연결관계를 정의한다. 

- PIROR 연산자로 계층구조를 표현 할 수 있다. PRIOR 은 직전 상위 노드의 값을 반환한다.

- 서브쿼리는 사용할 수 없다. 

START WITH 으로 최상위 행을 정의했다면, 이제 하위레벨의 행을 어떻게 표현할지 정의해야 한다. 

B레벨은 2번째 레벨이고 첫번째 레벨인 A레벨의 부서번호를 상위부서번호로 가지고 있다.

CONNECT BY PRIOR 부서번호 = 상위부서번호

 

위의 뜻은 직전 노드의 행의 부서번호가 현재 검색을 위한 행의 상위부서일때, 즉 2레벨의 상위부서번호가 1레벨의 상위부서인 row 에 대해서 검색을 하겠다는 의미이다. 1레벨 부터 순차적으로 탐색할때 사용하며 반대 방향으로 탐색을 원할경우다음과 같이 쓸 수 있다.

CONNECT BY PRIOR 상위부서번호 = 부서번호

• LEVEL

- 계층형 쿼리에서 수행결과의 레벨을 표현하는 의사칼럼이다.

SELECT	부서번호, LEVEL, LPAD(' ' ,4*(LEVEL-1)) || 부서이름 AS CONNECT BY 
FROM	EMP	
START WITH 상위번호 IS NULL
CONNECY BY PRIOR 부서번호 = 상위부서번호
[ORDER BY 부서번호]

이렇게 쿼리를 수행하면 다음과 같이 조회할 수 있다.

 

---

참고

오라클 뿐 아니라 다른 DB에서도 이런 트리형의 계층구조를 조회할 수 있는 함수나 예약어가 있다. 그렇지 않다면 재귀쿼리를 사용해서 표현도 가능하다. MSSQL과 같은 경우에는 재귀쿼리로 표현해야 한다. 표현한다면 다음과 같다.

WITH TREE_QUERY AS(
	SELECT	부서번호, 상위부서, 부서이름
	FROM	EMP where 상위부서 IS NULL
	UNION ALL
	SELECT	a.부서번호, a.상위부서, a.부서이름
	FROM	EMP a, TREE_QUERY b
	WHERE	a.상위부서 = b.부서번호
)
SELECT	부서번호, 상위부서, 부서이름
FROM	TREE_QUERY

 

 

 

 

참고자료

재귀쿼리

https://nive.tistory.com/149

계층형 쿼리 쉬운 정리

https://grandma-coding.tistory.com/entry/Oracle-%EA%B3%84%EC%B8%B5%ED%98%95-%EC%BF%BC%EB%A6%AC-%EC%89%AC%EC%9A%B4-%EC%A0%95%EB%A6%ACSTART-WITH-CONNECT-BY