8.[Oracle] 집합연산자를 사용해보자.

-집합연산자

집합 연산자는 둘 이상의 구성 요소 query 결과를 하나의 결과로 조합합니다. 집합 연산자가 포함된 query를 복합 query라고 합니다.

 

연산자	반환
UNION	중복 행이 제거된 두 query의 행
UNION ALL	중복 행이 포함된 두 query의 행
INTERSECT	query에 공통적인 행
MINUS	첫번째 query에 있는 행 중 두번째 query에 없는 행

 

집합 연산자는 모두 우선 순위가 같습니다. SQL 문에 여러 개의 집합 연산자가 포함되어 있으면 Oracle 서버는 괄호가 명시적으로 다른 순서를 지정하지 않는 한 왼쪽(위)에서 오른쪽(아래)으로 연산자를 평가합니다. 다른 집합 연산자와 함께 INTERSECT 연산자가 사용된 query에서는 괄호를 사용하여 평가 순서를 명시적으로 지정해야 합니다.

 

-집합연산자 지침

• SELECT 리스트의 표현식은 개수가 일치해야 합니다.

• 두번째 query에 있는 각 열의 데이터 유형은 첫번째 query에 있는 상응하는 열의 데이터 유형과 일치해야 합니다. • 실행 순서를 변경하려면 괄호를 사용합니다.

• ORDER BY 절은 명령문의 맨 끝에만 올 수 있습니다.

 

-union 연산자

UNION 연산자는 양쪽 query에 의해 선택된 모든 행을 반환합니다. UNION 연산자를 사용하여 여러 테이블의 모든 행을 반환하고 중복된 모든 행을 제거합니다.

 

• 선택한 열의 개수가 동일해야 합니다.

• 선택한 열의 데이터 유형이 동일한 데이터 유형 그룹(숫자 또는 문자)에 속해야 합니다.

• 열 이름은 동일하지 않아도 됩니다.

• UNION은 선택된 모든 열에 적용됩니다.

• NULL 값은 중복 검사 시 무시되지 않습니다.

• 기본적으로 출력은 SELECT 절의 열을 기준으로 오름차순으로 정렬됩니다.

 

모든 사원의 현재 및 이전 직무 세부 사항을 표시합니다 . 각 사원을 한번만 표시합니다.

SELECT employee_id, job_id

FROM employees

UNION

SELECT employee_id, job_id

FROM job_history;

 

UNION 연산자는 중복 레코드를 제거합니다. EMPLOYEES 테이블과 JOB_HISTORY 테이블에서 나오는 레코드가 동일하면 레코드가 한 번만 표시됩니다. 슬라이드에 표시된 출력에서, EMPLOYEE_ID가 200인 사원의 레코드는 JOB_ID가 각 행에서 다르기 때문에 두 번 나타나는 것을 알 수 있습니다.

 

-UNION ALL

UNION ALL 연산자를 사용하여 여러 query에서 모든 행을 반환합니다.

UNION에 대한 지침과UNION ALL에 대한 지침은 두 가지 점 외에는 동일합니다. 즉, UNION ALL의 경우UNION과 달리 기본적으로 중복 행이 제거되지 않고 출력이 정렬되지 않습니다.

 

모든 사원의 현재 및 이전 부서를 표시합니다.

SELECT employee_id, job_id, department_id

FROM employees

UNION ALL

SELECT employee_id, job_id, department_id

FROM job_history

ORDER BY employee_id;

 

예제에서는 30개 행이 선택됩니다. 두 테이블의 총합은 30개 행입니다. UNION ALL 연산자는 중복 행을 제거하지 않습니다. UNION은 양쪽query에 의해 선택된 모든 구분 행을 반환하고, UNION ALL은 모든 중복 행을 포함하여 양쪽 query에 의해 선택된 모든 행을 반환합니다.

 

-INTERSECT 연산자

INTERSECT 연산자를 사용하여 여러 query에 공통되는 모든 행을 반환합니다.

• query에서 SELECT 문으로 선택된 열의 개수와 데이터 유형은 query에 사용된 모든 SELECT 문에서 동일해야 합 니다. 그러나 열 이름은 동일하지 않아도 됩니다.

• 교차 테이블 순서를 반대로 해도 결과에는 영향을 주지 않습니다.

• INTERSECT에서는 NULL 값이 무시되지 않습니다.

 

