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

계층 구조 쿼리의 이해(4)

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

SQL은 필자에게 있어서 참으로 이해하기 쉬운 프로그램 언어였다. 간단한 기본 구문(SELECT, FROM, WHERE, GROUP BY, HAVING)과 조인을 통해 안 되는 게 없을 정도였기 때문이다. 물론 기본 구문 외에 다양한 함수들은 따로 배워야 하겠지만 말이다. 필자가 Q&A 게시판에 올라오는 수많은 질문에 답하면서 느낀 점은 ‘질문을 올린 사람은 SQL을 어렵게 생각하고 있었다’는 것이었다.

앞서 언급한 기본 구문들은 정말 너무나도 간단한데, 이 간단한 구문을 복잡하게 사용하고 있었던 것이다. 필자가 다루는 SQL은 전혀 복잡하지 않다. 필자는 SQL을 단순하고 쉽게 작성하는 노하우를 이 페이지를 통해 독자들과 공유하고자 한다. 자주 올라오는 SQL 질문들, 독자들이 어려워했던 질문들의 해법을 제시하고자 한다.

지난 시간에는 계층쿼리를 이용한 재미있는 응용문제를 풀어보았다. 이번 시간에는 CONNECT BY 구문이 아닌 전혀 다른 방법을 이용한 계층쿼리를 살펴보자. 계층쿼리의 새로운 사용법을 배우는 시간이 될 것이다.

[질문 17] 오라클의 Connect By 구문을 이용한 계층 쿼리를 다르게 구현하는 방법은 없나요

오라클에서는 START WITH와 CONNECT BY 구문을 이용하여 간단하게 계층형 자료 조회가 가능했다. 그런데 DB가 꼭 오라클이란 법은 없다. 오라클에는 계층구조를 처리하는 구문이 CONNECT BY 구문 외에 한 가지 더 있다. 지난 회에 소개한 계층쿼리와 구별하기 위해 재귀쿼리(Recursive SQL)라 칭하겠다. 이 재귀쿼리는 Oracle 11G 버전에서 새로 생긴 기능이다.

또한 MS SQL Server의 구문과 동일한 문법이 사용된다. 이 재귀쿼리 구문을 살펴보기 전에 지난시간에 소개했던 계층구문부터 살펴보자

SELECT empno
, ename
, mgr
, LEVEL lv
, CONNECT_BY_ROOT(ename) root
, SYS_CONNECT_BY_PATH(ename, '-') path
, ROWNUM ord
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno
;

계층쿼리와 그 실행 결과를 토대로 지난 회에 배운 내용을 복습해 보자. 계층 전개의 조건은 두 가지다, 시작조건과 전개조건. START WITH 구문에 시작조건을 적어주고 CONNECT BY 구문에 전개조건을 적어주면 계층 쿼리는 끝이다.

나머지는 부분은 다음과 같다.
레벨을 나타내는 LEVEL
경로를 표시해주는 SYS_CONNECT_BY_PATH
시작값을 표시해주는 CONNECT_BY_ROOT
ORDER SIBLING BY의 정렬 결과의 번호를 ROWNUM으로 표시했다.

이를 새로운 재귀쿼리 형태로 표현해 보겠다.

WITH t1(empno, ename, mgr) AS
(
SELECT empno, ename, mgr
FROM emp
WHERE mgr IS NULL -- START WITH mgr IS NULL
)
SELECT *
FROM t1
;

이 쿼리는 계층쿼리의 시작 조건인 START WITH 절의 조건만 따로 뽑은 것이다. 이렇게 뽑은 집합 T1을 이용해 하위 자식과 연결하는 SQL을 상상해 보자. 다음과 같은 형태가 될 것이다.

WITH t1(empno, ename, mgr) AS
(
SELECT empno, ename, mgr
FROM emp
WHERE mgr IS NULL -- START WITH mgr IS NULL
)
SELECT *
FROM t1
UNION ALL
SELECT c.empno
, c.ename
, c.mgr
FROM emp c
INNER JOIN t1 p
ON p.empno = c.mgr -- CONNECT BY PRIOR empno = mgr
;

부모집합인 T1과 자식 집합인 EMP를 계층구조 전개조건인 (p.empno = c.mgr)로 조인하는 형태가 될 것이다. 이 결과는 최상위집합 T1과 2레벨의 조인집합 3건이 나오는 형태다. 하지만 계층 전개는 2레벨에서 끝나고 만다.

3레벨을 전개하기 위해 계속 UNION ALL을 해야 한다면 무척이나 불편할 것이다. 다음과 같이 위 UNION 구문을 WITH 구문 안으로 올려보자.

