[SQL 18] 집합 연산자(UNION, UNION ALL, INTERSECT, MINUS)

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
집합 연산자의 종류

SELECT 결과에 대해서 집합 연산을 수행한다.

연산자	의 미	결   과
UNION	합집합	중복을 제거한 결과의 합을 검색
UNION ALL		중복을 포함한 결과의 합을 검색
INTERSECT	교집합	양쪽 모두에서 포함된 행을 검색
MINUS	차집합	첫 번째 검색 결과에서 두 번째 검색 결과를 제외한 나머지를 검색

No. 02
집합 연산

기본 구조

SELECT ……

[UNION | UNION ALL | INTERSECT | MINUS]

SELECT ……

[ORDER BY 컬럼 [ASC/DESC]];

• 두 SELECT 문의 컬럼 개수와 데이터 타입은 일치해야 한다.
• 검색 결과의 헤더는 앞쪽 SELECT문에 의해 결정된다.
• ORDER BY 절을 사용할 때는 문장의 제일 마지막에 사용한다.

◈ 예제

▶ UNION을 이용한 합집합

select employee_id, job_id

from employees

union

select employee_id, job_id

from job_history;

위의 예제를 실행해보면 115개의 행이 검색된다. EMPLOYEES 테이블에는 107개의 행이 있고, JOB_HISTORY 테이블에는 10개의 행이 있다.

UNION ALL을 이용하여 합집합을 했다면 117개의 행이 검색됐겠지만 UNION 연산은 중복된 결과는 하나만 남기고 제거하기 때문에 115개의 행만 검색된 것이다. 오라클 성능의 관점으로 보면, UNION ALL은 중복을 제거하는 작업을 하지 않기 때문에 UNION ALL 연산이 성능이 더 좋다.

UNION 연산은 중복된 결과를 제거한 결과의 합이 검색된다.

가능하면 UNION 보다는 UNION ALL을 사용하는 것이 좋다.

▶ INTERSECT를 이용한 교집합

select employee_id, job_id

from employees

intersect

select employee_id, job_id

from job_history;

두 테이블에서 검색된 결과에서 중복된 값의 결과가 검색되었다.

INTERSECT 연산은 양쪽에서 공통된 결과를 검색한다.

▶ MINUS를 이용한 차집합

select employee_id, job_id

from employees

minus

select employee_id, job_id

from job_history;

첫 번째 SELECT 문의 결과와 두 번째 SELECT 문의 결과에서 공통된 행을 빼고 검색된다.

즉, 첫 번째 결과 107개의 행에서 공통된 2개 행을 뺀 105개의 행이 검색된다.

MINUS 연산은 첫 번째 결과에서 두 번째 결과를 제외한 나머지를 검색한다.

▶ SELECT 문의 매칭

select department_id, to_number(null) location, hire_date

from employees

union

select department_id, location_id, to_date(null)

from departments;

UNION 연산을 이용하여 모든 사원의 department_id, location, hire_date를 검색한 결과이다.

집합 연산을 할 때는 SELECT 문의 컬럼 개수와 타입이 일치해야 한다.

TO_DATE(null)을 사용한 이유는 DEPARTMENTS 테이블에는 hire_date 라는 컬럼이 없다. 그래서 컬럼의 개수와 컬럼의 타입을 맞춰주기 위해서 변환 함수까지 사용한 것이다. 물론 NULL 이라고만 써도 오라클 자체에서 자동변환되어 인식하겠지만, 이전에도 말했듯이 명시적으로 타입을 바꿔주면 성능면에서 더 좋다. NULL이 아닌 다른 값을 없는 컬럼의 값 대신 집어 넣을때는 변환 함수로 타입을 맞춰주지 않으면 에러가 발생하는 경우가 있다. 검색 결과의 헤더는 앞쪽 SELECT문에 의해 결정된다.

TO_NUMBER(null)도 마찬가지의 이유로 기술한 것이다. 여기서 주목해야 할 것은 TO_NUMBER(null)에 LOCATION 이라는 별명을 붙여주었다. SQL문의 실행 결과의 헤더에도 LOCATION으로 표시된다.

즉, 검색 결과의 헤더는 앞쪽 SELECT 문에 의해 결정된다.

---

 TIP 

보통 MINUS 집합 연산자를 이용한 방법보다 두 개의 조건을 이용한 SELECT 문이 훨씬 단순하고 가독성이 좋으며 성능 또한 우수하다. 그러나 경우에 따라 MINUS 연산자를 이용하면 성능이 훨씬 좋을 때가 있다. 다음과 같은 경우처럼 조건에 부정연산이 들어 갔을 때 같은 경우이다.

select employee_id, last_name, job_id

from employees

where last_name like 'K%'

and job_id <> 'SA_REP';

select employee_id, last_name, job_id

from employees

where last_name like 'K%'

minus

select employee_id, last_name, job_id

from employees

where job_id = 'SA_REP';

 인덱스란 나중에 배우면 알겠지만 책의 목차와 같은 역할을 한다. '<>'와 같은 부정연산은 인덱스를 사용할 수 없다. 만약 job_id 컬럼에 인덱스가 있더하더라도 job_id 컬럼에 대한 연산이 부정 연산이면 인덱스를 사용할 수 없다. 이런 경우 job_id에 대한 연산을 '<>'이 아니라 '='으로 바꾸면 인덱스를 이용할 수 있다. 위의 경우처럼 MINUS 연산자를 이용하여 일치하는 데이터를 검색한 다음 빼면 원하는 결과를 얻을 수 있다. 물론 이때 조건에 사용된 last_name 컬럼에도 사용가능한 인덱스가 있어야 한다.

---