2019. 4. 18. 10:56ㆍDateBase
-----------------------------Set 연산자 (집합 연산자)----------------------------------
set 연산자 (집합 연산자)
- 두 개 이상의 조회 결과를 하나로 결합한다.
- 연산자
UNION
A UNION B
A의 조회결과와 B의 조회결과에 포함된 모든 행을 반환한다.
단, A와 B 둘 다에 포함되어 있는 행은 한번만 선택된다.
(A와 B의 합집합을 반환한다. 단, 중복된 행은 한번만)
UNION ALL
A UNION ALL B
A의 조회결과와 B의 조회결과에 포함된 모든 행을 반환한다.
A와 B 모두에 포함된 중복된 행도 모두 반환한다.
(A와 B의 합집합을 반환한다. 중복된 행 포함)
INTERSECT
A INTERSECT B
A의 조회결과와 B의 조회결과에 공통으로 포함된 행만 반환한다.
(A와 B의 교집합을 반환한다, 교집합인 경우 조인도 가능하다.)
MINUS
A MINUS B
A의 조회결과에서 B의 조회결과를 뺀 행을 반환한다.
(A와 B의 차집합을 반환한다)
- 쿼리 작성 시 주의 사항
- A쿼리문과 B쿼리문의 선택된 컬럼의 갯수가 동일해야 한다.
SELECT 컬럼1, 컬럼2
FROM A
SELECT 컬럼3, 컬럼4
FROM B
- A쿼리문과 B쿼리문에서 선택된 컬럼의 데이터 타입이 동일해야 한다.
* 컬럼1, 컬럼3이 타입이 같아야하고 컬럼2와 컬럼4가 타입이 같아야한다.
- A쿼리문과 B쿼리문에서 선택된 컬럼의 이름을 동일하지 않아도 된다.
- 정렬을 위한 ORDER BY 절은 맨 마지막 SELECT문에 붙인다.
- 다중컬럼 서브쿼리
- 서브쿼리가 두 개 이상의 컬럼을 반환하는 경우 사용된다.
- SELECT ~
FROM ~
WHERE (컬럼1, 컬럼2) IN ( SELECT 컬럼1, 컬럼2
FROM ~
WHERE ~ )
* 컬럼1끼리, 컬럼2끼리 실제 컬럼명은 다를 수 있다.
- 스칼라 서브쿼리
- 하나의 행에서 하나의 값(1행 1열)만 반환하는 서브쿼리다.
- 오직 하나의 값을 반환하는 서브쿼리다.
- 주로 SELECT절에서 사용된다.
* DECODE 및 CASE의 조건 및 표현식 부분
* SELECT 문의 모든 절에서 사용가능하다. (GROUP BY 절은 제외)
* UPDATE문의 SET절
- 상호연관 서브쿼리
- SELECT COLUMN1, COLUMN2,
(SELECT COLUM1
FROM TABLE INNER
WHERE INNER.COLUM3 = OUTER.COLUM3)
FROM TABLE OUTER
- SELECT COLUM1, COLUM2, ...
FROM TABLE OUTER
WHERE COLUM3 = (SELECT COLUM1
FROM INNER
WHERE INNER.COLUMN4 = OUTER.COLUMN4)
- 상호연관 서브쿼리는 OUTER 쿼리의 행이 조회될 때마다 서브(INNER)쿼리가 실행된다.
예시) 모든 직원의 현재 직종 및 이전 직종을 사원아이디, 직종아이디로 표현하기
1) 각 사원에 대해 동일한 직종은 한번만 조회
SELECT EMPLOYEE_ID, JOB_ID -- 현재
FROM EMPLOYEES
UNION
SELECT EMPLOYEE_ID, JOB_ID -- 과거
FROM JOB_HISTORY;
2) 모든 직원의 현재 직종 및 이전 직종 표현하기
SELECT EMPLOYEE_ID, JOB_ID -- 현재
FROM EMPLOYEES
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID -- 과거
FROM JOB_HISTORY
ORDER BY EMPLOYEE_ID;
예시2) 50번 부서에서 근무중이거나 근무했던 사원의 아이디, 직종을 조회하기
SELECT EMPLOYEE_ID, JOB_ID, HIRE_DATE, NULL -- 값이 없는경우 NULL을 입력하면 실행된다.
FROM EMPLOYEES
WHERE DEPARTMENT_ID =50
UNION ALL
SELECT EMPLOYEE_ID, JOB_ID, START_DATE, END_DATE
FROM JOB_HISTORY
WHERE DEPARTMENT_ID = 50;
예시3) 현재 50번 주서에 근무중이 직원중에서 이전에 50번 부서 근무 경험이 있는 직원 조회하기
SELECT B.FIRST_NAME, A.EMPLOYEE_ID
FROM (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 50
INTERSECT
SELECT EMPLOYEE_ID
FROM JOB_HISTORY
WHERE DEPARTMENT_ID = 50) A, EMPLOYEES B
WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID;
예시) 90번 부서에 근무중인 사원중에서 업무가 변경된 적이 한번도 없는 사원의 아이디를 조회하기
SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90
MINUS
SELECT EMPLOYEE_ID
FROM JOB_HISTORY;
///////////////////////////////////다중컬럼 서브쿼리
예시) 직원아이디가 174 사원의 관리자 및 부서가 동일한 사원을 조회하기
SELECT *
FROM EMPLOYEES
WHERE (MANAGER_ID, DEPARTMENT_ID) IN (SELECT MANAGER_ID, DEPARTMENT_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 174);
예시2) 부서별 최고급여를 조회했을때 그 급여를 받는 사원 조회
SELECT EMPLOYEE_ID, FIRST_NAME, NVL(TO_CHAR(DEPARTMENT_ID),'없음'), SALARY
FROM EMPLOYEES
WHERE (NVL(DEPARTMENT_ID,0), SALARY) IN (SELECT NVL(DEPARTMENT_ID,0), MAX(SALARY)
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID);
///////////////////////////////////스칼라 서브쿼리
예시) 아이디, 이름, 급여, 평균급여와의 차
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY-(SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES) 평균급여차
FROM EMPLOYEES;
예시2) 평균급여보다 적은 급여를 받는 사원의 급여를 인상시키기
(평균급여 - 급여)/4
UPDATE EMPLOYEES
SET SALARY = SALARY + TRUNC(((SELECT AVG(SALARY) FROM EMPLOYEES) - SALARY/4))
WHERE EMPLOYEE_ID IN (SELECT EMPLOYEE_ID
FROM EMPLOYEES
WHERE SALARY <(SELECT AVG(SALARY)
FROM EMPLOYEES));
예시3) 급여별 급여 인상액 추청해보기
평균급여 대비 15000달러 이상 10%인상, 10000달러 이상 15%인상, 10000달러 미만 20%인상
SELECT
EMPLOYEE_ID,
FIRST_NAME,
SALARY,
CASE
WHEN SALARY >= 15000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.1)
WHEN SALARY >= 10000 THEN TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.15)
ELSE TRUNC((SELECT AVG(SALARY) FROM EMPLOYEES) * 0.2)
END INCREMENT_SALARY
FROM EMPLOYEES;
///////////////////////////////////////////상호연관 서브쿼리
예시) 부서 아이디, 부서이름, 부서별 사원수 계산하기
SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME,
(SELECT COUNT(*) FROM EMPLOYEES X WHERE X.DEPARTMENT_ID = D.DEPARTMENT_ID) 사원수
FROM DEPARTMENTS D
WHERE D.MANAGER_ID IS NOT NULL;
예시2) 일반 서브쿼리와 상호연관 서브쿼리
-- 전체 사원의 평균급여보다 급여를 많이 받는 사원 조회하기
일반서브쿼리
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > (SELECT AVG(SALARY)
FROM EMPLOYEES); -- 서브쿼리는 단 한번 실행되고, 실행결과가 조건식에 사용된다.
-- 소속부서의 평균급여보다 급여를 많이 받는 사원 조회하기
상호연관 서브쿼리
SELECT E.FIRST_NAME, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(B.SALARY)
FROM EMPLOYEES B
WHERE B.DEPARTMENT_ID = E.DEPARTMENT_ID);