WITH t1(empno, ename, mgr, lv) AS
(
SELECT empno, ename, mgr
, 1 lv
FROM emp
WHERE mgr IS NULL -- START WITH mgr IS NULL
UNION ALL
SELECT c.empno
, c.ename
, c.mgr
, p.lv + 1 lv
FROM emp c
INNER JOIN t1 p
ON p.empno = c.mgr -- CONNECT BY PRIOR empno = mgr
)
SELECT *
FROM t1
;

뭔가 이상한 구문이 완성되었다. WITH 문에서 정의한 집합 T1이 WITH 문 안에서 자시 참조되고 있는 형태다. 오류가 날 것 같은 이 구문은 정상적으로 수행되며 결과는 다음과 같다.



이해를 돕기 위해 레벨항목도 별도로 구현하였다.

1레벨이 조회돼 T1이 되고 이 1레벨의 다시 emp와 조인하여 2레벨을 만들어 내게 된다. 이렇게 만들어진 2레벨 자료는 다시 T1이 되어 3레벨 자료를 만들어 낸다.

3레벨 자료는 다시 4레벨 자료를 만들어 내는 부모가 된다. 이렇게 집합 T1이 계속해서 재귀적으로 호출되는 형태가 된다. UNION 쿼리에서 지속적으로 자기 자신을 참조해 나가면서 계층을 완성해 나가는 구문인 셈이다.

계층 쿼리에서는 약속된 함수(LEVEL, CONNECT_BY_ROOT, SYS_CONNECT_BY_PATH) 등이 사용되었다면, 재귀쿼리에서는 이러한 함수가 존재하지 않는다. 재귀적으로 참조해 나간다는 원리를 이용해 해당 함수들을 수동으로 만들어 낼 수 있다. 위 쿼리에서 레벨을 구현한 것을 보면 부모 레벨에 1을 더하여 자식 레벨을 만들어 낸 것을 알 수 있다.

나머지 항목들도 마찬가지로 모두 완성해 보도록 하자.

WITH t1(empno, ename, mgr, lv, root, path) AS
(
SELECT empno, ename, mgr
, 1 lv
, ename root
, ename path
FROM emp
WHERE mgr IS NULL -- START WITH mgr IS NULL
UNION ALL
SELECT c.empno
, c.ename
, c.mgr
, p.lv + 1 lv -- LEVEL
, p.root -- CONNECT_BY_ROOT(ename)
, p.path ||'-'|| c.ename path -- SYS_CONNCET)BY_PATH(ename)
FROM emp c
INNER JOIN t1 p
ON p.empno = c.mgr -- CONNECT BY PRIOR empno = mgr
)
SEARCH DEPTH FIRST BY empno SET ord -- ORDER SIBLINGS BY empno
SELECT *
FROM t1
;

사용된 계산식들을 살펴보자. 레벨은 상위 레벨에 1을 더하여 만들고
, p.lv + 1 lv

경로는 상위 경로에 하위 이름을 붙여 나간다.
, p.path ||'-'|| c.ename) path

루트는 변경 없이 상위 자료를 그대로 계속 이어 받는다.
, p.root

정렬부분은 어떻게 구현할까 재귀쿼리에서도 약속된 표현이 사용되는 부분이 있다. 바로 정렬 부분이다.

SEARCH DEPTH FIRST BY empno SET ord

이 부분이 없었다면 정렬된 결과를 얻기 힘들었을 것이다.

[질문 18] Recursiv SQL 구문을 어디에 쓰나요

계층쿼리가 더 간단한데 재귀쿼리는 뭐 하러 사용하나 하고 생각할 수 있다. 재귀쿼리는 구문은 복잡하지만 그 구조만 정확하게 이해한다면 다양하게 활용할 수 있다.

1. 위 질문에서 root와 path를 만들어 냈던 것처럼 여러 가지를 유연하게 만들어 낼 수 있다.
2. 계층 쿼리로 해결하지 못한 것을 재귀쿼리로 해결할 수도 있다.
3. MS SQL Server에서도 동일한 구문을 사용할 수 있다.

정리하며

이번시간에는 계층쿼리의 또 다른 구현방법인 재귀쿼리를 살펴 보았다. 계층쿼리와 비교하여 구문의 형태나 동작방식은 전혀 다르지만 동일한 결과를 얻어냈다.

- WITH 문에 정의한 집합이 WITH 문 안에서 재귀적으로 참조되는 형태다.
- UNION ALL의 위쪽 쿼리는 계층의 시작자료를 검색하는 부분이다.
- 아래쪽 쿼리는 재귀참조를 이용해 계층관계를 기술하는 부분이다.
- 계층쿼리의 함수들은 논리적인 수식을 이용하여 구현할 수 있다.

이로써 계층쿼리에 대한 부분을 모두 알아보았다. 다음 시간에는 다른 주제로 찾아뵙도록 하겠다. (다음 회에 계속)