[Oracle] JOIN (4) 자체 조인(SELF JOIN)

    4. 자체조인 (SELF JOIN)

    같은 테이블을 다시 한번 조인하는 경우

---

EMPLOYEE TABLE

---

-- 전체 사원의 사번, 사원명, 사원부서코드   ==> EMPLOYEE E
--  사수의 사번, 사수명, 사수부서코드          ==> EMPLOYEE M

 

-->> 오라클 전용 구문

SELECT E.EMP_ID AS "사원사번", E.EMP_NAME AS "사원명", E.DEPT_CODE AS "사원부서코드",
        M.EMP_ID AS "사수사번", M.EMP_NAME AS "사수명", M.DEPT_CODE AS "사수부서코드"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID;

 

-->>  ANSI 전용구문

SELECT E.EMP_ID AS "사원사번", E.EMP_NAME AS "사원명", E.DEPT_CODE AS "사원부서코드",
        M.EMP_ID AS "사수사번", M.EMP_NAME AS "사수명", M.DEPT_CODE AS "사수부서코드"
FROM EMPLOYEE E
LEFT JOIN EMPLOYEE M ON (E.MANAGER_ID = M.EMP_ID);