Day 21 Database

22/11/21

📌 4. 조인(join)

• employees의 manager_id : 직원의 매니저 id (직원의 상사의 empid)
  departments의 manager_id : 부서의 매니저 id (부서장의 empid)

• employees의 manager_id와 departments의 manager_id 좋지 않은 컬럼의 배치!!
  차라리 Employees안에서 manager_id 와 Employee_id 연관 있다.
           Departments의 manager_id와 Employee_id 연관 있다.

Ex 2) employees 테이블과 departments 테이블 사용
department_id, department_name, 부서의 manager_id, 부서의 매니저 이름(last_name)을 함께 출력하시오.

select departments.department_id, department_name, 
		departments.manager_id, last_name as "매니저 이름"
from departments join employees
on departments.manager_id = employees.employee_id;
-- (==)
select departments.department_id, department_name, 
		departments.manager_id, last_name as "매니저 이름"
from departments join employees
on employees.employee_id = departments.manager_id;	
-- 죄변과 우변을 바꿨지만 결과는 같다.

Ex3) employees 테이블과 departments 테이블 사용
전체 사원들의 employee_id, last_name, salary, 직원의 manager_id, 직원이 소속된 부서의 department_id, department_name, 부서의 manager_id를 함께 출력하시오.

select employee_id, last_name, salary, 
		employees.manager_id as "직원의 매니저",
		departments.department_id, department_name, 
        departments.manager_id as "부서의 매니저"
from employees join departments
on employees.department_id = departments.department_id;
-- (==)
select employees.employee_id,employees.last_name, employees.salary, 
		employees.manager_id as "직원의 매니저", 
		departments.department_id, departments.department_name, 
        departments.manager_id as "부서의 매니저"
from employees join departments
on employees.department_id = departments.department_id;

• 모든 컬럼명 앞에 하나하나(departments. employees.) 다 붙여주면 쿼리 구문을 실행하는 속도(성능)가 훨씬 빨라진다.
  성능은 좋아질지 모르지만 쿼리 구문이 길어지고 지저분해질 수 있음

• 테이블 alias(별명) : from 절에 선언하고 쿼리구문 전체에 사용됨.

select e.employee_id,e.last_name, e.salary,
		e.manager_id as "직원의 매니저", 
		d.department_id, d.department_name,
        d.manager_id as "부서의 매니저"
from employees e join departments d
on e.department_id = d.department_id;

Ex4) departments 테이블과 locations 테이블 사용
부서의 department_id, department_name, 부서가 위치한 city명을 함께 출력하시오.

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

Ex5) employees 테이블과 jobs 테이블 사용
전체 사원들의 employee_id, last_name, job_id, 업무의 전체 이름(job_title)을 함께 출력하되
employee_id가 200미만인 사원만 출력하고, employee_id를 기준으로 오름차순 정렬해서 출력하시오.

select e.employee_id, e.last_name, e.job_id, j.job_title
from employees e join jobs j
on e.job_id = j.job_id
where e.employee_id < 200
order by e.employee_id;

---

📂 N개의 테이블 조인하기

Ex6) employees, departments, locations 테이블 사용
직원들의 employee_id, last_name, salary, department_id, department_name,
location_id, city을 함께 출력하시오.

select e.employee_id, e.last_name, e.salary, d.department_id, 
		d.department_name, l.location_id, l.city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
-- (==)
select e.employee_id, e.last_name, e.salary, d.department_id, 
		d.department_name, l.location_id, l.city
from employees e join departments d join locations l
on e.department_id = d.department_id
and d.location_id = l.location_id; 		-- (on/and 가능)

-- 문법 오류 (on / on 연속으로는 불가능)
select e.employee_id, e.last_name, e.salary, d.department_id, 
		d.department_name, l.location_id, l.city
from employees e join departments d join locations l
on e.department_id = d.department_id
on d.location_id = l.location_id; 		-- (on / on 불가능)

---

📂 Self-join(자체조인)

: 자기 자신 테이블과 조인하는 유형.
하나의 테이블을 마치 다른 테이블인듯 alias를 다르게 부여해서 조인을 함.

