/* ****************************************************
Self 조인
- 물리적으로 하나의 테이블을 두개의 테이블처럼 조인하는 것.
- self join에서는 하나를 부하테이블, 하나를 상사테이블로 생각
**************************************************** */
-- 직원 ID가 101인 직원의 직원의 ID(emp.emp_id), 이름(emp.emp_name), 상사이름(emp.emp_name)을 조회
select * from emp where emp_id = 101;
select emp_name as 상사이름 from emp where emp_id = 100;
select e.emp_id,
e.emp_name as "직원이름",
m.emp_name as "상사이름"
from emp e join emp m on e.mgr_id = m.emp_id
where e.emp_id = 101;
/* ****************************************************************************
외부 조인 (Outer Join)
- 불충분 조인
- 조인 연산 조건을 만족하지 않는 행도 포함해서 합친다
종류
left outer join: 구문상 소스 테이블이 왼쪽
right outer join: 구문상 소스 테이블이 오른쪽
full outer join: 둘다 소스 테이블 (Mysql은 지원하지 않는다. - union 연산을 이용해서 구현)
- 구문
from 테이블a [LEFT | RIGHT] OUTER JOIN 테이블b ON 조인조건
- OUTER는 생략 가능.
**************************************************************************** */
-- 직원의 id(emp.emp_id), 이름(emp.emp_name), 급여(emp.salary), 부서명(dept.dept_name), 부서위치(dept.loc)를 조회.
-- 부서가 없는 직원의 정보도 나오도록 조회. dept_name의 내림차순으로 정렬한다.
select count(*)
from emp e join dept d on e.dept_id = d.dept_id;
select count(*) from emp where dept_id is null;
-- 모든 직원의 id(emp.emp_id), 이름(emp.emp_name), 부서_id(emp.dept_id)를 조회하는데
-- 부서_id가 80 인 직원들은 부서명(dept.dept_name)과 부서위치(dept.loc) 도 같이 출력한다. (부서 ID가 80이 아니면 null이 나오도록)
select e.emp_id, e.emp_name, e.emp_id,
d.dept_name, d.loc
from emp e left join dept d on e.dept_id = d.dept_id
and d.dept_id = 80; -- and 추가적인 조건 => where 조건과는 다르다. join조건은 어떤 값들을 붙일 것인가에 대한 조건
-- where d.dept_id = 80;
-- TODO: 직원_id(emp.emp_id)가 100, 110, 120, 130, 140인
-- 직원의 ID(emp.emp_id),이름(emp.emp_name), 업무명(job.job_title) 을 조회. 업무명이 없을 경우 '미배정' 으로 조회
-- inner join
select e.emp_id, e.emp_name, j.job_title
from emp e join job j on e.job_id = j.job_id
where e.emp_id in (100, 110, 120, 130, 140);
-- outer join
select e.emp_id, e.emp_name, j.job_title
from emp e left join job j on e.job_id = j.job_id
where e.emp_id in (100, 110, 120, 130, 140);
-- inner join vs outer join => outer join을 사용한다.
select * from emp where emp_id in (130,140);
-- e.job_id는 null값을 가지고 있기에 join이 되지 않음 => null값을 채워줘야함
-- TODO: 부서 ID(dept.dept_id), 부서이름(dept.dept_name)과 그 부서에 속한 직원들의 수를 조회. 직원이 없는 부서는 0이 나오도록 조회하고 직원수가 많은 부서 순서로 조회.
-- count(*) : 행수
select d.dept_id, d.dept_name, count(e.emp_id) as "직원수"
from dept d left join emp e on d.dept_id = e.dept_id
group by d.dept_id, d.dept_name;
-- 부서 테이블의 모든 내용이 다 들어가야하므로, left join을 해줘야함, emp table에서는 없지만 dept테이블에는 있는 직원이 '직원수 = 1' 로 컬럼에 들어가게됨
-- count(e.emp_id)를 해줘야 사람수 0, count(*) => 행수를 포함해서 출력
-- TODO: EMP 테이블에서 부서_ID(emp.dept_id)가 90 인 모든 직원들의 id(emp.emp_id), 이름(emp.emp_name), 상사이름(emp.emp_name), 입사일(emp.hire_date)을 조회.
-- 입사일은 yyyy/mm/dd 형식으로 출력
select e.emp_id,
e.emp_name,
m.emp_name as "상사이름",
date_format(e.hire_date, '%Y년 %m월 %d일') as 'hire_date'
from emp e left join emp m on e.mgr_id = m.emp_id
where e.dept_id = 90;
-- TODO 2003년~2005년 사이에 입사한 모든 직원의 id(emp.emp_id), 이름(emp.emp_name), 업무명(job.job_title), 급여(emp.salary), 입사일(emp.hire_date),
-- 상사이름(emp.emp_name), 상사의입사일(emp.hire_date), 소속부서이름(dept.dept_name), 부서위치(dept.loc)를 조회.
select e.emp_id,
e.emp_name,
j.job_title,
e.salary,
e.hire_date,
m.emp_name as "상사이름",
m.hire_date as "상사입사일",
d.dept_name,
d.loc
from emp e left join job j on e.job_id = j.job_id
left join dept d on e.dept_id = d.dept_id
left join emp m on e.mgr_id = m.emp_id
where year(e.hire_date) between 2003 and 2005;
-- 한번 outer join -> outer join하지 않으면 값이 사라지므로, 계속 해야함
-- New York(dept.loc)에 있는 부서의 직원들의 업무명(job.job_title)을 조회
-- job + emp + dep
select d.loc,
j.job_title
from dept d left join emp e on d.dept_id = e.dept_id
left join job j on e.job_id = j.job_id
where d.loc = 'New York';
