Outer join
Join 시키려는 컬럼의 해당 데이터 값이 NULL일 경우, 해당 데이터 출력이 누락되는데,
이런 누락되는 문제를 해결하기 위해 outer join이 사용된다.
NVL2 함수
기본적인 NVL 함수에서 확장된 기능을 가진다.
3개의 인자를 가지며, 첫번째는 대상 데이터, 두번째는 해당 데이터가 NULL이 아닐때,
세번째는 해당 데이터가 NULL일때 출력하고자 하는 값을 지정한다.
NVL( ~ , 1 ) : ~ 데이터가 NULL일 경우 1 로 반환
NVL2(~, 1, 2) ~데이터가 NULL일 경우 1 로, 아닐경우 2로 반환한다.
예시)
--NVL2함수
SELECT employee_id, NVL2(department_name, 'Have Department', 'Do not Have Department')
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
서브 쿼리
하나의 구문을 쿼리라고 칭한다면, 메인쿼리와 서브쿼리로 데이터를 나눠
두개의 데이터를 연계시켜 데이터를 처리할수 있다.
단일행 서브쿼리
서브쿼리의 반환 데이터의 행의 갯수가 1개 일경우 단일행 서브쿼리라 한다.
다중행 서브쿼리
서브쿼리의 반환데이터가 1개가 아닌 복수의 값을 가질때 다중행 서브쿼리라 한다.
IN 연산자
다중행 서브쿼리의 데이터가, 메인쿼리의 WHERE 조건에 하나라도 만족한다면 참입니다.
ALL 연산자
ALL은 다중 행 서브쿼리의 모든 값을 만족시키는 데이터 값을 출력한다.
서브쿼리의 값이 20~30이고, WHERE의 모든값이 100일때 WHERE > ALL(서브쿼리) 라는 조건문이 있다. 이는 서브쿼리의 모든 값보다 커야하므로, 31~100값이 반환 될것이다.
ANY 연산자
ALL과 유사하나 반대의 의미로, 어떠한 값이라도 만족하는 데이터 값을 찾아 반환한다.
서브쿼리의 값이 20~30이고, WHERE의 모든값이 100일때 WHERE < ANY(서브쿼리) 라는 조건문이 있다. 이는 ANY의 어떤 값과 비교해도 작아야하므로, 1~19가 출력 될것이다.
서브쿼리의 최소가 크면 참이 된다.
계층형 쿼리
피라미드의 형태로, 대표자가 최상위에 위치하며, 서로서로 MANAGE하는 사원들의 순번으로 출력하는 방식 (?)
--계층형 쿼리
사원의 사번, 이름, 관리자번호를 출력하시오.
SELECT employee_id, first_name, manager_id
FROM employees
SELECT LEVEL, LPAD(employee_id, length(employee_id)+ LEVEl -1 , '*' )employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
// 현재행의 employee_id를 manager_id로 쓰는 행을 찾기
LEVEL은 START WITH, CONNECT BY와 같이 사용할수 있다
SELECT employee_id, first_name, manager_id
FROM employees
SELECT LEVEL, LPAD(employee_id, length(employee_id)+ LEVEl -1 , '*' )employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NUL
CONNECT BY PRIOR employee_id = manager_id
책정리
p222, 232, 240, 244, 248,252,458
예시
outer join
--사원의 사번, 부서명을 출력하시오.
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
--모든사원(107명)의 사번, 부서명을 출력하시오.
SELECT employee_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
ANSI OUTER JOIN 변환
SELECT employee_id, department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
--NVL함수 적용
SELECT employee_id, NVL(department_name, 'Do not Have Department')
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
--NVL2함수
SELECT employee_id, NVL2(department_name, 'Have Department', 'Do not Have Department')
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id
--부서번호, 부서명, 부서가 속한 도시명을 출력하시오 (부서수:27, 도시수:23)
SELECT d.department_id, department_name, city
FROM departments d Join locations l
on d.location_id = l.location_id
// 27건
--부서번호, 부서명, 부서가 속한 도시명을 출력하시오 (부서수:27, 도시수:23)
단, 모든 도시를 출력하시오.
SELECT d.department_id, department_name, city
FROM departments d RIGHT OUTER Join locations l
on d.location_id = l.location_id
// 43건, 도시를 기준으로 출력하게 한다.
--도시별 부서수를 출력하시오. 단, 모든 도시를 출력하시오.
SELECT city, count(d.department_id)
FROM departments d RIGHT OUTER JOIN locations l
ON d.location_id = l.location_id
GROUP by city
--사원의 사번, 이름, 관리자 사번, 관리자 이름을 출력하시오. 관리자가 없는 사원도 모두 출력하시오.
SELECT d.employee_id "Employee ID",
NVL2(m.employee_id, to_char(m.employee_id), 'Do not have Manager') "Manager ID", NVL2(m.first_name, TO_CHAR(m.first_name), 'Do not have Manager') "Manager Name"
FROM employees d LEFT OUTER JOIN employees m
ON d.manager_id = m.employee_id
--사원별 사번, 이름, 업무시작일자, 업무종료일자, 업무명을 출력하시오
SELECT e.employee_id, first_name, start_date, end_date, j.job_title, e.
FROM employees e Join job_history jh
ON e.employee_id = jh.employee_id
Join jobs j on jh.job_id = j.job_id
--사원별 사번, 이름, 업무시작일자, 업무종료일자, 업무명, 현재 업무명을 출력하시오
SELECT e.employee_id, first_name, start_date, end_date, j.job_title, jn.job_title
FROM
employees e LEFT OUTER Join job_history jh
ON e.employee_id = jh.employee_id
LEFT Join jobs j on jh.job_id = j.job_id
LEFT JOIN jobs jn on e.job_id = jn.job_id
============================오후======================
--이전에 담당했었던 업무를 다시 담당한 사원들의 사번, 이름을 출력하시오.
SELECT e.employee_id, first_name
FROM employees e JOIN job_history jh
ON e.employee_id = jh.employee_id
WHERE e.job_id = jh.job_id
SELECT e.employee_id, first_name
FROM employees e JOIN job_history jh
ON e.employee_id = jh.employee_id AND e.job_id = jh.job_id
서브쿼리
--'IT'부서의 사번, 이름을 출력하시오.
1) 부서명이 'IT'인 부서테이블의 **컬럼 검색
2) **값과 같은 값을 갖는 사원 테이블의 컬럼 사번,이름 컬럼값 출력
SELECT employee_id, first_name
FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name = 'IT')
--최대급여를 받는 사원의 사번, 급여를 출력하시오.
SELECT first_name,employee_id, salary
FROM employees
WHERE salary
=
(SELECT max(salary)
FROM employees)
--평균급여보다 많은 급여를 받는 사원의 사번, 급여를 출력하시오.
SELECT salary, employee_id, first_name
FROM employees
WHERE salary >
(SELECT avg(salary)
FROM employees)
[Multi Row Subquery]
--부서별 최대급여를 받는 사원들의 사번, 급여를 출력하시오.
SELECT department_id, employee_id, salary
FROM employees
WHERE salary in (
SELECT max(salary)
FROM employees
GROUP BY department_id)
// 실행되나 데이터값 오류
[Pairwising]
SELECT department_id, employee_id, salary
FROM employees
WHERE (department_id,salary) in (
SELECT department_id, max(salary)
FROM employees
GROUP BY department_id)
// 부서별 최대급여를 구하기위해 부서별로 그룹을 짓고, 각 부서의 최대 급여를 구했다
(서브쿼리)
하지만 그 사원들(최대 급여를 받는)의 사원의 사번과 급여를 구하기 위해
전 사원의 데이터와 대조를 하게 된다 (서브쿼리 in)
하지만 첫번째 예제에서 사번과 급여를 묶지 않고 각개 매치시키게 된다.
이를 해결하기 위해서는, 서브쿼리에서 사번과, 최대급여를 묶고
메인 쿼리 조건문에서 서브쿼리의 묶음과 둘다 일치하는 데이터 값을 찾게 조건을 추가해주어야 한다.
WHERE (department_id,salary) in (
SELECT department_id, max(salary) //
[Multi Row SubQuery 연산자 : IN / ALL / ANY]
--30번 부서의 최대급여보다 많은 급여를 받는 사원의 부서번호, 사번, 이름을 출력하시오.
SELECT department_id, employee_id, first_name
FROM employees
WHERE
salary >
(
SELECT max(salary)
FROM employees
WHERE department_id = 30
)
-- ALL 연산자로 변환
SELECT department_id, employee_id, first_name
FROM employees
WHERE
salary > ALL
(
SELECT (salary)
FROM employees
WHERE department_id = 30
)
-- 30번 부서의 최소급여보다 큰 사원의 사번, 부서번호, 이름, 급여를 출력하시오
SELECT department_id, employee_id, first_name
FROM employees
WHERE
salary >
(
SELECT min(salary)
FROM employees
WHERE department_id = 30
)
--ANY연산자 변환
SELECT department_id, employee_id, first_name
FROM employees
WHERE
salary > ANY
(
SELECT (salary)
FROM employees
WHERE department_id = 30
)
[그룹함수 사용안함]
--30번 부서사원들의 최대급여보다 적은 급여를 받는 사원의 사번, 부서번호, 이름, 급여를 출력하시오.
SELECT employee_id, department_id, first_name, salary
FROM employees
where salary < ANY
(
SELECT salary
FROM employees
WHERE department_id = 30
)
--30번 부서사원들의 최대급여보다 많은 급여를 받는 사원의 사번, 부서번호, 이름, 급여를 출력하시오.
SELECT employee_id, department_id, first_name, salary
FROM employees
where salary > ALL
(
SELECT salary
FROM employees
WHERE department_id = 30
)
--30번 부서사원들의 최소급여보다 적은 급여를 받는 사원을 출력하시오.
SELECT employee_id, department_id, first_name, salary
FROM employees
where salary < ALL
(
SELECT salary
FROM employees
WHERE department_id = 30
)
--30번 부서사원들의 급여와 다른 급여를 받는 사원을 출력하시오.
SELECT employee_id, department_id, first_name, salary
FROM employees
where salary <> ALL
(
SELECT salary
FROM employees
WHERE department_id = 30
)
=ANY : IN과 같은 의미
<>ANY : 하나 이상 다르다
>ANY : MIN()보다 크다
<ANY : MAX()보다 작다
=ALL : 모두 같다
<>ALL : 모두 다르다 NOT IN과 같은 의미.
>ALL : MIN()보다 크다
<ALL : MAX()보다 작다
--------------------------------------------------------------------
--'Shipping'부서에 속한 사원들 중 'Sales'부서의 평균 급여보다 적은 급여를 받는 사원의 사번, 급여, 부서 번호를 출력하시오.
SELECT first_name, salary, department_id
FROM employees
WHERE salary < (
SELECT avg(salary)
FROM employees
WHERE department_id
=
(
SELECT department_id
FROM departments
WHERE department_name='Sales'
))
AND department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Shipping'
)
-- 'Sales'부서에 속한 사원들 중 'Sales'부서의 평균 급여보다 적은 급여를 받는 사원의 사번, 급여, 부서번호를 출력하시오.
SELECT first_name, salary, department_id
FROM employees
WHERE salary < (
SELECT avg(salary)
FROM employees
WHERE department_id
=
(
SELECT department_id
FROM departments
WHERE department_name='Sales'
))
AND department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
)
상호연관 서브쿼리
SELECT first_name, salary, department_id
FROM employees a
WHERE salary < (SELECT avg(salary)
FROM employees
WHERE department_id = a.department_id
)
AND department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
)
--계층형 쿼리
사원의 사번, 이름, 관리자번호를 출력하시오.
SELECT employee_id, first_name, manager_id
FROM employees
SELECT LEVEL, LPAD(employee_id, length(employee_id)+ LEVEl -1 , '*' )employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
// 현재행의 employee_id를 manager_id로 쓰는 행을 찾기
LEVEL은 START WITH, CONNECT BY와 같이 사용할수 있다
SELECT employee_id, first_name, manager_id
FROM employees
SELECT LEVEL, LPAD(employee_id, length(employee_id)+ LEVEl -1 , '*' )employee_id, first_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id