[SQL] JOIN

* JOIN
    두개이상의 테이블을 연결해서 데이터를 검색하는 방법
    보통 두개이상의 행(ROW)들의 공통된 값 기본키, 외래키를 사용해서 JOIN한다
    
    목적: 현재테이블에서 상대테이블의 정보(column) 산출하기 위한 것
    
    기본키(Primary key) : 테이블에서 중복되지 않는키 
    외래키(Foreign key) : 다른 테이블에서 기본키이거나 고유키인 경우가 많다
    
    JOIN 종류
    inner join          = 교집합    ★★★★★ (중요도)
    full outer join    = 합집합
    cross join
    outer join                        ★★★
        left
        right
    self join                            ★★★★★

 

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

 

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

 

-- job_id == IT_PROG  업무명

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
    AND e.job_id = 'IT_PROG';

 

-- 3개 테이블을 조인

SELECT e.employee_id, e.first_name,
    d.department_name, j.job_title
FROM employees e, departments d, jobs j
WHERE e.department_id = d.department_id
    AND e.job_id = j.job_id
        AND e.employee_id = 101;

 

 

-- 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      left outer join + right 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;

 

 

-- left outer
-- 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;

 

-- right 차집합

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;


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;

 

 

-- self join : 동일한 테이블을 join

SELECT emp.employee_id, emp.first_name,
    emp.manager_id, mgr.employee_id,
    mgr.first_name
FROM employees emp, employees mgr   -- emp:사원 mgr:상사
WHERE emp.manager_id = mgr.employee_id;