[Database Programming] 8. INLINE VIEW

VIEW = 임시 테이블

- 뷰를 만드는 목적 : 귀찮아서

- 매번 쓰는 QUERY들을 매번안쓰기위해 임시 테이블로 만들어 둔 것

- 실무에서는 잘 사용하지 않음(테이블의 구조변경이 빈번히 발생하기 때문. 바뀔때마다 뷰를 수정하기 힘들기 때문)

- 쿼리내부에서 잠깐동안 쓸 뷰를 만들고 지우는것 = INLINE VIEW

- 조인만으로 풀리지않는 문제를 INLINE VIEW를 통해서 처리함

- 보통 INLINE VIEW내부에는 복잡한 JOIN쿼리가 작성됨 -> 그 결과를우리가 사용할것!

- INLINE VIEW는 FROM 테이블에 (위치) 쓴다 ( FROM에 들어가는 소괄호 : 인라인 뷰, 소괄호 안에는 새로운 SELECT 쿼리 써주면 됨 )

 

 

인라인뷰 문법

SELECT COLUMN ...
FROM (SELECT *
		FROM TABLE) A
INNER JOIN(SELECT *
			FROM TABLE) B
WHERE ....

 

인라인뷰 사용예제

- 사원의정보를 INLINE VIEW를이용해 조회하기

SELECT EMPLOYEE_ID
	 --, SALARY : FROM() 에 있는 INLINE VIEW에 SALARY라는 컬럼은 없기때문에 사용할 수 없음
  FROM (-- 1ST.INLINE VIEW로 이용할 SELECT문장을 먼저작성
		SELECT EMPLOYEE_ID
			 , FIRST_NAME
			 , LAST_NAME
		  FROM EMPLOYEES) TEMP_E
;

 

 

오라클만의 특별한기능 1. ROWNUM

-- EMPLOYEES테이블에서 ROW 3개만 조회한다
SELECT *
  FROM EMPLOYEES 
 WHERE EMPLOYEE_ID <= 102
 ;
-- 'ROWNUM' 을 이용한 풀이
SELECT *
  FROM EMPLOYEES 
 WHERE ROWNUM <= 3
 ;

'EMPLOYEES테이블에서 연봉을 가장많이 받는 사원 3명만 조회한다' 라는 예제를 풀 때, 
아래와 같이 푼다면 : 1. SELECT, FROM , WHERE 을 먼저 실행하고, 2. 그 결과를 가지고 ORDER BY(정렬) 하기때문에 ORDER BY는 가장 마지막에 처리됨 --> INLINE VIEW를 이용해야한다!

SELECT *
  FROM EMPLOYEES 
 WHERE ROWNUM <= 3
 ORDER BY SALARY DESC
  ;

'EMPLOYEES테이블에서 연봉을 가장많이 받는 사원 3명만 조회한다' 
- 인라인뷰 & ROWNUM 사용한 풀이

SELECT * 
  FROM (--EMPLOYEES테이블에서 연봉을 기준으로 내림차순 정렬  
		SELECT *
		  FROM EMPLOYEES 
		 ORDER BY SALARY DESC) ORDERED_EMPLOYEES
 WHERE ROWNUM <= 3		 
;

 

 

오라클만의 특별한기능 2. NVL

- 원하는 데이터의 값이 NULL값이 아닌 다른것(ex.0)으로 바꾸고 싶다면 : NVL() 사용하기

SELECT 10000
	 , NVL(NULL, 0) --NULL일경우 숫자 0으로 변경하고싶다면 : NVL() 사용하기
  FROM DUAL 
 ;

 

인라인뷰 예제2

- 연봉의 총합이 가장 높은 부서의 모든 정보를 조회한다

- 접근방법: 부서'별' 연봉의 총 합 -> 연봉총합 으로 내림차순 정렬 -> 정렬된 데이터에서 1개만 조회 -> 부서의 정보 알기위해 JOIN

SELECT D.*
	 , ORDERED_DEPT_SAL.SUM_SALARY --부서별 연봉 총 합
  FROM ( SELECT D.DEPARTMENT_ID -- 부서별 연봉의 총 합
   		 --NVL() : 총합이 NULL값일경우 0으로 바꾸는 함수
		 , NVL(SUM(E.SALARY), 0) AS SUM_SALARY 
		  FROM DEPARTMENTS D
		  LEFT JOIN EMPLOYEES E
		    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID 
		 -- WHERE E.EMPLOYEE_ID IS NOT NULL
		 GROUP BY D.DEPARTMENT_ID 
		 -- 내림차순 정렬 (단, SUM_SALARY가 NULL값이라면 제외시켜줘야함)
		 ORDER BY SUM_SALARY DESC) ORDERED_DEPT_SAL 
--부서의 정보 알기위해 JOIN 해야함
INNER JOIN DEPARTMENTS D
   ON D.DEPARTMENT_ID = ORDERED_DEPT_SAL.DEPARTMENT_ID
WHERE ROWNUM = 1
;

인라인뷰 예제3

- 사원의 수가 가장 많은 직무의 모든 정보 상위 3개를 조회한다

- 접근방법: 직무별 사원의 수 구하기 -> 사원의 수로 정렬 -> 그중 하나만 가지고와서 직무와 JOIN -> 3개조회

SELECT J.*
	 , ORDERED_JOB_EMP_COUNT.E_COUNT
  FROM (SELECT J.JOB_ID 
			 , NVL(COUNT(E.EMPLOYEE_ID), 0) AS E_COUNT
		  FROM JOBS J
	      LEFT JOIN EMPLOYEES E
	        ON J.JOB_ID = E.JOB_ID 
		 GROUP BY J.JOB_ID 
		 ORDER BY E_COUNT DESC) ORDERED_JOB_EMP_COUNT
 INNER JOIN JOBS J
    ON J.JOB_ID = ORDERED_JOB_EMP_COUNT.JOB_ID
 WHERE ROWNUM <= 3
;

 

인라인뷰 예제4

- 가장 많은 사원이 근무중인 부서의 모든 정보를 조회한다 (인라인뷰가 2번씩 필요할 것)

-- 부서별 사원의 수 (부서에 사원이 존재하지 않는다면 0으로 조회한다 = 메인테이블 : 부서)

SELECT D.*
  FROM (-- 사원의 수로 내림차순 정렬
		SELECT D.DEPARTMENT_ID 
			 , COUNT(E.EMPLOYEE_ID) AS E_COUNT --COUNT집계함수에 별칭 주는 방법 : AS+별칭명 작성 (AS 생략가능) 
		  FROM DEPARTMENTS D
		  LEFT JOIN EMPLOYEES E
		    ON D.DEPARTMENT_ID  = E.DEPARTMENT_ID 
		 GROUP BY D.DEPARTMENT_ID 
		 ORDER BY E_COUNT DESC) ORDERED_DEPT_EMP_COUNT
 INNER JOIN DEPARTMENTS D --INLINE VIEW와 DEPARTMENTS 조인한다
    ON D.DEPARTMENT_ID = ORDERED_DEPT_EMP_COUNT.DEPARTMENT_ID
-- 정렬된 데이터에서 1개만 조회
 WHERE ROWNUM = 1
;