[ORACLE] JOIN-2 예제들

JOIN의 예제들~~!!

---

**JOIN

select * from all_constraints
where table_name = 'EMPLOYEES1';

INNER JOIN
그냥 join하면 이 inner join이다. inner를 생략하고 쓴다. 

employees 테이블에는 employee_id가 156과 100인 두명의 last_name이 'King'인사용자가 있는데, 이들의 department_name을 보이시오. <=last_name, employee_id

select last_name, employee_id, department_name from employees, departments
where last_name = 'King';

select last_name, department_id, department_name from departments
whre department_id in (156, 100);//오류 조인 해야한다. 없는 테이블에서 불러오려고 하기 때문에 

select last_name, employee_id, department_name from employees, departments
where last_name ='King'; =where 이하가 적용 안되서 신뢰 할 수 없다

select last_name, employee_id, department_name from employees, departments
where last_name ='King'
and employees.department_id = departments.department_id; =이렇게 써줘서 조인 해야한다. 두테이블에 관계를 맺어줌

select e.last_name, e.employee_id, d.department_name from employees e, departments d / Alias, Cname(Canonical) name별명 출신이 정확하면 안써도 된다.
where last_name ='King'
and e.department_id = d.department_id;

select last_name, employee_id, department_name, department_id from employees e, departments d
where last_name ='King' //department_id는 애매해서 오류
and e.department_id = d.department_id; 
<비교>
select last_name, employee_id, department_name, d.department_id from employees e, departments d
where last_name ='King'
and e.department_id = d.department_id;
**=implicity(암묵적)/explicity(명시적)/ambigous(애배모호한)

select last_name, employee_id, department_name, e.department_id
from employees e INNER JOIN departments d
ON e.department_id = d.department_id
where last_name = 'King';

두 King 사원의 salary(e), first_name(e), employee_id(e), job_title(jobs), department_name(departments) 를 보이시오
=>desc로 공통적으로 들어가있는 부분을 찾아야 한다. 

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
조인하는 두테이블에서 항목/데이터의 수가 다를 댜 이를 커버해주면서 조인해주는 기법이다. 여기에는 right outer join 과 left outer join 두가지가 있는데 right/left를 쓰는 위치는 항목 수가 더 많은 테이블을 오른쪽/ 왼쪽에 두는지에 따라서 써주면 된다.

1999년 상반기에 입사한 사원들의 employee-id, last-name, hire_date 를 보이시오.
select employee_id, last_name, to_char(hire_date, 'yyyy-mm') "HIRED"from employees
where to_char(hire_date, 'yyyy-mm') between '1999-01'and '1999-06'; 
조인 한거
select employee_id, last_name, department_name, to_char(hire_date, 'yyyy-mm') "HIRED"from employees e join departments d
on d.department_id = e.department_id
where to_char(hire_date, 'yyyy-mm') between '1999-01'and '1999-06';
= 11행에서 10행으로 바뀌었는데 부서를 할당 못받았다.

outer join은 조인하는 테이블의 항목_수나 데이터_수에서 차이가 있는 것 커버해서 질의의 결과에 포함시킬때 사용한다. 위에서 join의 조건을 만족시키지 않는 grant사원의 정보가 employees테이블에 모두 있고, departments테이블에는 일부가 빠져 있으므로 join문에서 데이터가 많은 employees 테이블을 왼쪽에 두면 left outer join이 되고, 오른쪽에 두면 right outer join이 된다.

select employee_id, last_name, to_char(hire_date, 'yyyy-mm') as HIRED, department_name
from emploees e LEFT OUTER JOIN departments d
     ON d.department_id = e.department_id
where to_char(hire_date, 'yyyy-mm') BETWEEN '1999-01' and '19999-06';
<비교> 암시적
select employee_id, last_name, to_char(hIre_date, 'yyyy-mm') as HIRED, department_name
from departments d  RIGHT OUTER JOIN employees e
     ON d.department_id = e.department_id
where to_char(hire_date, 'yyyy-mm') BETWEEN '1999-01' and '19999-06';

select employee_id, last_name, to_char(hIre_date, 'yyyy-mm') as HIRED, department_name
from employees e JOIN  departments d
     ON d.department_id(+) = e.department_id
where to_char(hire_date, 'yyyy-mm') BETWEEN '1999-01' and '19999-06';
=데이터가 부족한쪽에 (+)를 써준다.

***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)로 보임
***<MGR_name을 100에서 king 이라는 last_name으로 비교>***
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 a.last_name, b.last_name, department_name,
from employees a, employees b, department 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 "MGR_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';

select last_name, department_name, 
(select last_name from employees where employee_id = e.manager_id) "MGR_NAME"
from employees e JOIN departments d
   on e.department_id = d.department_id
where department_name = 'Executive'; => last_name을 self-join 하는대신 scalar subsQuery를 사용할 수도 있다.