221121 월요일

[SQL문법]

5. 조인(join)

• 여러 테이블의 데이터를 함께 출력하기 위한 문법

 select 컬럼명1, 컬럼명2, 컬럼명3
 from 테이블1 join 테이블2
 on 테이블1.컬럼명 = 테이블2.컬럼명      → 조인조건문

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

  ex) from employees e join departments d

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

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

desc departments
desc locations

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

(예제5) 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개의 테이블 조인하기

테이블 수	조인조건 수
2	1
3	2
N	N-1

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

1.join on join 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
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;
(==) 
2.join on and
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;

• 문법 오류

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;

self-join(자체조인)

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

(예제7) 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;

(예제8) 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;

조인 : 추가 유형(outer join)

-	inner join(내부조인)	outer join(외부조인)
정의	조인조건을 만족하는 행만 반환하는 조인	조인조건을 만족하는 행과조인조건을 만족하지 않는 행까지 반환하는 조인
종류	on절 조인	[ left | right ] outer join

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

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

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

select *
from departments;  -- 우리 회사에 존재하는 부서는 27개임.

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

[작성1] inner join 👉 106명 출력됨 / 11개의 부서가 출력됨.

✔ 문제점 : ①부서가 없는 신입사원은 결과에 나타나지 않음.
               ②직원이 소속되어 있지 않은 빈부서가 결과에 나타나지 않음.
→ 조인조건을 만족하지 않는 것은 출력하지 않는 이너조인이기 때문이다!!

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명 출력됨.

✔ left outer join을 사용하면
→ 조인조건에 만족하지 않은 왼쪽 테이블도 출력해준다!

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개 추가로 출력됨.

✔ right outer join을 사용하면
→ 조인조건에 만족하지 않은 왼쪽 테이블도 출력해준다!

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;