[Oracle] 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    
-- 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(+)   -- +위치에 따라 바뀜 지금은 left join
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;  -- +위치에 따라 바뀜 지금은 right join


-- 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  -- LEFT를 RIGHT로 바꿀수 있음
        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(+);  -- (+)기호가 없는 쪽으로 조인된다. (없는쪽이 중심이 된다.)


-- left 차집합
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, departments d
WHERE e.department_id(+) = d.department_id
        AND e.department_id IS NOT NULL;