오라클 조인

--표준화 된 JOIN

--Natural Join (컬럼명 같으면 무조건 조인. e. d. 등 별칭쓰는 것 금지. 이름이 같으면 다 조인하기 때문에 매우 위험하다.)
SELECT employee_id, first_name, last_name, department_id, department_name
FROM employees NATURAL JOIN departments;

--Join USING절 (using 값이 같으면 조인. 별칭명 금지.)
SELECT employee_id, first_name, last_name, department_id, department_name
FROM employees JOIN departments USING(department_id);

--Join ON절 (컬럼명 달라도 조인. 별칭명 정확히 써줘야함.)
SELECT employee_id, first_name, last_name, e.department_id, department_name
FROM employees e JOIN departments d ON(e.department_id = d.department_id);

 

 

--오라클 전용 OUTER JOIN (부족한 부분에 (+)를 쓴다.)
SELECT employee_id, e.department_id, department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);

 

--FULL OUTER JOIN
--사번, 부서번호, 부서명을 모두 출력하시오.
SELECT e.employee_id, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id);

--오라클 전용 FULL JOIN은 안된다.
--SELECT employee_id, d.department_id, d.department_name
--FROM employees e, departments d
--WHERE e.department_id(+) = d.department_id(+);