마농의 SQL 백문백답: 단순하고 쉽게 작성하는 SQL 노하우 [1회]

계층 구조 쿼리의 이해

단순하고 쉽게 작성하는 SQL 노하우를 공유하자!

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다. 앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

[질문 1] 계층 구조 테이블을 어떻게 조회해야 하나요

이는 단골 질문들 가운데 하나다. 물론 이 질문은 SQL 기본 문법으로는 절대 해결할 수 없다. 오라클의 경우엔 계층구조 전용 SQL 구문이 있다. 이 구문은 간단하면서도 매우 강력하다. 하지만 이 간단한 구문을 사용함에 있어서 개발자들이 많이들 어려워하고 있는 실정이다. 왜 어려울까 바로 구문에 대한 정확한 이해가 없이 COPY & PASTE 형태의 작업을 반복해 왔기 때문이다. 이번 시간에는 이 구문에 대한 기본 개념을 이해하고 이와 관련된 응용문제를 풀어보는 시간을 갖도록 하겠다.

1) 계층 구조 테이블
부모와 자식의 계층관계를 구조화한 테이블이다. 테이블의 구성은 매우 간단하다. 코드와 부모코드로 구성된다. 직장에서의 사원과 상사의 관계를 예로 들어 그림으로 표현해보자.



1번의 데이터 모델 관점과 2번의 트리구조 형태는 논리적인 관점에서 바라본 구조다. 3번의 테이블이 바로 우리가 사용해야할 RDB(관계형 DB)에서의 계층 구조다. 이 테이블을 이용해 원하는 결과를 얻어내는 SQL을 작성해야 하는 것이다.

2) 오라클의 계층구조 쿼리 기본 문법

SELECT 컬럼 리스트
, LEVEL AS 계층의 깊이
FROM 테이블
WHERE (조건)
START WITH (시작 위치 조건)
CONNECT BY (PRIOR 하위코드 = 상위코드)
ORDER SIBLINGS BY (정렬기준)
;

기본 사용법은 위와 같다. SELECT, FROM, WHERE 등은 SQL 기본 구문이니 설명은 생략하고, 여기서는 빨간색 계층 구문을 살펴보도록 하겠다.

1. START WITH: 계층구조의 시작 조건을 기술한다.
2. CONNECT BY: 계층구조의 전개 조건을 기술한다.
3. PRIOR: 컬럼 앞에 붙어 상위행의 컬럼임을 나타낸다.
4. LEVEL: 계층구조 전개의 깊이/단계를 표현
5. ORDER SIBLINGS BY: 같은 부모 아래 형제들끼리 정렬

오라클 기본 교육용 SCOTT 계정의 EMP 테이블로 실습해보면 다음과 같다.

SELECT empno, ename, mgr
, LEVEL lv
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;

사원(EMP) 테이블에서 사원번호(EMPNO)와 관리자사원번호(MGR) 두 개 항목이 계층구조를 이루고 있다. 여기서 MGR이 없는 사원은 바로, 계층구조에서 가장 높은 위치의 사원을 의미한다. 바로 “MGR이 없는 사원”조건을 계층의 시작조건으로 줄 수 있다.

START WITH mgr IS NULL

사원과 상사 사이의 관계는 EMPNO를 기준으로 보면 EMPNO는 MGR을 가지고 있다. 이 EMPNO가 갖고 있는 MGR은 상사의 EMPNO임을 의미한다. 바로 이 “MGR은 상사의 EMPNO 이다”라는 조건이 계층 전개 조건이다. “MGR은 상사의 EMPNO이다”조건을 그대로 쿼리로 옮기면 다음과 같다.

CONNECT BY PRIOR empno = mgr

PRIOR는 컬럼 앞에 붙어서 해당 항목이 상위 항목임을 나타내는 구문이다.

이 쿼리의 실행 결과를 살펴보자.



