[Oracle] JOIN (2) 포괄조인, 외부조인 (OUTER JOIN)

    2. 포괄 조인 / 외부 조인 ( OUTER JOIN )

    두 테이블 간의 JOIN시 일치하지 않는 행도 포함시켜서 조회 가능
    단, 반드시 LEFT / RIGHT 지정해야됨!! (기준이 되는 테이블 지정)

---

EMPLOYEE TABLE

---

-- 사원명, 부서명, 급여, 연봉

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

-- 부서 배치가 아직 안된 사원 2명에 대한 정보가 조회 X
-- 부서에 배정된 사원이 없는 부서 같은 경우도 조회 X

---

-- 1) LEFT [OUTER] JOIN : 두 테이블 중 왼편에 기술된 테이블 기준으로 JOIN

 

-->> ANSI 구문

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE -- EMPLOYEE에 있는건 무조건 다 나오는거임
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

-- 부서배치를 받지 않았던 2명의 사원 정보도 조회 됨

 

-->> 오라클 전용 구문

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE = DEPT_ID(+); -- 기준으로 삼고자 하는 테이블의 반대편 컬럼뒤에 (+) 붙이기

---

-- 2) RIGHT [OUTER] JOIN : 두 테이블 중 오른편에 기술된 테이블을 기준으로 JOIN

 

-->> ANSI 구문

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE
RIGHT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);

 

-->> 오라클 전용 구문

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE, DEPARTMENT
WHERE DEPT_CODE(+) = DEPT_ID;

---

-- 3) FULL [OUTER[ JOIN : 두 테이블이 가진 모든 행을 조회할 수 있음(단, 오라클 전용 구문으로는 안됨)

SELECT EMP_NAME, DEPT_TITLE, SALARY, SALARY * 12
FROM EMPLOYEE
FULL JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID);