DAY10. VIEW & WITH

VIEW

1. 테이블이나 다른 뷰(VIEW)를 이용해서 만든 가상 테이블이다.
2. 데이터가 아닌 쿼리문만을 저장하고 있다.
3. 자주 사용하고 복잡한 쿼리문을 뷰로 만들어 두면 쉽게 호출해서 사용할 수 있다.

가상 테이블

실제 디스크에 저장하지 않고 정의만 데이터사전에 따로 저장하는 테이블이다.
뷰에 대한 수정 결과를 뷰를 정의한 기본 테이블에 적용할 수 있다.

장점

1. 데이터를 보호할 수 있는 보안상 이점이 있다.
2. 사용자가 보고 싶은 데이터만 제공하는 편의성을 제공한다.
3. 복잡한 쿼리를 단순하게 호출한다.
4. 뷰 사용으로 인한 DB 성능상 이점은 없다.

뷰 만들기

CREATE VIEW V_EMP AS (
    SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
      FROM DEPARTMENTS D INNER JOIN EMPLOYEES E
        ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
);

뷰 조회하기

SELECT * FROM V_EMP;

뷰를 이용한 연습

사원번호, 사원명, 부서번호, 부서명, 지역번호, 도로명 주소를 조회하시오.

SELECT V.EMPLOYEE_ID, V.FIRST_NAME, V.LAST_NAME, V.DEPARTMENT_ID, V.DEPARTMENT_NAME, L.LOCATION_ID, L.STREET_ADDRESS
  FROM LOCATIONS L INNER JOIN V_EMP V
    ON L.LOCATION_ID = V.LOCATION_ID;

WITH

1. 자주 사용하거나 복잡한 쿼리문을 WITH 절의 코드 블록으로 등록시켜 놓을 수 있다.
2. WITH 절의 코드 블록은 임시로 저장되기 때문에 곧바로 사용해야 한다.
3. 쿼리문의 가독성이 좋아진다.

연습하기

1. 1 ~ 10번째로 고용된 사원 조회하기

1) 서브쿼리

SELECT EMPLOYEE_ID, HIRE_DATE
FROM (SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE
        FROM EMPLOYEES)
WHERE RN BETWEEN 1 AND 10;

2) WITH

WITH
    MY_SUBQUERY AS (
        SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE ASC) AS RN, EMPLOYEE_ID, HIRE_DATE
          FROM EMPLOYEES
)
SELECT EMPLOYEE_ID, HIRE_DATE
  FROM MY_SUBQUERY
 WHERE RN BETWEEN 1 AND 10;

2. 부서별 부서번호, 부서명, 연봉총액을 조회하기

1) 조인

SELECT MY.DEPARTMENT_ID
    , MY.TOTAL_SALARY
    , D.DEPARTMENT_NAME
 FROM DEPARTMENTS D INNER JOIN (SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
                                  FROM EMPLOYEES
                                 GROUP BY DEPARTMENT_ID) MY
   ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;  

2) WITH

WITH MY_SUBQUERY AS (
    SELECT DEPARTMENT_ID, SUM(SALARY) AS TOTAL_SALARY
      FROM EMPLOYEES
     GROUP BY DEPARTMENT_ID
)
SELECT MY.DEPARTMENT_ID
     , D.DEPARTMENT_NAME
     , MY.TOTAL_SALARY
  FROM DEPARTMENTS D INNER JOIN MY_SUBQUERY MY
    ON D.DEPARTMENT_ID = MY.DEPARTMENT_ID;