성공적인 결과가 나왔는가 계층 전개 순서대로 잘 정렬되어 나왔다. 하지만 잘 모르겠다. 원본 테이블을 그대로 조회한 것과 정렬순서만 다를 뿐 결과만 봐서는 큰 차이점을 느낄 수가 없다.

3) 오라클의 계층구조 쿼리 확장 구문

SELECT 컬럼리스트
, LEVEL AS 계층의깊이
, CONNECT_BY_ROOT (컬럼) AS 루트노드값
, CONNECT_BY_ISLEAF AS 최하위여부
, CONNECT_BY_ISCYCLE AS 순환관계발생여부
, SYS_CONNECT_BY_PATH(컬럼, '구분자') AS 계층구조전체경로
FROM 테이블
WHERE (조건)
START WITH (시작위치조건)
CONNECT BY NOCYCLE PRIOR 하위코드 = 상위코드
ORDER SIBLINGS BY (정렬기준)
;

기본 사용법에 추가로 학장 구문 사용법을 표시했다. 이번에는 파란색 표시 구문을 살펴보자.

1. CONNECT_BY_ROOT: 계층의 최상위 값을 표시
2. ONNECT_BY_ISLEAF: 계층의 최하위 여부를 표시
3. SYS_CONNECT_BY_PATH: 계층 전계 경로 표시
4. NOCYCLE: 순환구조 에러 방지용 구문, 순환구조 발생지점까지만 계층 전개
5. CONNECT_BY_ISCYCLE: NOCYCLE 사용 시 순환구조 발생지점을 표시

[질문 2] 계층구조임을 한눈에 알 수 있도록 해주는 표현 방법은 없을까요

앞서 살펴봤듯이 계층구조를 전개했다고 그 계층구조가 한눈에 들어오지는 않는다.

SELECT empno
, mgr
, LEVEL lv
, LPAD(' ', (LEVEL-1)*2, ' ') || ename AS ename
, SYS_CONNECT_BY_PATH(ename, '-') AS enames
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;

다음과 같은 방법으로 계층표현을 가시적으로 표현할 수 있다.

1. 들여쓰기 형태의 계층 표현: LEVEL에 LPAD 함수를 적용해 들여쓰기 하는 방법
2. 계층 경로를 모두 표시: SYS_CONNECT_BY_PATH를 이용해 계층 경로를 표시하는 방법

[질문 3] 계층 구조 전개 후 정렬을 하면 계층 구조가 흐트러지나요. 계층구조를 그대로 유지하면서 그 안에서 정렬은 어떻게 해여 하나요

- ORDER SIBLING BY 구문을 이용해야만 한다.
- 일반 ORDER BY 절을 사용하면 안 된다.

[질문 4] 계층 구조를 역으로 전개하려면 어떻게 하나요

PRIOR의 의미를 모르고 형식적으로 사용했기 때문에 해결방법을 모르는 것이다. PRIOR는 항목의 앞에 붙어 상위의 항목임을 나타낸다고 했다. 다음은 순방향 전개를 위한 조건절이다.

CONNECT BY PRIOR empno = mgr

그렇다면 역방향 전개를 위한 조건으로 바꾼다면

CONNECT BY empno = PRIOR mgr

정말로 간단하다. PRIOR의 위치만 바꿔주면 된다. 이 간단한 생각을 하지 못하는 이유는 무엇 때문일까 PRIOR를 하나의 독립적인 구문으로써 의미를 부여하지 못한 채 “CONNECT BY PRIOR”를 하나의 숙어처럼 떼어낼 수 없다고 생각하기 때문이다.

[질문 5] 상사의 이름을 함께 표시하고 싶어요.

SELECT empno
, ename
, mgr
, (SELECT ename FROM emp WHERE empno = m.mgr) AS mgr_name
FROM emp m
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;

