[Oracle] 조인, 그룹함수, 서브쿼리

-4- 여러 테이블의 데이터 표시 (조인)

조인: 여러 테이블을 걸쳐서 검색.

(모든 정보가 하나의 테이블에 다 들어가있는 것이 아니므로, 종합적으로 정보를 보기 위해서 조인함)

=> 부하가 많이 걸림. 속도 느림.

따로따로 검색해서 사용하는 것이 속도면에서 빠름.

1. 카타시안 곱 (Cartesian Product)

: 조인 조건을 생략하거나 부적합한 경우

 

2. 조인 유형

- 오라클 구문 (등가, 비등가, 포괄, 자체)

-- 문법이 더 간략하고 쓰기 쉬움.

select table1.column, table2.column
from table1, table2
where table1.column1 = table2.column2
and 추가 조건;

-- 동일한 열 이름이 여러 테이블에 있는 경우 열 이름에 테이블 이름을 접두어로 붙임.

-- 열 이름이 다를 경우 신경 ㄴㄴ

-- from 에서 테이블 별칭 붙여서 간단하게 사용할 수 있음

 

* 등가 조인 (= 단순 조인, 내부 조인(inner join))

참조가 되는 테이블(=부모테이블)의 기본 키 (primary key) 

- 한 줄만 딱 고를 수 있는 키. (중복 되면 안됨, 값을 꼭 가지고 있어야 함.)

현 테이블에서 부모테이블과 값을 비교할 컬럼 (참조하는 컬럼) -> 외래 키

외래키가 기본키를 참조하여 비교,

 

* 테이블 별칭 사용

select e.department_id, d.department_id
from employees e, departments d
where e.department_id = d.department_id;

* and 추가 검색 조건

select last_name, employees.department_id, department_name
from employees, departments
where employees.department_id = departments.department_id
and last_name = 'Matos';

-- last_name 은 employees 에만 있어서,
-- department_name 은 departments 에만 있어서 별칭 안붙여도 시스템이 이해함
-- department_id는 employees와 departments 둘다 있어서 헷갈리지 않게 별칭 붙여줌

* 2개 이상 테이블 조인

select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id;

 

*비등가 조인 

- 조인할 테이블에 동일한 컬럼이 없어 서로 다른 값을 가지는 경우

=> where 조건을 충족할 시 출력.

 

▼ 테이블 생성

create table job_grades(
gra varchar2(1),    --문자1개 담을 열 정의 
lowest_sal number,
highest_sal number
);

insert into job_grades values('A', 1000, 2999);	-- 행 추가
insert into job_grades values('B', 3000, 5999);
insert into job_grades values('C', 6000, 9999);
insert into job_grades values('D', 10000, 14999);
insert into job_grades values('E', 15000, 24999);
insert into job_grades values('F', 25000, 40000);

select * from job_grades;

delete job_grades; -- 전제 행 삭제

delete job_grades where ~~~ -- 부분 행 삭제

select last_name, salary, gra
from employees, job_grades 
where salary between lowest_sal and highest_sal;
-- 사원의 급여 등급 계산

 

* 포괄 조인

- 조인 조건을 만족하지 않는 행도 보여줌.

(+)가 붙은 반대쪽 편의 내용을 보여줌. -- 내가 보고 싶은 쪽의 반대편에 붙인다.

select e.last_name, e.department_id, d.department_name
from employees e, departments d
where e.department_id(+) = d.department_id;
-- d.department_id 중 (e.department_id =) 조건을 만족하지 않는 d.department_id도 보여줌

 

* 자체 조인

- 자기 자신을 조인함. -- 테이블을 두 번 검색함.

- 열 별칭 필수.

select worker.last_name || ' works for ' || manager.last_name
from employees worker, employees manager
where worker.manager_id = manager.employee_id;
-- 사원의 관리자 번호가 관리자의 사원 번호와 일치하는 경우

 

- SQL 표준 (교차, 자연, Using, 전체 또는 한쪽 포괄, 포괄조인에 대한 임의의 조인 조건)

* 교차 조인 (cross join)

= 두 테이블 사이의 카티시안 곱과 동일

 

* 자연 조인 (natural join)

- 두 테이블 사이에서 이름이 같은 열을 조인. 다른 공통 열이 존재할 경우 함께 몽땅 조인.

- 동일한 컬럼을 자동 검색

select department_id, department_name, location_id, city
from departments
natural join locations
where department_id in (20, 50);
--

 

* Usint절을 포함하는 조인

