[ORACLE] JOIN-1

이번엔 JOIN을 알아 볼 것이다.

---

 JOIN 
 단일 테이블에서 정보를 쿼리하기도 하지만 때때로 두 세 개의 테이블에서 정보를 쿼리하는 경우도 많다. 

이럴 때 Join으로 여러 테이블들을 묶어서 쿼리하는데 FROM ~ 절에 원하는 항목이 들어 있는 테이블들을 지정해주면 된다. 
예를 들어 사원 King의 사원번호와 부서이름을 얻는다면 사원번호(employee_id)는 employees 테이블에 있지만 
부서이름(department_name)은 departments 테이블에서 얻어와야만 한다. 
이런 경우 두 테이블을 조인을 해야 한다.

  조인이 필요할 때에는 먼저 
1) 요구하는 정보(컬럼_명, 항목)는 무엇인가, 
2) 요구하는 이 정보는 어느 테이블에 들어 있나, 
3) 두(세)개 테이블을 조인한다면 각 테이블에서 서로 공통적으로 들어있는 열_명(관계형 데이터베이스이기 때문에 각 테이블은 특정 항목에서 PK와 FK의 관계가 적어도 하나씩은 있다)이 무엇인지 살피는데, 이 항목을 근거로 해서 조인이 이뤄진다.
4) 조인에는 Inner, Outer, Self, Full 등이 있으므로 적절히 지정해서 작업해주면 된다.

  INNER JOIN

그냥 Join 하면 이 inner join이다. inner를 생략하고 쓴다.
현재 HR로 로그인되어 있다면 
a. 자주 사용하는 employees 테이블에 대해서 
▪ DESC employees; 해서 employees의 열_명을 보면 EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION _PCT, MANAGER_ID, DEPARTMENT_ID가 있고,  ▪ DESC departments; 해서 departments의 열_명을 보면 DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID가 있다. 
=>이 두  테이블에 공통적으로 department_id 항목이 있다. 

** 테이블을 복사하면 원본 테이블에 들어 있는 제약조건은 일부 복사되지 않는다!!!
employees 테이블에는 employee_id가 156과 100인 두 명의 last_name이 'King'인 사용자가 있는데, 이들의 department_name을 보이시오. <= last_name, employee_id는 employees 테이블에 있고, department_name은 departments 테이블에 있으므로 이 두 테이블을 조인해야 한다!

select last_name, salary, employee_id, job_title, department_name
from employees e, jobs j, departments d
where e.job_id = j.job_id
AND e.department_id = d.department_id
AND last_name = 'King'; <= Oracle Join
 혹은 다음처럼 할 수도 있는데 
select last_name, salary, employee_id, job_title, 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
where last_name = 'King'; <= ANSI Join   

  OUTER JOIN
  Outer Join은 조인하는 두 테이블에서 항목/데이터의 수가 다를 때 이를 커버해주면서 조인해주는 기법이다. 
여기에는 right outer join과 left outer join 두 가지가 있는데 right/left를 쓰는 위치는 항목 수가 더 많은 테이블을 오른쪽/왼쪽에 두는지에 따라서 써주면 된다.

  SELF JOIN
  Self Join은 하나의 테이블을 Alias로 구분해서 마치 두 테이블인 것처럼 만들어 놓고 JOIN 해주는 기법이다.
  Employees 테이블에서 Executive 부서(department_name)의 각 사원의 last_name과 해당 사원의 부서장
(manager_id)의 last_name을 보이시오. employees 테이블에는 last_name과 manager_id가 있을 뿐이다. 이 문제를 해결하기 위해서는 manager_id의 last_name을 똑같은 employees 테이블에서 얻어야 한다. 하나의 employees 테이블을 Alias로 구분해서 마치 두 테이블인 것처럼 만들어 놓고 JOIN 해주면 된다. 

select last_name, d.manager_id, employee_id
from employees e JOIN departments d 
ON d.department_id = e.department_id
WHERE d.department_name = 'Executive'; =>상사가 manager_id(100)로 보임
==>
select A.last_name as EMP_NAME, B.last_name as MGR_NAME
from employees A JOIN employees B  // Self-Join
     ON A.manager_id = B.employee_id
                 JOIN departments D 
     ON A.department_id = D.department_id
where D.department_name = 'Executive' =>상사가 last_name('King')으로 보임; 

/* departments 테이블의 Executive 부서의 부서장과 부서원의 이름을 보이시오 */
select department_name, a.last_name, b.last_name
from employees a, employees b, departments d
where a.manager_id = b.employee_id
and a.department_id = d.department_id
and d.department_name = 'Executive';

select department_name, a.last_name, b.last_name
from employees a JOIN employees b
on a.manager_id = b.employee_id
                 JOIN departments d
on d.department_id = a.department_id
where d.department_name = 'Executive'; 
=>두번째 last_name을 self-join 하는 대신 scalar SubQuery를 사용할 수도 있다.
select last_name, department_name, 
(SELECT last_name FROM employees WHERE employee_id = e.manager_id) "MRG_NAME"
from employees e JOIN departments d
              ON e.department_id = d.department_id
where department_name = 'Executive'; 

---

●예제는 다음 글에서~~