스칼라 서브쿼리(SELECT 절에서 사용하는 서브쿼리)를 이용하여 메인 쿼리의 MGR 항목을 서브쿼리의 조건으로 투입하여 이름을 가져 오는 구문이다. 하지만 이에 대한 결과는 맞게 나오지만, 정답은 아니다. 이 문제의 해결방법 역시 PRIOR에 있다.

SELECT empno
, ename
, mgr
, PRIOR ename AS mgr_name
FROM emp m
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
;

필자가 이 문제를 퀴즈 형태로 냈을 때 대부분의 사람들이 위 서브쿼리 형태의 답변을 제시했다. 위 PRIOR를 이용한 답변을 제시했을 때 사람들의 반응은 “놀라움”이었다. 정말 간결하지 않은가 이는 PRIOR의 의미를 되새기는 좋은 문제이다.

[질문 6] 계층쿼리 사용시 ora-01436 에러가 발생해요.

ora-01436: CONNECT BY의 루프가 발생됐다. 이 에러는 계층 테이블의 데이터에 문제가 있을 때 발생한다.

예를 들자면 다음과 같은 경우다. 'A-B-C' 형태의 계층구조가 있을 때 C의 자식으로 A가 추가된 형태다. 'A-B-C-A'의 형태가 되는 것이다. 이 경우 A는 다시 B를 자식으로 갖게 되고, 'A-B-C-A-B-C-A-....' 이는 결국 무한 반복하는 순환 구조가 된다. 이러한 오류 데이터를 찾아 제거해주는 것이 근본 해결책이다. 그러나 그렇게 할 수 없을 때 사용할 수 있는 방법이 NOCYCLE이다.

CONNECT BY NOCYCLE PRIOR 하위코드 = 상위코드

NOCYCLE 구문을 사용하면 순환구조를 중간에 끊어버리고 에러를 발생시키지 않는다. 결과는 'A-B-C'까지만 수행되어 나오게 된다.

[질문 7] 순환구조 에러 발생 지점(데이터)을 알고 싶어요.

앞 [질문 6]의 연장선이다. NOCYCLE를 통해 에러를 해결할 수 있었지만, 에러의 원인을 찾아 제거하고 싶을 것이다. 이때 해당 자료는 CONNECT_BY_ISCYCLE을 이용해 찾을 수 있다. 이 값이 1을 가리키는 데이터를 찾으면 된다.

[질문 8] 계층 구조의 최상위 노드의 값을 알고 싶어요.

계층 전개 쿼리에서 바로 상위의 값을 확인하는 방법은 PRIOR를 이용하면 된다. 그렇다면 최상위 노드의 값은 어떻게 확인할 수 있을까 SYS_CONNECT_BY_PATH를 통해서도 알 수 있다.

REGEXP_SUBSTR(SYS_CONNECT_BY_PATH(empno, '-'), '[^-]+')

전체 계층의 경로로부터 '-' 가 아닌 연속문자열을 잘라내는 구문이다. 하지만 CONNECT_BY_ROOT 구문을 이용한다면 간단하게 최상위 값을 구할 수 있다.

CONNECT_BY_ROOT(empno)

[질문 9] 계층 구조의 말단 노드만을 뽑고 싶습니다.

최상위 노드인지 여부는 LEVEL 이 1인지를 판별하면 쉽게 알 수 있다. 그러나 최하위 노드는 그 레벨이 어디까지 이어지는지 알 수 없고, 또한 각 말단 노드들의 레벨이 각기 다를 것이다. 말단 노드인지를 판단할 수 있는 함수는 CONNECT_BY_ISLEAF이다.

WHERE CONNECT_BY_ISLEAF = 1

WHERE 절에 위 조건을 줌으로써 최하위 노드만을 뽑을 수 있다.

정리하며

지금까지 오라클 계층 쿼리 기본 구문의 사용법과 이와 관련하여 자주 올라왔던 질문과 그에 대한 해답을 살펴보았다. 다음 회에는 계층 쿼리의 응용문제를 풀어보도록 하겠다. (다음 회에 계속)