- 등가 조인에 사용될 열만 지정할 수 있음

select e.employee_id, e.last_name, d.location_id
from employees e join departments d
using (department_id); 	--using 조인컬럼;
-- employees 및 departments 테이블의 department_id열을 조인하여 사원이 일하는 위치 표시

 

* on 절로 조인

- 임의의 조건을 지정하거나 조인할 열을 지정

- 조인 에서의 where 역할 

select 컬럼명, 컬럼명
from t1 join t2
on 조인 조건
where 행 조건;

select e.employee_id, e.last_name, e.department_id, d. department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id);

- 3-way 조인

select employee_id, city, department_name
from employees e
join departments d
on d.department_id = e.department_id
join locations l
on d.location_id = l.location_id;

- left outer join

select e.last_name, e.department_id, d.department_name
from employees e
left outer join deparments d
on (e.department_id = d.department_id);
-- departments 테이블에 일치하는 행이 없어도 왼쪽 테이블인 employees 테이블의 모든 행 검색

(=)
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where d.department_id(+) = e.department_id;

- right outer join

select e.last_name, e.department_id, d.department_name
from employees e
right outer join deparments d
on (e.department_id = d.department_id);
-- employees 테이블에 일치하는 행이 없어도 오른쪽 테이블인 deparments 테이블의 모든 행 검색

(=)
select e.last_name, e.department_id, d.department_name
from employees e, departments d
where d.department_id = e.department_id(+);

- full outer join

select e.last_name, e.department_id, d.department_name
from employees e
full outer join deparments d
on (e.department_id = d.department_id);
-- departments 테이블에 일치하는 행이 없어도 employees 테이블의 모든 행을 검색.
-- 또한 employees 테이블에 일치하는 행이 없어도 departments 테이블의 모든 행을 검색.

- 추가 조건 (and)

select e.employee_id, e.last_name, e.department_id, d.department_id, d.location_id
from employees e join departments d
on (e.department_id = d.department_id)
and e.manager_id = 149;
-- employees 및 departments 테이블 조인 후 관리자id가 149인 사원만 표시

 

 

---

 

 

-5- 그룹 함수를 사용한 데이터 집계

1. 그룹 함수: 행 집합에 작용하여 그룹 당 하나의 결과 생성

select [column,] group function(column), ...
from table
[where condition]
[group by column] -- 그룹 지을 조건
[having 그룹 선택 조건]
[order by column];

- count 제외하고 널 값 무시함.

avg

count  (널이 아닌 행의 수, * : 중복행 및 널 포함 모든 행의 수)

max

min

stddev (표준편차)

sum

variance (분산)

 

select avg(salary), max(salary), min(salary), sum(salary)
from employees
where job_id like '%REP%'; -- 아무 위치에나 REP 있는거 찾음

select min(hire_date), max(hire_date)
from employees;
-- 최근에 입사한 사원과 가장 오래전에 입사한 사원 표시

select min(last_name), max(last_name)
from employees;
-- 영문자 순으로 나열했을 때 맨 처음 및 맨 마지막에 해당하는 사원

select count(*)
from employees
where department_id = 50;
-- 널 값 행, 중복 행 포함하여 where 조건을 만족하는 행의 수 반환
-- count(*): 테이블의 행 수 반환

select count(commission_pct)
from employees
where department_id = 80;
-- 부서 80에서 커미션을 받는 사원의 수

select count(distinct department_id)
from employees;
-- employees 테이블에서 중복되지 않는 부서 값의 수 표시

select avg(nvl(commission_pct, 0))
from employees
-- nvl: 그룹함수가 널값을 포함하도록 강제로 지정
-- 모든 사원이 받는 커미션 총액을 회사 전체 사원 수(20)으로 나눈 값

 

 

2. 데이터 그룹 생성: group by 절 구문

- 열 별칭 사용 불가

select column, group_function(column)
from table
[where condition]
[group by group_by_expression] -- 행 그룹화 기준을 결정하는 값을 가진 열 지정 -> 각 그룹에 대한 요약 정보 반환
[order by column];

select department_id, avg(salary)	-- group by 열을 select 절에 포함시키지 않아도 됨
from employees
group by department_id
order by avg(salary);
-- 각 부서의 부서 번호 및 평균 급여 표시, 월급 평균으로 정렬

 

- 그룹 내 그룹

: 하나 이상의 group by 열을 나열하여 그룹 및 하위 그룹에 대한 요약결과 반환

select department_id dept_id, job_id, sum(salary)
from employees
group by department_id, job_id;

 

