SQL 7. SET 연산자 사용
관련링크
본문
7-1. SET 연산자
SET 연산자는 둘 이상의 구성 요소 질의 결과를 하나로 결합한다.
왜!!! 조인은 안되는가?
조인이 될려면 두개의 테이블의 비교값이 있어야 하며
조인은 옆으로 붙고 SET 연산자는 밑으로 붙는다
SET연산자가 들어 있는 질의를 복합 질의라고 한다.
모든 SET연산자는 동등한 우선순위를 갖는다.
SQL문에 여러 개의 SET 연산자가 들어 있는 경우 명시적(explicit)으로 순서를 지정하는 괄호가 없으면 SQL server에서는 이를 왼쪽(위)에서 오른쪽(아래)로 평가한다.
INTERSECT연산자를 다른 SET연산자와 함께 사용하는 질의에서 명시적으로 평가 순서를 지정하려면 괄호를 사용해야 한다.
7-2. UNION 연산자
UNION 연산자는 두 질의 중 어느 것 하나에 의해서라도 선택된 행은 모두 반환한다.
여러 테이블의 행을 모두 반환한 후 중복 행을 제거하려면 UNION 연산자를 사용한다.
지침
선택될 열의 개수와 데이터 유형은 질의에 사용된 모든 SELECT 문에서 같아야 한다.
열 이름은 같지 않아도 된다.
UNION은 선택될 열전체에 대해 작동한다.
중복을 확인하는 동안에도 NULL 값은 무시되지 않는다.
IN 연산자는 UNION 연산자보다 높은 우선순위를 갖는다.
기본적으로 결과는 SELECT 절의 첫째 열에 대해 오름차순으로 정렬된다.
예제) UNION 연산자 사용
모든 사원의 현재 및 이전 업무에 대한 세부 사항을 표시한다.
각 사원에 대해 한 번씩만 표시한다.
오라클 디비 예제 :
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
오라클 디비 예제 :
SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;
☞설명) UNION SET 연산자 사용
UNION 연산자를 사용하면 중복 레코드가 모두 제거 된다.
EMPLOYEES 및 JOB_HISTORY 테이블 모두에 같은 레코드가 있으면 한 번만 표시된다.
출력에서, EMPLOYEE_ID가 200인 사원에 대한 레코드는 JOB_ID가 서로 다르므로 두 번 표시된다.
EMPLOYEE_ID가 200인 사원에 대한 레코드는 JOB_ID와 DEPARTMENT_ID가 다르므로 세 번 출력이 된다.
즉, 행 대한 열값이 모두 같아야 한번만 출력되고 적어도 한 열만 달라도 모든 행이 출력된다.
7-3. UNION ALL연산자
여러 질의의 행을 모두 반환하려면 UNION ALL 연산자를 사용한다.
지침
UNION 과 달리 중복 행이 제거되지 않으면 기본적으로 출력이 정렬되지 않는다.
DISTINCT 키워드는 사용할 수 없다.
예외를 제외하면 UNION 과 UNION ALL 의 지침은 동일하다.
지침
질의에 있는 SELECT 문에 의해 선택될 열의 개수와 데이터 유행은 질의에 사용된 모든 SELECT 문에서 같아야 합
니다. 열 이름은 같지 않아도 됩니다.
예제) UNION ALL 연산자 사용
오라클 디비 예제 :
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행이 된다.
UNION ALL 연산자를 사용하면 중복 행이 제거되지 않는다.
UNION ALL 대신 UNION을 사용하여 출력 결과를 확인하면 총 29행이 출력된다.
즉, UNION 연산자를 사용하면 중복행은 제거가 된다는 것을 확인 할 수 있다.
UNION ALL을 사용하면 중복 행이 강조 표시된다.
UNION은 두 질의 중 어느 것 하나에 의해서만 이라도 선택된 구분(distinct) 행을 모두 반환한다.
UNION ALL 은 두 질의 중 어느 것 하나에 의해서만 이라도 선택된 행은 중복 행을 포함하여 모두 반환한다.
위 예제 결과와 비교해 보자.
7-4. INTERSECT 연산자
INTERSECT 연산자는 여러 질의에 공통적인 행을 모두 반환한다.
지침
질의에 있는 SELECT문에 의해 선택될 열의 개수와 데이터 유형은 질의에 사용된 모든 SELECT문에서 같아야 한다.
열 이름은 같지 않아도 된다.
INTERSECT에 사용될 테이블의 순서를 바꿔도 결과는 달라지지 않는다.
INTERSECT는 NULL 값을 무시하지 않는다.
예제) INTERSECT 연산자 사용
입사 이후 현재 업무와 같은 업무를 담당한 적이 있는 사원의 사원ID와 업무ID를 표시한다.
오라클 디비 예제 :
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
☞설명) INTERSECT 연산자
예제 질의에서는 양쪽 테이블에서 선택된 열이 같은 값을 가지는 레코드만 반환한다.
EMPLOYEES 테이블에 대한 SELECT 문에 DEPARTMENT_ID 열을 추가하고 JOB_HISTORY 테이블에 대한 SELECT문에
DEPARTMENT_ID 열을 추가한 다음 이 질의를 실행하면 추가된 행 값의 중복 여부가 달라지므로 결과가 달라질 수 있다.
employees테이블과 job_history테이블의 employee_id, job_id가 같은 행은 2행이 반환 된다.
예제)
오라클 디비 예제 :
SELECT employee_id, job_id, department_id
FROM employees
INTERSECT
SELECT employee_id, job_id, department_id
FROM job_history;
☞설명)
위 예제와 비교 해보자.
세 개의 열의 값이 같은 행만 반환한다.
employees테이블과 job_history테이블의 employee_id, job_id, department_id가 같은 행은 1행이 반환 된다.
7-5. MINUS 연산자
MINUS 연산자를 사용하면 첫째 질의가 반환한 행으로부터 둘째 질의가 반환한 행을 제외한 행이 반환된다.
(첫째 SELECT문 MINUS 둘째 SELECT문)
WHERE 절에 있는 모든 열이 SELECT 절에 있어야 MINUS 연산자가 작동합니다.
예제) MINUS 연산자
업무가 변경된 적이 없는 사원의 사원 ID를 표시합니다.
오라클 디비 예제 :
SELECT employee_id, job_id
FROM employees
MINUS
SELECT employee_id, job_id
FROM job_history;
☞설명) MINUS 연산자
예제에서는 EMPLOYEES 테이블의 사원 ID 및 업무 ID에서 JOB_HISTORY 테이블의 사원ID 및 업무ID를 뺀다.
결과 집합에서는 EMPLOYEES 테이블에 있지만 JOB_HISTORY 테이블에는 없는 행을 표시한다.
이것은 업무를 바꾼 적이 없는 사원에 대한 레코드다.
7-6. SET 연산자 지침
질의의 SELECT 목록에 있는 표현식의 개수와 데이터 유형이 서로 일치해야 한다.
WHERE 절에 UNION, UNION ALL, INTERSECT 및 MINUS SET 연산자를 사용하는 질의는 해당 SELECT목록에도
WHERE 절과 같은 개수와 유형의 열을 사용해야 한다.
ORDER BY 절:
- 명령문의 끝에만 사용할 수 있다.
- 열 이름, 별칭 또는 위치 표기법을 사용할 수 있다.
ORDER BY 절에 사용된 열 이름 또는 별칭은 첫째 SELECT목록과 일치해야 한다.
SET 연산자는 서브 쿼리에 사용할 수 있다.
예)
오라클 디비 예제 : SELECT employee_id, department_id
FROM employees
WHERE (employee_id, department_id)
IN ( SELECT employee_id, department_id
FROM employees
UNION
SELECT employee_id, department_id
FROM job_history );
7-7. Oracle Server와 SET연산자
질의에서 SET연산자를 사용하면 Oracle Server에서는 UNION ALL 연산자의 경우를 제외하고 자동으로 중복 행을 제거 한다.
출력되는 열 이름은 첫째 SELECT문의 열 목록에 따라 결정된다.
기본적으로 출력은 SELECT절의 첫째 열에 대해 오름차순으로 정렬된다.
혼합질의의 여러 구성 요소 질의에 있는 SELECT 목록들의 표현식은 개수와 데이터 유형이 서로 일치해야 한다.
* 구성 요소 질의에서 문자 데이터를 선택하면 반환 값의 데이터 유형
두 질의에서 모두 데이터 유형이 CHAR인 값을 사용하면 반환 값의 데이터 유형은 CHAR이다.
두 질의 중 하나에서 데이터 유형이 VARCHAR2인 값을 사용하면 반환 값의 데이터 유형은 VARCHAR2이다.
예제) SELECT 문 일치
UNION 연산자를 사용하여 모든 사원의 부서ID, 위치 및 입사 일자를 표시한다.
오라클 디비 예제 :
SELECT department_id, TO_NUMBER(null) location, hire_date
FROM employees
UNION
SELECT department_id, location_id, TO_DATE(null)
FROM departments;
☞설명) SELECT문 일치
두 질의의 SELECT 목록에 있는 표현식의 개수를 일치시켜야 하므로 더미(dummy)열 및 데이터 유형변환 함수를 사용하
여 이 규칙을 적용할 수 있다.
location이라는 이름은 더미 열 머리글로 주어진다.
첫째 질의에 TO_NUMBER 함수를 사용하여 둘째 질의에서 검색하는 LOCATION_ID 열의 NUMBER 데이터 유형에 일치 시킨다.
둘째 질의에 TO_DATE함수를 사용하여 첫째 질의에서 검색하는 HIRE_DATE 열의 DATE 데이터 유형에 일치 시킨다.
예제) SELECT 문 일치
UNION 연산자를 사용하여 모든 사원의 사원ID, 업무ID 및 급여를 표시한다.
오라클 디비 예제 :
SELECT employees_id, job_id, salary
FROM employees
UNION
SELECT employee_id, job_id, 0
FROM job_history;
☞설명) SELECT 문 일치
EMPLOYEES 및 JOB_HISTORY 테이블에는 EMPLOYEE_ID, JOB_ID 및 DEPARTMENT_ID와 같이 공통적인 열이 여러 개있다.
UNION 연산자를 사용하여 EMPLOYEE_ID, JOB_ID 및 SALARY를 표시하는 질의를 작성하려고 하는데 급여가
EMPLOYEES 테이블에만 존재하면 EMPLOYEES 및 JOB_HISTORY 테이블의 EMPLOYEE_ID 열과 JOB_ID 열을 일치시키
고, 리터럴 값 0을 JOB_HISTORY SELECT문에 추가하여 EMPLOYEES SELECT 문에 있는 숫자 유형의 SALARY 열과 일
치 시킨다.
이 출력 결과에서는 JOB_HISTORY 테이블의 레코드에 해당하는 각 행의 SALARY 열에 0이 들어 있다.
예제) 행 순서 제어
두 UNION 연산자를 사용하여 영어 문장을 만듦.
오라클 디비 예제 :
COLUMN a_dummy NOPRINT
SELECT 'sing; AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I' 'd like to teach', 1
FROM dual
UNION
SELECT ‘the world to', 2
FROM dual
ORDER BY 2;
☞설명) 행 순서 제어
기분적으로 출력은 첫째 열에 대해 오름차순으로 정렬된다.
ORDER BY 절을 사용하면 이를 변경할 수 있다.
ORDER BY를 사용하여 행 정렬
ORDER BY절은 혼합 질의에서 한 번만 사용 할 수 있다.
ORDER BY 절은 질의의 끝에 와야 하며 ORDER BY절에는 열 이름, 별칭 또는 위치 표기법을 사용할 수 있다.
ORDER BY절 없이 슬라이드에 있는 코드 예제를 실행하면 첫째 열의 문자 순으로 다음과 같이 출력됩니다.
형식]
COL[UMN] [{column|alias} [option]]
[option]
CLE[AR]: Clears any column formats
HEA[DING] text: Sets the column heading
FOR[MAT] format: Changes the display of the column using a format model
NOPRINT | PRINT: Suppresses or displays the column heading and data
NULL