[ORACLE 9일차] UNION, MINUS, Connect by

◈◈ UNION 

이 연산은 두 개의 테이블을 하나로 만드는 연산이다.

즉, 2개의 테이블을 하나로 합치는 것. 

주의사항은 두 개의 테이블의 칼럼 수, 칼럼의 데이터 형식 모두가 일치해야 한다. 

만약 두 개의 테이블에 UNION 연산이 사용될 때 칼럼 수 혹은 데이터 형식이 다르면 오류가 발생한다.

UNION 연산은 두 개의 테이블을 하나로 합치며 중복된 데이터를 제거한다.

그래서 UNION은 정렬(SORT) 과정을 발생시킨다.

◈ UNION ALL

이 연산도 두 개의 테이블을 하나로 합치는 것.

UNION 처럼 중복을 제거하거나 정렬을 유발하지 않음.

아래의 예를 보고 이해하자!!!

SQL> SELECT * FROM EMP;

SQL> SELECT DEPTNO FROM EMP UNION SELECT DEPTNO FROM EMP;

다음과 같이 출력되었고,

SQL> SELECT DEPTNO FROM EMP UNION ALL SELECT DEPTNO FROM EMP;

중복을 제거하지 않고 모든 DEPTNO가 출력된 것을 볼 수 있다.

◈ MINUS

이 연산은 두 개의 테이블에서 차집합을 조회한다.

즉, 먼저 쓴 SELECT 문에는 있고, 뒤에 쓰는 SELECT 문에는 없는 집합을 조회하는 것.

MS-SQL에서는 MINUS 연산과 동일한 연산이 EXCEPT이다.

예를 들어, DEPT와 EMP를 MINUS 연산을 하면 DEPT에만 있는 행을 조회한다.

SQL> SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP;

다음과 같이 EMP에서 참조하고 있는 테이블 DEPT에만 있는 DEPTNO가 조회된 것을 확인할 수 있다.

◈ 계층형 조회 (= Connect by)

계층형 조회는 오라클 데이터베이스에서 지원하는 것으로 계층형으로 데이터를 조회할 수 있다.

즉, 상위 부장에서 차장, 차장에서 과장, 과장에서 대리, 대리에서 사원 순으로 트리 형태의 구조를 탐색하며 조회하는 것이다.

물론 역방향 조회도 가능하다.

계층형 조회를 실습하기 위해 EMP2 테이블을 만들어 본다.

create table EMP2(

    empno number(10) primary key,

    ename varchar2(20),

    deptno number(10),

    mgr number(10),

    job varchar2(20),

    sal number(10)

);

SQL>

insert into emp2 values(1000,'test1',20,NULL,'MANAGER',800);

insert into emp2 values(1001,'test2',30,1001,'SALESMAN',800);

insert into emp2 values(1002,'test3',30,1000,'CLERK',2900);

insert into emp2 values(1003,'test4',20,1000,'SALESMAN',2950);

insert into emp2 values(1004,'test5',30,1002,'CLERK',3950);

insert into emp2 values(1005,'test6',30,1001,'SALESMAN',3800);

insert into emp2 values(1006,'test7',30,1001,'CLERK',3100);

insert into emp2 values(1007,'test8',10,1003,'SALESMAN',2550);

insert into emp2 values(1008,'test9',20,1004,'CLERK',2800);

insert into emp2 values(1009,'test10',20,1005,'ANALYST',4200);

insert into emp2 values(1010,'test11',30,1006,'PRESIDENT',8800);

insert into emp2 values(1011,'test12',10,1007,'ANALYST',4100);

insert into emp2 values(1012,'test13',20,1006,'MANAGER',5100);

insert into emp2 values(1013,'test14',10,1007,'CLERK',3250);

insert into emp2 values(1014,'test15',30,1000,'CLERK',2890);

Connect by는 트리(Tree) 형태의 구조로 질의를 수행하는 것으로 START WITH구는 시작 조건을 의미하고,

CONNECT BY PRIOR 는 조인 조건이다. Root 노드로부터 하위 노드의 질의를 실행한다.

계층형 조회에서 최대 계층의 수를 구하기 위한 문제.

MAX(LEVEL)을 사용하여 최대 계층수를 구한다. 

즉, 계층형 구조에서 마지막 Leaf Node의 계층 값을 구한다.

SQL>

SELECT MAX(LEVEL)

FROM EMP2

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

SQL>

SELECT LEVEL, EMPNO, MGR, ENAME

FROM EMP2

START WITH MGR IS NULL

CONNECT BY PRIOR EMPNO = MGR;

위의 예에서 EMPNO와 MGR 칼럼 모두 사원번호가 입력되어 있다.

하지만 MGR은 관리자 사원번호를 가지고 있다. 즉 MGR 1000번은 1002,1003,1014번 사원을 관리한다. 

계층형 조회 결과를 명확히 보기 위해서는 LPAD함수를 사용할 수 있다.

LPAD 함수는 추후에 알아보기로 하자!!