[SQL]계층형 쿼리 및 셀프 조인 정리

계층형 쿼리 정의

 

• 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용한다. 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.
• 엔터티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다. 순환관계 데이터 모델의 예로는 조직도, 사원(관리자포함), 메뉴 등이 있다.

 

한국데이터베이스진흥원

 

계층형 쿼리 구조

SELECT 칼럼명... 
FROM 테이블명
WHERE 조건... 
START WITH 조건 
CONNECT BY PRIOR 조건;

• START WITH절은 계층 구조 전개의 시작 위치를 지정하는 구문이다. 즉, 루트(시작) 데이터를 지정하며 조건을 만족하는 모든 ROW들은 루트 노드가 된다. 
• CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 하며 START WITH과는 달리 서브쿼리가 올 수 없다.
• “PRIOR 자식 = 부모”형태를 사용하면 계층구조에서 부모 → 자식 방향으로 내려가는 순방향이고 “PRIOR 부모 = 자식”형태를 사용하면 계층구조에서 자식 → 부모 방향으로 올라가는 역방향으로 전개된다.

예시(순방향)

SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || EMPNO 사원, MGR 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

 

셀프 조인

 

• 셀프 조인(Self Join)이란 동일 테이블 사이의 조인을 말한다. 따라서 FROM절에 동일 테이블이 두 번 이상 나타난다.
• 동일 테이블 사이의 조인을 수행하면 테이블과 칼럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)를 사용해야 한다. 그리고 칼럼에도 모두 테이블 별칭을 사용해서 어느 테이블의 칼럼인지 식별해줘야 한다.
• 셀프 조인은 동일한 테이블(EMP)이지만, 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일하다.

 

셀프 조인 예시

 

[예제] 아우터 조인을 사용해서 관리자가 존재하지 않는 데이터까지 모두 결과에 표시 

*차상위관리자를 불러오기 위해서 한번에 할 수 없고, 조인을 한번 해야함