❗❗❗❗❗ 헷갈림 주의 ❗❗❗❗❗
Ex7) employees 테이블로부터 사원들의 employee_id, last_name, salary, job_id,
직원의 상사ID(manager_id)와 직원의 상사 이름(last_name)을 함께 출력하시오.

select  e1.employee_id, e1.last_name, e1.salary, e1.job_id, 
		e1.manager_id as "매니저 ID", e2.last_name as "매니저 이름"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;
-- (==)
select  e1.employee_id, e1.last_name, e1.salary, e1.job_id, 
		e2.employee_id as "매니저 ID", e2.last_name as "매니저 이름"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

Ex8) employees, departments, jobs 테이블 사용
직원들의 employee_id, last_name, salary, job_title, department_name을 함께 출력하시오.

select e.employee_id, e.last_name, e.salary, j.job_title, d.department_name
from employees e join jobs j
on e.job_id = j.job_id
join departments d
on e.department_id = d.department_id;
-- (==)
select e.employee_id, e.last_name, e.salary, j.job_title, d.department_name
from employees e join jobs j join departments d
on e.job_id = j.job_id
and e.department_id = d.department_id;

---

✏️ <연습문제>

1. employees 테이블과 departments 테이블을 조인하여 모든 사원의 정보와 함께
   부서 정보를 함께 출력하시오.

select e.employee_id, e.last_name, e.salary, e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

2. employees 테이블로부터 모든 사원의 last_name, employee_id, 매니저 이름, manager_id를
   함께 출력하시오
   

select e1.last_name as "Employee", e1.employee_id as "Emp#", 
		e2.last_name as "Manager", e1.manager_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;
-- (==)
select e1.last_name as "Employee", e1.employee_id as "Emp#", 
		e2.last_name as "Manager", e2.employee_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

-- [오답] 직원의 이름, 직원의 사번, 직원의 매니저 이름(여기까진 O)
            직원의 매니저의 매니저 번호(X)

select e1.last_name as "Employee", e1.employee_id as "Emp#", 
	e2.last_name as "Manager", e2.manager_id as "Mgr#"
from employees e1 join employees e2
on e1.manager_id = e2.employee_id;

---

📂 조인 : 추가 유형(outer join)

• Inner join(내부조인) : 조인조건을 만족하는 행만 반환하는 조인
• Outer join(외부조인) : 만족하는 행과 만족하지 않는 행까지 반환하는 조인

• [알고 있어야 하는 테이블 내 데이터 정보]

select employee_id, last_name, department_id
from employees
where department_id is null;	-- 소속된 부서가 없는 사원 1명 있음.

select count(distinct department_id)
from employees;					-- 107명의 직원들이 소속된 부서의 개수는 11개임.

select*
from departments;				-- 우리 회사에 존재하는 부서는 27개임.
								-- 16개의 부서는 비어있음.

✍ inner join과 outer join 예제

employees 테이블과 departments 테이블을 department_id를 기준으로 조인을 해서
직원정보와 부서정보 함께 출력하시오.

• [작성1] inner join - 106명 출력/11개의 부서 출력됨.
  문제점 : 1) 부서가 없는 신입사원은 결과에 나타나지 않음.
             2) 직원이 소속되어 있지 않은 빈부서가 결과에 나타나지 않음.            

select e.employee_id, e.last_name, e.salary, 
		e.department_id, d.department_name
from employees e join departments d
on e.department_id = d.department_id;

• [작성2] outer join - 107명 출력
  부서가 없는 신입사원도 결과에 나타남.
  조인조건을 만족하지 않는 왼쪽 테이블에 있는 데이터를 출력하고 싶을때

select e.employee_id, e.last_name, e.salary, 
		e.department_id, d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id;

• [작성3] outer join - 빈부서 16개 추가로 출력됨
  조인조건을 만족하지 않는 오른쪽 테이블에 있는 데이터를 출력하고 싶을때

select e.employee_id, e.last_name, e.salary, 
		d.department_id, d.department_name
from employees e right outer join departments d
on e.department_id = d.department_id;