1. 집합연산자(Set Operation)
집합연산자는 두 개 이상의 테이블을 연결하여 하나로 결합하는 연산자 입니다. 집합연산자를 사용하기 위해서는 컬럼의 수와 타입이 동일해야 합니다.
집합연산자에는 다음과 같은 유형이 있습니다.
집합연산자 유형
유형 |
설명 |
UNION |
합집합 연산을 수행하며, 중복을 제거하여 결과를 표시합니다. |
UNION ALL |
합집합 연산을 수행하며, 중복을 포함하여 결과를 표시합니다. |
INTERSECT |
교집합 연산을 수행합니다. |
MINUS |
차집합 연산을 수행합니다. |
기본 구조는 다음과 같습니다.
집합연산자 기본 구조
SELECT ... [UNION | UNION ALL | INTERSECT | MINUS] SELECT ... |
1) UNION
UNION은 합집합 연산을 수행하며, 중복된 행은 하나로 표시합니다. 내부적으로 중복된 값을 제거하는 과정에서 SORT 기능이 동작합니다.
아래와 같은 예제 데이터가 있습니다.
EMPLOYEES_1
ID |
NAME |
1 |
Alice |
2 |
Bob |
3 |
Charlie |
4 |
David |
EMPLOYEES_2 ID | NAME | 5 | Emily | 2 | Bob | 4 | David | 6 | Fiona |
두 테이블의 결과에 대하여 중복을 제거하여 합쳐 출력하기 위해서는 다음과 같은 쿼리문을 작성하면 됩니다.
UNION 예제
SELECT ID, NAME FROM EMPLOYEES_1 UNION SELECT ID, NAME FROM EMPLOYEES_2 |
UNION 결과
ID |
NAME |
1 |
Alice |
2 |
Bob |
3 |
Charlie |
4 |
David |
5 |
Emily |
6 |
Fiona |
2) UNION ALL
UNION ALL은 합집합 연산을 수행하며, 중복된 행을 그대로 표시합니다. UNION과 달리 데이터의 중복을 제거하거나 정렬 과정을 거치지 않기 때문에 성능상 UNION보다 유리합니다.
UNION ALL 예제
SELECT ID, NAME FROM EMPLOYEES_1 UNION ALL SELECT ID, NAME FROM EMPLOYEES_2 |
UNION ALL 결과 ID | NAME | 1 | Alice | 2 | Bob | 3 | Charlie | 4 | David | 5 | Emily | 2 | Bob | 4 | David | 6 | Fiona |
3) INTERSECT
INTERSECT는 교집합 연산을 수행하며, 중복된 행은 제거하여 출력합니다.
INTERSECT 예제
SELECT ID, NAME FROM EMPLOYEES_1 INTERSECT SELECT ID, NAME FROM EMPLOYEES_2 |
INTERSECT 결과 ID | NAME | 1 | Alice | 3 | Charlie |
4) MINUS
MINUS는 차집합 연산을 수행하며, 중복된 행은 제거하여 출력합니다. SQL Server에서는 EXCEPT를 사용합니다.
MINUS 예제
SELECT ID, NAME FROM EMPLOYEES_1 MINUS SELECT ID, NAME FROM EMPLOYEES_2 |
MINUS 결과
MINUS는 다음과 같이 NOT EXISTS나 NOT IN 서브쿼리를 통해서도 출력할 수 있습니다.
NOT EXISTS를 사용한 차집합 예제
SELECT ID, NAME FROM EMPLOYEES_1 A WHERE NOT EXISTS ( SELECT 1 FROM EMPLOYEES_2 B WHERE 1 = 1 AND A.ID = B.ID AND A.NAME = B.NAME ) |
NOT IN을 사용한 차집합 예제
SELECT ID, NAME FROM EMPLOYEES_1 WHERE (ID, NAME) NOT IN (SELECT ID, NAME FROM EMPLOYEES_2) |
2. 계층형 질의(Hierarchical Query)
계층형 질의는 동일한 테이블에 대하여 상위와 하위 포함 관계를 가진 계층형 데이터를 계층형 구조로 조회하기 위한 질의어 입니다.
기본 구조는 다음과 같습니다.
계층형 질의 기본 구조
SELECT 컬럼명 FROM 테이블명 WHERE 조건문 START WITH 조건문 CONNECT BY [NOCYCLE] 조건문 ORDER SIBLINGS BY 컬럼명 |
START WITH 키워드에서는 계층 구조의 시작(ROOT)를 지정하고, CONNECT BY 키워드에서는 자식 노드의 조건을 설정합니다. 이때 PRIOR 키워드를 사용하는데, 해당 키워드의 위치에 따라 순방향 계층형 질의 또는 역방향 계층형 질의를 할 수 있습니다.
조건문 앞에 NOCYCLE 키워드를 붙인다면 순환 데이터를 조회하지 않게 됩니다. 즉, 그래프 구조가 아닌 트리형 구조로 데이터를 조회할 수 있습니다.
같은 레벨인 형제 노드 간 정렬을 위해서는 ORER SIBLINGS BY 키워드를 사용합니다.
계층형 질의문에서는 다음과 같은 가상 컬럼을 사용할 수 있습니다.
계층형 질의문 가상 컬럼
가상 컬럼 |
설명 |
LEVEL |
루트 데이터를 1로 하며, 리프 데이터까지 1씩 증가합니다. |
CONNECT_BY_ISLEAF |
리프 데이터이면 1, 아니면 0을 출력합니다. |
CONNECT_BY_ISCYCLE |
사이클이 존재하면 1, 아니면 0을 출력합니다. |
다음과 같은 함수도 사용할 수 있습니다.
계층형 질의문 함수 함수 | 설명 | SYS_CONNECT_BY_PATH | 루트 데이터부터 현재 데이터까지의 경로를 출력합니다. | CONNECT_BY_ROOT | 현재 데이터의 루트 데이터를 출력합니다. |
예를 들어 아래와 같은 예제 데이터가 있습니다.
EMPLOYEES
EMPLOYEE_ID |
EMPLOYEE_NAME |
MANAGER_ID |
１ |
John |
|
２ |
Adam |
１ |
３ |
Emily |
１ |
４ |
Sarah |
２ |
５ |
Tom |
２ |
６ |
Jason |
３ |
７ |
Alex |
４ |
８ |
Chris |
４ |
MANAGER_ID를 상위노드로 하는 트리구조를 다음 그림과 같이 나타낼 수 있습니다.
트리구조 
해당 데이터를 계층형 질의문으로 출력해보겠습니다.
계층형 질의문 예제
SELECT EMPLOYEES_ID , EMPLOYEES_NAME , MANAGER_ID , LEVEL , CONNECT_BY_ISLEAF AS IS_LEAF , SYS_CONNECT_BY_PATH(EMPLOYEES_NAME, '/') AS PATH , CONNECT_BY_ROOT(EMPLOYEES_NAME) AS ROOT FROM EMPLOYEES START WITH MANAGER_ID IS NULL CONNECT BY NOCYCLE PRIOR EMPLOYEES_ID = MANAGER_ID |
MANAGER_ID가 NULL인 데이터가 루트 노드가 되고, EMPLOYEES_ID의 상위 데이터가 MANAGER_ID인 트리 구조를 DFS로 탐색합니다. 해당 트리는 순방향으로 진행됩니다. 결과는 다음과 같습니다.
계층형 질의문 결과
EMPLOYEES_ID |
EMPLOYEES_NAME |
MANAGER_ID |
LEVEL |
IS_LEAF |
PATH |
ROOT |
1 |
John |
|
1 |
0 |
/John |
John |
2 |
Adam |
1 |
2 |
0 |
/John/Adam |
John |
4 |
Sarah |
2 |
3 |
0 |
/John/Adam/Sarah |
John |
7 |
Alex |
4 |
4 |
1 |
/John/Adam/Sarah/Alex |
John |
8 |
Chris |
4 |
4 |
1 |
/John/Adam/Sarah/Chris |
John |
5 |
Tom |
2 |
3 |
1 |
/John/Adam/Tom |
John |
3 |
Emily |
1 |
2 |
0 |
/John/Emily |
John |
6 |
Jason |
3 |
3 |
1 |
/John/Emily/Jason |
John |
해당 계층형 질의문은 다음과 같이 셀프조인을 통해 나타낼 수도 있습니다.
셀프 조인 예제
SELECT A.EMPLOYEES_ID, A.EMPLOYEES_NAME, A.MANAGER_ID, B.EMPLOYEES_NAME AS MANAGER_NAME FROM EMPLOYEES A, EMPLOYEES B WHERE B.EMPLOYEES_ID = A.MANAGER_ID |
결과는 다음과 같습니다.
셀프 조인 결과
EMPLOYEES_ID |
EMPLOYEES_NAME |
MANAGER_ID |
MANAGER_NAME |
3 |
Emily |
1 |
John |
2 |
Adam |
1 |
John |
5 |
Tom |
2 |
Adam |
4 |
Sarah |
2 |
Adam |
6 |
Jason |
3 |
Emily |
8 |
Chris |
4 |
Sarah |
7 |
Alex |
4 |
Sarah |
|
스팟 
