23일차//[Oracle] JOIN

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

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

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 employee_id, first_name,
    e.department_id, d.department_id,
    department_name, d.location_id

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


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

--ORACLE left outer join + 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(+)
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;

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

--대칭차집합
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 :동일한 테이블을 조인, 자기테이블에 있는 데이터 찾기
SELECT a.employee_id, a.first_name,
     a.manager_id, b.employee_id,
     b.first_name
     -- b는 상사에 대한정보
FROM employees a , employees b -- a: 사원  b: 상사
WHERE a.manager_id = b.employee_id;

--계층형 구조 오름, 내림
SELECT a.employee_id, a.first_name AS"사원",
    a.manager_id AS "사원의 상사", b.employee_id,
    b.first_name AS "상사"
FROM employees a, employees b
WHERE a.manager_id = b.employee_id(+)
CONNECT BY PRIOR a.manager_id = a.employee_id; -- 상향식