join

˙join 형식

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

기본 키(Primary Key) : 테이블에서 중복이 되지않는 키
외래 키(Foreign Key) : 다른 테이블에서 Primary Key(PK), Unique Key(UK)인 경우가 많다.

JOIN 종류          중요도
inner join          *****
full outer join     *
cross join          *
outer join          
    left            ***     left outer join
    right           ***     right outer join
self join           ****

기본 키(Primary Key) 와 외래 키(Foreign Key)의 사용법을 잘 알아두도록 하자!

 

˙inner join

-- ansi SQL
SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id;
-- 생략가능
SELECT employee_id, first_name,
    e.department_id, d.department_id,   -- 양쪽 테이블에 관여하는 Alias는 생략할 수 없다.
    department_name
FROM employees e INNER JOIN departments d
    ON e.department_id = d.department_id;

-- oracle
SELECT e.department_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.job_id, j.job_id,
    j.job_title
FROM employees e, jobs j
WHERE e.job_id = j.job_id;

˙cross 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;

˙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;
     
-- 완전 차집합
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;     
     
-- oracle X -> 없는데 굳이 만들자면
SELECT e.department_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.department_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(+);

˙left outer join

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.department_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(+);

-- left outer join + 차집합
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;

˙right outer join

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.department_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;
    
-- right outer join + 차집합
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;

˙self join → 동일한 테이블을 join (형식은 inner join과 비슷하나 alias를 다 붙여줘야 한다.)
    동일 테이블이지만 다른 테이블로 생각하면 편하다!

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
    AND a.employee_id = 168;