Join

• 2개 이상의 테이블을 연결해서 데이터를 검색하는 방법
• 보통 두 개 이상의 행(row)들의 공통된 값의 기본키, 외래키를 사용해서 join함

• 기본 키(primary key) : 테이블에서 중복되지 않는 키
• 외래 키(foreign key) : 다른 테이블에서 pk(primary key), uk(unique key)인 경우

✔️ inner join

• 교집합

• ansi SQL

select employee_id, e.first_name,e.department_id, d.department_id, department_name  
from EMPLOYEES e inner join DEPARTMENTS d on e.department_id = d.department_id;

• oracle

select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from EMPLOYEES e, DEPARTMENTS d
where e.department_id = d.department_id

• 아래 결과처럼 연결되어 출력된다
  

✔️ cross join

• 하나가 다른 모든것과 join함
  

• ansi

select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from EMPLOYEES e cross join departments d;

• oracle

select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from EMPLOYEES e, departments d;

✔️ self join

• 동일한 테이블을 join

select a.employee_id, a.first_name, a.manager_id, b.employee_id, b.first_name
from EMPLOYEES a, EMPLOYEES b	-- a:사원, b:상사
where a.manager_id = b.employee_id;

• 출력 결과
  

✔️ outer join

left

• ansi

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name 
FROM employees e LEFT OUTER JOIN departments d
    ON e.department_id = d.department_id;

• oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
    AND e.department_id IS NULL;	-- null인 값만 취급하면 왼쪽만 구할 수 있음

right

• ansi

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name 
FROM employees e RIGHT OUTER JOIN departments d
    ON e.department_id = d.department_id;

• oracle

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id
    AND e.department_id IS NULL;

✔️ full outer join

• 합집합
  

• ansi

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name     
FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

• oracle(잘 사용하지 않음)

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+)
UNION
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;

✔️ full outer join + 차집합

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name     
FROM employees e FULL OUTER JOIN departments d
    ON e.department_id = d.department_id
WHERE e.department_id IS NULL
    OR d.department_id IS NULL;

사진 출처 : https://commons.wikimedia.org/wiki/File:Joins_del_SQL.svg