DB: JOIN 사용 예졔

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

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

- 3개 이상 조인
-> 테이블1 join 테이블2
    on 공통컬럼1 = 공통컬럼1
    join 테이블 3
    on 공통컬럼2 = 공통컬럼 2
-----------------------------------------------------------------------------

퀴즈 hr> 3개이상 테이블을 조인하여 사원이름, 이메일, 부서번호, 부서이름, 직종번호, 직종이름

select e.last_name, e.email,e.department_id, d.department_name,
  j.job_id, j.job_title
from emp00 e, departments d, jobs j
where e.DEPARTMENT_ID = d.DEPARTMENT_ID
and e.job_id = j.job_id;
  
안씨 조인
select e.last_name, e.email,e.department_id,
d.department_name,
j.job_id, j.job_title
from emp00 e
inner join departments d
on e.department_id = d.department_id
inner join jobs j
on e.job_id = j.job_id;

-----------------------------------------------------------------------------
퀴즈hr> 'Seattle' (city)에 근무하는 사원의 이름, 부서번호, 직종번호, 직종이름, 도시이름을 출력하라.

select e.last_name, e.email,e.department_id,
  d.department_name,l.location_id,
  j.job_id, j.job_title,
  l.location_id, l.CITY
  from emp00 e inner join departments d
  on e.department_id = d.department_id
  inner join jobs j
  on e.job_id = j.job_id
  inner join locations l
  on l.location_id = d.location_id
  where city = 'Seattle';
  
select e.last_name, e.email,e.department_id,
  d.department_name,
  j.job_id, j.job_title,
  l.location_id, l.city
  from emp00 e, departments d, jobs j, locations l
  where e.DEPARTMENT_ID = d.DEPARTMENT_ID
  and e.job_id = j.job_id
  and l.location_id = d.location_id
  and city = 'Seattle';

문제 > 'aaaaaaaaaa' 의 부서이름을 출력하시오. 


-> select employee_id, department_id
        from employees
        where last_name = 'King';


-> select department_id, department_name
        from departments
        where department_id    IN(80,90);

=> 조인 이용하여 <- InnerJoin  
-> select employee_id, e.department_id, d.department_name
        from employees e, departments d
        where e.department_id = d.department_id
        and last_name = 'aaaaaaaaaa';