[Oracle] SQL Joins (INNER, FULL, LEFT, RIGHT, SELF)

그림은 제가 이해하기 위해 직접 그렸습니다...😏

 

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

 

 

--ansi
SELECT e.employee_id, e.first_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 outer

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 outer

 

 

 

-- left outer (employees)
-- 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;
    
-- oarcle
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 outer (departments)
-- 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;

-- oarcle
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
WHERE e.department_id = d.department_id(+) --left outer
    AND e.department_id IS NULL; -- A-B

SELECT e.employee_id, e.first_name,
    e.department_id, d.department_id,
    d.department_name
FROM employees e
WHERE e.department_id(+) = d.department_id --right outer
    AND d.department_id IS NULL; -- B-A

 

+ Self join

-- 이름이 TJ인 사원보다 늦게 입사한 사원의 이름과 입사일을 출력하기
select b.first_name, b.hire_date
from employees a, employees b	--employees를 다르게 두개의 테이블로 생각 
where a.first_name = 'TJ'	--a테이블에서 TJ를 찾고 
    and a.hire_date < b.hire_date;	--TJ의 입사일보다 늦은 사원을 b테이블에서 찾기