- 그룹 결과 제외: having 절

-- where 절을 사용하여 그룹 제한 불가능(where 절에서 그룹 함수  사용 불가) => having 절 사용

-- having 절을 사용하면 행이 그룹화 됨, 그룹함수 적용, having 절과 일치하는 그룹 표시됨

select column, group_function
from table
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

select department_id, max(salary)
from employees
group by department_id
having max(salary)>10000;
-- 최고 급여가 $10000를 넘는 부서의 부서 번호 및 최고 급여 표시

select job_id, sum(salary) payroll
from employees
where job_id not like '%REP%'
group by job_id
having sum(salary) > 13000
order by sum(salary)
-- 월급 총액이 $13000를 넘는 각 업무에 대해 업무 id와 월급 총액을 표시하되,
-- 영업 사원을 제외시킨 후 월급 총액에 따라 목록 정렬

 

- 그룹 함수 중첩

-- 그룹함수는 두 번까지 중첩될 수 있음.

select max(avg(salary))
from employees
group by department_id;
-- 최고 평균 급여 표시

 

 

---

 

 

 

-6- 서브 쿼리

~보다 많이 받는 사람 찾기

1. ~의 급여 찾기 ---- 서브쿼리(내부 질의)

2. 해당 급여보다 급여가 많은 사원 찾기 (외부 질의)

 

select select_list
from table
where expr operator연산자
                                (select select_list
                                 from table);

- 서브 쿼리를 괄호()로 묶음

- 비교 조건의 오른쪽에 서브쿼리를 넣음

- 서브쿼리의 order by 절은 Top-N 분석을 수행하지 않을 경우에는 필요 없음 (top 10 같이..)

- 단일 행 서브 쿼리에는 단일 행 연산자(=, >, >=, <, <=, <>)를 사용, 다중행 서브쿼리에는 다중 행 연산자 사용.

select last_name
from employees 
where salary >
                (select salary
                from employees
                where last_name = 'Abel');
-- 사원 Abel의 급여를 확인 (내부 질의)
-- 내부질의의 결과를 사용하여 해당 급여보다 급여가 많은 사원 모두 표시 (외부 질의)

 

1. 단일 행 서브 쿼리

select last_name, job_id, salary
from employees
where job_id =
                (select job_id
                from employees
                where employee_id = 141)
and salary >
                (select salary
                from employees
                where employee_id = 143);
-- 사원 141과 업무id가 동일하면서 사원 143보다 급여가 많은 사원 표시

 

- 서브 쿼리에서 그룹 함수 사용

select last_name, job_id, salary
from employees
where salary =
                (select min(salary)
                from employees);
-- 최소 급여를 받는 모든 사원의 이름, 업무id 및 급여를 표시
-- min 그룹 함수는 단일 값을 외부 질의에 반환

 

- having 절에 서브쿼리 사용

select department_id, min(salary)
from employees
group by department_id
having min(salary) >
                (select min(salary)
                from employees
                where department_id = 50);
-- 최소 급여가 부서 50의 최소 급여보다 많은 부서를 모두 표시

 

2. 다중 행 서브 쿼리

in (목록에 있는 임의의 멤버와 동일)

select last_name, salary, department_id
from employees
where salary in (select min(salary)
                from employees
                group by department_id);
-- 각 부서에서 최소 급여를 받는 사원

any (값을 서브 쿼리에 의해 반환된 각 값과 비교)

- <any : 최대값보다 작음

- >any : 최소값보다 큼

- =any : in과 동일

select employee_id, last_name, job_id, salary
from employees
where salary < any 
			(select salary
                    from employees
                    where job_id = 'IT_PROG')
and job_id <> 'IT_PROG';
-- IT 프로그래머가 아니면서 급여가 임의의 IT 프로그래머보다 낮은 사원을 표시

all (값을 서브쿼리에 의해 반환된 모든 값과 비교)

- <all : 최대값보다 큼

- >all : 최소값보다 작음

 

 

- 서브쿼리에서의 널값

select e.last_name
from employees e
where e.employee_id not in
    (select m.manager_id from employees m);
-- 부하직원이 없는 모든 사원
-- 널값으로 아무것도 안 뜸
-- 내부질의에서 반환한 값 중 하나가 널 값이므로 전체 질의의 행을 반환하지 않는 것.

select e.last_name
from employees e
where e.employee_id not in
    (select m.manager_id from employees m
     where manager_id is not null);
-- 사장인 king (manager_id = null)을 제외하고 평사원 이름 출력