Oracle UNION, UNION ALL example

오라클 WITH, UNION ALL 문장을 사용한 계층구조 커리 사용 예

UNION

2개의 테이블이나 결과집합을 합칠 때 동일한 행이 중복되지 않도록 합친다

UNION ALL

2개의 테이블이나 결과집합을 합칠 때 중복여부와 상관 없이 무조건 합친다

WITH 절 안에서 UNION ALL을 사용하면 반복적으로 호출되는 Recursive Member 커리가 되며 계층구조 출력기능을 수행할 수 있다

Hierarchical Queries Using Recursive WITH Clauses

https://oracle-base.com/articles/11g/recursive-subquery-factoring-11gr2

반복호출 WITH 절 (Recursive WITH Clauses)

WITH 절 안에 2개 이상의 커리가 UNION ALL으로 연결되어 있는 커리를 반복호출 WITH 절(Recursive WITH Clauses)이라고 한다

UNION ALL 위에 있는 커리를 Anchor Member (고정멤버 커리)라고 하고, UNION ALL 이후에 있는 커리를 Recursive Member (반복멤버 커리)라고 한다. Anchor Member 커리는 트리구조상의 최상위 노드(루트노드)를 가져오는 역할을 하며, Recursive Member 커리는 루트노드에 하위노드를 반복적으로 추가하는 역할을 한다

기본적인 계층구조(Hierarchical Query) 커리

-- WITH절의 컬럼수와 SELECT 컬럼수가 일치해야 한다
WITH t1(empno,ename,mgr)AS
(
  SELECT empno, ename, mgr FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr FROM emp e JOIN t1 ON e.mgr = t1.empno
)
SELECT * FROM t1; -- SELECT 절이 반드시 요구됨

위의 커리 실행결과 ( 깊이우선(Depth First)이 아닌 너비우선(Breadth First)으로 출력됨 )

7839	KING	
7566	JONES	7839
7698	BLAKE	7839
7782	CLARK	7839
7499	ALLEN	7698
7521	WARD	7698
7654	MARTIN	7698
7844	TURNER	7698
7900	JAMES	7698
7902	FORD	7566
7934	MILLER	7782
7369	SMITH	7902

정렬조건 지정

깊이 우선, 너비 우선, 동일차수일 경우 정렬기준 컬럼 지정

• BREADTH FIRST BY : Sibling rows are returned before child rows are processed.
• DEPTH FIRST BY : Child rows are returned before siblings are processed

WITH t1(empno,ename,mgr)AS
(
  SELECT empno, ename, mgr FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr FROM emp e JOIN t1 ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno SET ordering
SELECT * FROM t1 ORDER BY ordering;

위의 쿼리에서 SEARCH DEPTH FIRST BY empno  : 트리 구조에서 동일차수(한 노드 안에 있는 형제노드)의 노드들 사이에는 사번을 이용하여 정렬한다.

위의 쿼리에서 SET ordering : SET 다음에 오는 단어는 테이블로부터 데이터를 가져오는 순서가 정수로 저장되는 임시 컬럼이므로 주 쿼리에서 SELECT ordering, empno FROM t1 과 같은 방법으로 사용할 수 있다

위의 문장 실행결과

7839	KING		1
7566	JONES	7839	2
7902	FORD	7566	3
7369	SMITH	7902	4
7698	BLAKE	7839	5
7499	ALLEN	7698	6
7521	WARD	7698	7
7654	MARTIN	7698	8
7844	TURNER	7698	9
7900	JAMES	7698	10
7782	CLARK	7839	11
7934	MILLER	7782	12

WITH 반복커리에서 트리의 차수(LEVEL) 얻기

WITH t1(empno,ename,mgr,lvl)AS
(
  SELECT empno, ename, mgr, 1 AS lvl FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t1.lvl+1 AS lvl FROM emp e JOIN t1 ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno SET ordering
SELECT * FROM t1 ORDER BY ordering;

위의 문장 실행결과

7839	KING		1	1
7566	JONES	7839	2	2
7902	FORD	7566	3	3
7369	SMITH	7902	4	4
7698	BLAKE	7839	2	5
7499	ALLEN	7698	3	6
7521	WARD	7698	3	7
7654	MARTIN	7698	3	8
7844	TURNER	7698	3	9
7900	JAMES	7698	3	10
7782	CLARK	7839	2	11
7934	MILLER	7782	3	12

계층구조 커리 결과 트리의 차수를 이용하여 시각적인 계층구조를 표시하는 예

WITH t1(empno,ename,mgr,lvl)AS
(
  SELECT empno, ename, mgr, 0 AS lvl FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t1.lvl+1 AS lvl FROM emp e JOIN t1 ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno SET ordering
SELECT empno, LPAD('　', lvl*3, '　')||ename AS ename, mgr FROM t1 ORDER BY ordering;

위의 문장 실행결과

7839	KING	
7566	 　JONES	7839
7902	　　　FORD	7566
7369	 　　　　SMITH	7902
7698	 　BLAKE	7839
7499	　　　ALLEN	7698
7521	　　　WARD	7698
7654	　　　MARTIN	7698
7844	　　　TURNER	7698
7900	　　　JAMES	7698
7782	 　CLARK	7839
7934	　　　MILLER	7782

WITH 절에 테이블 내의 총 레코드 수를 포함하는 예

WITH t1(empno,ename,mgr,lvl, totalRows)AS
(
  SELECT empno, ename, mgr, 0 AS lvl, (SELECT COUNT(*) FROM emp) totalRows FROM emp WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t1.lvl+1 AS lvl, t1.totalRows FROM emp e JOIN t1 ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno SET ordering
SELECT empno, LPAD('　', lvl*3, '　')||ename AS ename, mgr, totalRows FROM t1 ORDER BY ordering;

위의 기능에 추가하여 레코드 중에서 한 화면에 보여줄 특정 행(Row)만을 가져오는 예

SEARCH DEPTH FIRST BY empno DESC SET ordering : 깊이우선 탐색으로 가져온 결과를 empno DESC 순으로 정렬하여 출력

위의 DESC 대신 ASC, 혹은 생략하면 오름차순이 된다

WITH t1(empno,ename,mgr,lvl, totalRows)AS
(
  SELECT empno, ename, mgr, 0 AS lvl, (SELECT COUNT(*) FROM emp) totalRows 
  FROM emp 
  WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t1.lvl+1 AS lvl, t1.totalRows 
  FROM emp e 
  JOIN t1 
  ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno DESC SET ordering
SELECT empno, LPAD('　', lvl*3, '　')||ename AS ename, mgr, totalRows 
FROM t1 
WHERE ordering BETWEEN 1 AND 5
ORDER BY ordering;

page 라는 임시컬럼을 생성하여 특정 페이지에 속하는 행들을 가져오는 예 (한 화면에 5행씩 보여주는 경우)

WITH t1(empno,ename,mgr,lvl, totalRows)AS
(
  SELECT empno, ename, mgr, 0 AS lvl, (SELECT COUNT(*) FROM emp) totalRows 
  FROM emp 
  WHERE mgr IS NULL
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, t1.lvl+1 AS lvl, t1.totalRows 
  FROM emp e 
  JOIN t1 
  ON e.mgr = t1.empno
)
SEARCH DEPTH FIRST BY empno SET ordering
SELECT * FROM
(
  SELECT empno, LPAD('　', lvl*3, '　')||ename AS ename, mgr, totalRows, TRUNC((ordering-1)/5)+1 AS page 
  FROM t1 
  ORDER BY ordering
)
WHERE page=1;