[Oracle SQL] Join

/* Inner Join : 일반적 Join */
 
select e.first_name, e.last_name, e.email, d.department_name
from employees e, departments d
where e.department_id = d.department_id;
 
 
 
select emp.first_name, emp.last_name, emp.email, emp.department_id, 
       dep.department_name, job.job_title, loc.city
from employees emp, 
     departments dep,
     jobs job,
     locations loc
where emp.department_id = dep.department_id
  and emp.job_id = job.job_id
  and dep.location_id = loc.location_id
  and loc.city = 'Seattle';
 
 
/* Self Join : 자신의 테이블에서 자신의 칼럼을 Join */
 
select emp1.employee_id, emp1.first_name, emp2.employee_id "상사ID", emp2.first_name "상사 이름"
from employees emp1, employees emp2
where emp1.manager_id = emp2.EMPLOYEE_ID;
 
 
/* 
   Outter Join
   (+) : 두개의 테이블 중 하나의 테이블 컬럼 데이터가 없을 경우 
         ROW가 누락되기 때문에 모든 데이터를 누락없이 출력하기 위해 사용
*/
 
select emp.employee_id, emp.first_name, emp.department_id, dep.department_name, loc.city
from employees emp, departments dep, locations loc
where emp.department_id = dep.department_id (+)
  and dep.location_id = loc.location_id (+);