ORACLE 04. SELECT_ JOIN _다중조인

< 다중 조인>

 하나 이상의 테이블에서 데이터를 조회하기 위해 사용하고 수행결과는 하나의 Result set으로 나옴 

예시 1) 사번, 사원명, 부서명, 직급명 조회 

>> 오라클 전용구문

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE E, DEPARTMENT D, JOB J
WHERE DEPT_CODE = DEPT_ID(+) -- 포괄조인 
      AND E.JOB_CODE = J.JOB_CODE;

>> ANSI구문 

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE E
LEFT JOIN DEPARTMENT ON (DEPT_CODE= DEPT_ID)
JOIN JOB USING (JOB_CODE); -- 제시한 모든 테이블에 동일한 컬럼이 있으면 사용 ON으로 사용

 

예시 2)  전 사원들의 사번, 사원명, 부서명, 직급명,근무지역명, 국가명, 급여등급

>> 오라클 전용구문

SELECT EMP_ID
     , EMP_NAME
     , DEPT_TITLE
     , JOB_NAME
     , LOCAL_NAME
     , NATIONAL_NAME
     , SAL_LEVEL
  FROM EMPLOYEE E
     , DEPARTMENT D
     , JOB J
     , LOCATION L
     , NATIONAL N
     , SAL_GRADE S
 WHERE E.DEPT_CODE = D.DEPT_ID
   AND E.JOB_CODE = J.JOB_CODE
   AND D.LOCATION_ID = L.LOCAL_CODE
   AND L.NATIONAL_CODE = N.NATIONAL_CODE
   AND SALARY BETWEEN MIN_SAL AND MAX_SAL;

>> ANSI구문

SELECT EMP_ID
     , EMP_NAME
     , DEPT_TITLE
     , JOB_NAME
     , LOCAL_NAME
     , NATIONAL_NAME
     , SAL_LEVEL
  FROM EMPLOYEE E
  JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
  JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
  JOIN NATIONAL  N ON (L.NATIONAL_CODE = N.NATIONAL_CODE)
  JOIN SAL_GRADE S ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);

 

예시 3) 보너스를 받는 직원들의 사원명, 보너스, 연봉, 부서명, 근무지역명을 조회하시오
--    이때 부서 배정이 안된 사원이 있을 경우 부서명과 근무지역명은 '미정'으로 조회되도록 하시오.

>> 오라클 구문 

SELECT EMP_NAME, BONUS, SALARY*12, NVL(DEPT_TITLE,'미정'), NVL(LOCAL_NAME,'미정')
FROM EMPLOYEE E ,DEPARTMENT D, LOCATION L
WHERE E.DEPT_CODE = D.DEPT_ID(+)
AND D.LOCATION_ID = L.LOCAL_CODE(+)
AND BONUS IS NOT NULL;

>>ANSI구문 

SELECT EMP_NAME, BONUS, SALARY*12, NVL(DEPT_TITLE,'미정'), NVL(LOCAL_NAME,'미정')
FROM EMPLOYEE E 
LEFT JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
LEFT JOIN LOCATION L ON D.LOCATION_ID = L.LOCAL_CODE
WHERE BONUS IS NOT NULL;