현재 직책이 이전 직책과 동일한 사원 (즉, 직무가 변경된 적이 있지만 현재는 이전 직무로 복귀한 사원)의 사원ID와 직무ID를 표시합니다.

SELECT employee_id, job_id

FROM employees

INTERSECT

SELECT employee_id, job_id

FROM job_history;

 

예제에서 query는 양쪽 테이블에서 선택된 열에 동일한 값을 가지는 레코드만 반환합니다.

EMPLOYEES 테이블에 대한 SELECT 문에 DEPARTMENT_ID 열을 추가하고 JOB_HISTORY 테이블에 대한 SELECT 문에 DEPARTMENT_ID 열을 추가한 다음 이 query를 실행하면 어떤 결과가 발생합니까? 이 결과는 사용된 다른 열 값의 중복 여부에 따라 다를 수 있습니다.

 

-minus

MINUS 연산자를 사용하여 첫번째 query에 의해 선택되지만 두번째 query 결과 집합에는 없는 모든 구분 행을 반환합니다

 

한번도 직무를 변경하지 않은 사원의 사원 ID를 표시합니다.

SELECT employee_id

FROM employees

MINUS

SELECT employee_id

FROM job_history;

 

예제에서는 EMPLOYEES 테이블에 있는 사원ID에서 JOB_HISTORY 테이블에 있는 사원 ID를 뺍니다. 결과 집합은 빼기 후에 남은 사원을 표시하며, 이들은 EMPLOYEES 테이블에 존재하지만 JOB_HISTORY 테이블에는 존재하지 않는 행으로 표시됩니다. 이것은 직무가 한 번도 변경되지 않은 사원의 레코드입니다.

 

-SELECT 문 일치

• UNION 연산자를 사용하여 위치 ID, 부서 이름 및 해당 부서가 소재하는 지역을 표시합니다.

• 선택될 열이 하나 이상의 테이블에 존재하지 않는 경우 TO_CHAR 함수 또는 다른 변환 함수를 사용하여 데이터 유 형을 일치시켜야 합니다.

 

SELECT location_id, department_name "Department", TO_CHAR(NULL) "Warehouse location"

FROM departments

UNION

SELECT location_id, TO_CHAR(NULL) "Department", state_province

FROM locations;

 

query에서 SELECT 리스트의 표현식은 개수가 일치해야 하기 때문에 더미 열과 데이터 유형 변환 함수를 사용하여 이러한 규칙을 준수합니다. 슬라이드에서는 더미 열 머리글로 Warehouse location이라는 이름이 제공되었습니다. 두번째 query에 의해 검색되는 state_province 열의 VARCHAR2 데이터 유형과 일치시키기 위해 첫번째 query에서 TO_CHAR 함수를 사용합니다. 이와 마찬가지로 첫번째 query에 의해 검색되는 department_name 열의 VARCHAR2 데이터 유형과 일치시키기 위해 두번째 query에서 TO_CHAR 함수를 사용합니다.

 

UNION 연산자를 사용하여 모든 사원의 사원 ID, 직무 ID 및 급여를 표시합니다.

SELECT employee_id, job_id,salary

FROM employees

UNION

SELECT employee_id, job_id,0

FROM job_history;

 

예제는 EMPLOYEES 테이블과 JOB_HISTORY 테이블의 EMPLOYEE_ID 열과 JOB_ID 열을 일치시킵니다. EMPLOYEES SELECT 문의 숫자 SALARY 열과 일치시키기 위해 JOB_HISTORY SELECT 문에 리터럴 값 0이 추가됩니다.

 

-집합 연산에서 ORDER BY 절 사용

• ORDER BY 절은 복합 query의 맨 끝에 한 번만 올 수 있습니다.

• 구성 요소 query에 개별적으로 ORDER BY 절을 사용할 수 없습니다.

• ORDER BY 절은 첫번째 SELECT query의 열만 인식합니다.

• 기본적으로 첫번째 SELECT query의 첫번째 열을 기준으로 오름차순으로 출력이 정렬됩니다.

 

ORDER BY 절은 복합 query에서 한 번만 사용될 수 있습니다. ORDER BY 절을 사용할 경우에는 query 맨 끝에 두어야 합니다. ORDER BY 절에서 열 이름이나 alias를 사용할 수 있습니다. 기본적으로 출력은 첫번째 SELECT query의 첫번째 열을 기준으로 오름차순으로 정렬됩니다.