Oracle Connect by 구문, Start with 구문 예제

다른 DB를 써보면 오라클이 얼마나 기능이 많은 지를 다시 한번 느끼게 될 때가 있습니다.

유용하게 사용해오던 Connect by 구문도 다른 DB에서 사용하려고 하면 없어서 고생하는 경우가 있네요.

 

connect by 구문은 계층적인 관계를 이용해서 데이터를 조회할 때 사용하는 구문입니다.

조회할 때 사용하기 때문에 당연히 select SQL 쿼리에서 사용합니다.

 

scott 유저에 있는 EMP 테이블 데이터를 가지고 체크해보겠습니다.

 

 

EMP 테이블에서 보면 EMPNO 와 MGR 값에 계층적인 관계(Hierarchical Relation)가 있는 것을 확인할 수 있습니다.

EMPNO 가 7839 번인 KING 은 'PRESIDENT' 회장님이기 때문에 MGR 가 없네요.

나머지 사원들은 모두 MGR 가 있습니다.

 

여기에서 7839(KING) 번을 레벨1 로 보고, 그 아래 단계에 있는, 즉 7839 번을 자신의 직속상위 매니저로 두고 있는

7566, 7698, 7782 번을 레벨2 로 볼 수 있겠습니다.

그 아래 단계는 레벨3 ...   이런 식으로 확인해 보고 싶겠죠? 다음과 같이 하면 됩니다.

 

SQL>
select a.*, level 
from EMP a
start with mgr is null
connect by prior empno = mgr
;

 

level 이라는 pseudo column 을 사용해서 조회하면 됩니다.

이때, level 값을 얻기 위해서

               - connect by 구문으로 관계를 표시해야 하고,

               - start with 구문으로 시작시점을 알려줘야

합니다.

 

connect by 구문을 쓸 때, prior 키워드를 사용하는데, empno 의 상위계층을 prior 로 지정하는 겁니다.

즉, " prior empno = mgr " 는 empno 의 상위계층이 mgr 임을 알려주는 것이죠.

일종의 self join 과도 같다고 할 수 있겠습니다.

 

start with mgr is null  의  의미는 "mgr 값이 null 인 데이터부터 시작해라" 라는 의미입니다.

 

조회해보면 다음과 같이 결과가 나옵니다.

 

 

이때, "connect by prior empno = mgr" 구문을 "connect by mgr = prior empno" 와 같이 바꿔도 결과는 같습니다.

prior 구문이 왼쪽에 있든 오른쪽에 있든 상관없습니다.

 

다음과 같이 하면 레벨2 인 데이터만 조회해 볼 수 있습니다.

 

 

 

▶ 참고) connect by 구문으로 테스트데이터 만들기