오라클 SQL 문법 공부20- INLINE VIEW(인라인 뷰), 스칼라 서브쿼리

서론

서브쿼리에 이어 테이블처럼 사용되는 인라인 뷰에 대해서 알아보고자 한다. 보통 서브쿼리와 인라인 뷰가 괄호안에 있는 쿼리라 다 같은 서브쿼리라 생각할 수도 있지만 부르는 명칭은 확실히 다르다. WHERE절에 사용되는 서브쿼리는 말 그대로 서브쿼리이며 FROM 절에 테이블 명 대신 사용되는 서브쿼리형식이 바로 인라인 뷰라고 한다.

본론

INLINE VIEW(인라인 뷰)

--INLINE View (인라인 뷰)
--vIEW는 필요할 때 생성한 후 계속 여러 번 반복해서 재사용할 수 있습니다.
--그러나 여러 번 사용할 필요 없이 1회만 필요한 View일 경우 SQL 문장의 FROM 절에 View 의
--서브쿼리 부분을 바로 적어주면 되며 이런 1회용 View를 INLINE View라고 합니다.

--student 테이블과 department 테이블을 사용하여 학과별로 학생들의 최대 키를 갖는 학생의
--이름 학년 키 학과이름을 출력하세요
--1)다중컬럼 서브쿼리
SELECT *
  FROM STUDENT S,
       DEPARTMENT D
 WHERE (S.DEPTNO1,
               S.HEIGHT) IN (SELECT DEPTNO1,
               MAX(HEIGHT)
          FROM STUDENT
         GROUP BY DEPTNO1)
   AND S.DEPTNO1 = D.DEPTNO;
--각 그룹별 대소비교는 다중컬럼 서브쿼리로는 불가능(인라인 뷰 대체)

--2) 인라인 뷰
--다중컬럼 서브쿼리로 해결하지 못하는 문제들을 인라인 뷰로 해결 가능
SELECT *
  FROM STUDENT S,
       (SELECT DEPTNO1,
               MAX(HEIGHT) AS "MAX_HEIGHT"
          FROM STUDENT
         GROUP BY DEPTNO1) I
 WHERE S.DEPTNO1 = I.DEPTNO1
   AND S.HEIGHT = I.MAX_HEIGHT;

--1. PROFESSOR 테이블에서 각 학과별 최대 연봉을 갖는 교수의 이름, 교수번호, 학과명, PAY를 출력하세요
--(다중컬럼, 인라인뷰)
SELECT P.*,
       D.DNAME
  FROM PROFESSOR P,
       DEPARTMENT D
 WHERE (P.DEPTNO,
               P.PAY) IN (SELECT DEPTNO,
               MAX(PAY)
          FROM PROFESSOR
         GROUP BY DEPTNO)
   AND P.DEPTNO = D.DEPTNO;
 
SELECT P.*,
       D.DNAME
  FROM PROFESSOR P,
       DEPARTMENT D,
       (SELECT DEPTNO,
               MAX(PAY) AS "MAX_PAY"
          FROM PROFESSOR
         GROUP BY DEPTNO) I
 WHERE P.DEPTNO = I.DEPTNO
   AND P.PAY = I.MAX_PAY
   AND P.DEPTNO = D.DEPTNO;

--2. STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요.
--(다중컬럼, 인라인뷰)
SELECT *
  FROM STUDENT S,
       DEPARTMENT D
 WHERE (S.GRADE,
               S.HEIGHT) IN (SELECT GRADE,
               MAX(HEIGHT)
          FROM STUDENT
         GROUP BY GRADE)
   AND S.DEPTNO1 = D.DEPTNO;


SELECT *
  FROM STUDENT S,
       (SELECT GRADE,
               MAX(HEIGHT) AS "MAX_HEIGHT"
          FROM STUDENT
         GROUP BY GRADE) I
 WHERE S.GRADE = I.GRADE
   AND S.HEIGHT = I.MAX_HEIGHT;

인라인뷰와 같은 형태를 보통 다 통틀어서 서브쿼리라고도 하는데 정확한 표현은 인라인 뷰가 맞다. 전 장에서 해보았던 서브쿼리 같은 경우에는 WHERE절에서 또 다른 쿼리를 괄호로 넣어 특정 데이터들을 가져오게 하는 방법이었다면 인라인뷰는 애초에 필터가 된 데이터 즉 테이블을 가지고 조인형식으로 사용하는 방법이다. 나중에 뷰라는 것도 얘기하겠지만 뷰 처럼 논리적인 테이블을 가지고 한다는 개념이기 때문에 인라인 뷰라고 한다.

실제 테이블 대신에 쿼리를 통해 걸러진 혹은 얻어진 논리적인 테이블의 데이터를 가지고 쿼리를 짠다고 보면 이해하기 쉬울 것이다.

상호연관 서브쿼리

--상호 연관 Sub Query
--상호 연관 Sub Query란 Main Query 값을 Sub Query에 주고 Sub Query 를 수행 한 후
--그 결과를 다시 Main Query로 반환해서 수행하는 Sub Query 를 말합니다.

--PROFESSOR 테이블에서 각 부서별 최대연봉을 갖는 교수의 이름, 교수번호, PAY를 출력하세요.

SELECT NAME, PROFNO, PAY
FROM PROFESSOR
WHERE (DEPTNO, PAY) IN (SELECT DEPTNO, MAX(PAY) FROM PROFESSOR GROUP BY DEPTNO);
--다중 쿼리 역시 잘 됨

SELECT NAME,
       PROFNO,
       PAY
  FROM PROFESSOR P1
 WHERE PAY IN (SELECT MAX(PAY)
          FROM PROFESSOR P2
         GROUP BY DEPTNO)
   AND P1.DEPTNO = P2.DEPTNO;
--P2.DEPTNO가 메인쿼리의 FROM 절에 선언되지 않았으므로 메인쿼리의 WHERE 절에서 사용할 수 없다

SELECT NAME,
       PROFNO,
       PAY
  FROM PROFESSOR P1
 WHERE PAY IN (SELECT MAX(PAY)
          FROM PROFESSOR P2
         WHERE P1.DEPTNO = P2.DEPTNO
         GROUP BY P2.DEPTNO);
--서브쿼리로 들어 갔을때 앞에서 선언 되어 있어서 P1 사용이 가능

--[상호연관 서브쿼리 SQL 실행순서 ]
--step1) 메인쿼리의 FROM 절에 있는 PROFESSOR(P1) 테이블 읽기
--step2) PROFESSOR 의 첫 행의 PAY 확인
--step3) 서브쿼리의 FROM 절에 있는 PROFESSOR(P2) 테이블 읽기
--step4) 메인쿼리의 첫 행의 DEPTNO를 서브쿼리에 전달
--step5) 전달받은 DEPTNO별 MAX(PAY) 계산
--step6) 서브쿼리에서 계산한 MAX(PAY)를 메인쿼리에 전달
--step7) 메인쿼리의 PAY와 서브쿼리의 MAX(PAY) 비교
--step8) 나머지 모든 행에 대해 위의 STEP 반복

SELECT NAME, PROFNO, PAY
FROM PROFESSOR P1
WHERE PAY IN (SELECT MAX(PAY) FROM PROFESSOR P2 WHERE 
P1.DEPTNO = P2.DEPTNO);

-- 애초에 그룹바이까지 할 필요는 없어보였다.
-- 왜냐하면 해당 DEPTNO에 대해서 MAX 값을 구하기 때문에
-- 알아서 같은 그룹(DEPTNO가 같은)의 MAX값만을 추린 PAY가 나오기 때문
-- 만약 PROFNO가 다른데 PAY 값이 동일한 값이 존재할경우 
-- 이럴경우에는 PROFNO 까지 비교해주어야 할듯(위의 쿼리로는 PAY값이 동일하면 이상한 값이 나올 수도 있다)

상호연관 서브쿼리는 메인쿼리에서 사용되는 컬럼값을 서브쿼리에서 사용할 수가 있다. 위의 상호연관 서브쿼리 실행순서가 중요하다. 메인쿼리의 테이블을 먼저 읽기 때문에 해당 테이블의 컬럼을 서브쿼리 안에서 (혹은 인라인 뷰 안에서) 애칭을 통하여 사용할 수 있다. 하지만 반대로 메인쿼리에서 서브쿼리 안에 있는 컬럼들을 사용할 수는 없다. 왜냐하면 서브쿼리는 괄호안 자체에서 수행 후 끝나버리기 때문에 메인쿼리쪽에서는 서브쿼리의 데이터를 알 수가 없다.

단지 인라인 뷰로써 나온 논리적 데이터들이나 서브쿼리를 통해 나온 SELECT 절의 값들만 메인쿼리절에서 사용 할 수가 있다는 말이다.

스칼라 서브쿼리

--스칼라 서브쿼리 :  SELECT 절에 사용되는 서브쿼리
--1)컬럼의 전체 내용을 특정 값으로 대체하고자 할 경우 사용

--EX1)EMP 테이블에서 DEPTNO가 10인 DEPTNO를 SMITH의 DEPTNO와
--같게하여 DEPTNO를 출력하여라

SELECT ENAME, CASE DEPTNO WHEN 10 THEN (SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH')
ELSE DEPTNO END AS "DEPTNO" FROM EMP;

--2)JOIN의 대체
--EX2) EMP 테이블과 DEPT 테이블을 이용하여 각 직원의 사번, 이름, 그리고 부서명을 함께 출력하여라.
SELECT E.EMPNO, E.ENAME, D.DNAME
FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO;

SELECT E.EMPNO, E.ENAME, (SELECT DNAME FROM DEPT WHERE E.DEPTNO = DEPTNO)
FROM EMP E;

--EX3) STUDENT 테이블과 PROFESSOR 테이블을 이용하여 각 학생의 이름과 지도교수 이름을 출력하여라
SELECT NAME, (SELECT NAME FROM PROFESSOR WHERE PROFNO = S.PROFNO) AS "P.NAME" FROM STUDENT S;
--메인쿼리 WHERE절에 조건이 없어 모두 출력이 되니 아우터 조인 할 필요가 없다.

스칼라 서브쿼리는 보통 상호연관 서브쿼리 방식을 이용하여 해당 메인컬럼의 값을 다른 테이블에서(서브쿼리) 참조하여 데이터를 가져와 출력해주는 형태로 SELECT 절에서 사용되는 상호연관 서브쿼리를 말한다. 성능상 좋은 방법은 아니기 때문에 사용하는데에 있어 주의가 필요하다.

실습문제

--1. GOGAK 테이블과 GIFT 테이블을 이용하여
--고객의 마일리지 포인트별로 받을 수 있는 최대상품을 조회
--하여 고객의 이름과 상품 명을 출력하세요. (스칼라 서브쿼리 이용)
SELECT G.GNAME, (SELECT GNAME FROM GIFT WHERE G.POINT BETWEEN G_START AND G_END) FROM GOGAK G;

--2. DEPT2 테이블을 이용하여 상위 부서명을 다음과 같이 출력하세요.
--경영지원부의 상위부서는 사장실 입니다.
--단, 상위부서가 없는 경우는 본인부서명이 출력되도록 하여라. (스칼라 서브쿼리)
SELECT D.DNAME ||'의 상위부서는 ' || NVL((SELECT D.DNAME FROM DEPT2 WHERE DCODE = D.PDEPT), D.DNAME) || ' 입니다.'
   FROM DEPT2 D;
   

--3. STUDENT, EXAM_01, HAKJUM 테이블을 이용하여 각 학생의 이름, 점수, 학점을 출력하도록 하여라
--(스칼라 서브쿼리)

SELECT NAME,
       (SELECT TOTAL
          FROM EXAM_01
         WHERE STUDNO = S.STUDNO),
       (SELECT GRADE
          FROM HAKJUM
         WHERE (SELECT TOTAL
                  FROM EXAM_01
                 WHERE STUDNO = S.STUDNO) BETWEEN MIN_POINT AND MAX_POINT)
  FROM STUDENT S;

--4. EMPLOYEES테이블에서 더 큰 범위의 JOB(앞의 두자)를 이용하여 큰범위 JOB별로 평균연봉을 구하고
--평균보다 높은 사람의 FIRST_NAME, SALARY, 큰범위JOB, 그사람의 상위 관리자도 동시에 나타내어라
--1)인라인뷰
SELECT E1.FIRST_NAME,
       E1.SALARY,
       E3.AVG_SAL,
       E3.JOB,
       E2.FIRST_NAME
  FROM EMPLOYEES E1,
       EMPLOYEES E2,
       (SELECT SUBSTR(JOB_ID, 1, 2) AS "JOB",
               AVG(SALARY) AS "AVG_SAL"
          FROM EMPLOYEES
         GROUP BY SUBSTR(JOB_ID, 1, 2)) E3
 WHERE E1.MANAGER_ID = E2.EMPLOYEE_ID(+)
   AND SUBSTR(E1.JOB_ID, 1, 2) = E3.JOB
   AND E1.SALARY > E3.AVG_SAL;
 
--2)상호연관
SELECT E1.FIRST_NAME,
       E1.SALARY,
       SUBSTR(E1.JOB_ID, 1, 2),
       E2.FIRST_NAME
  FROM EMPLOYEES E1,
       EMPLOYEES E2
 WHERE E1.SALARY > (SELECT AVG(SALARY) AS "AVG_SAL"
          FROM EMPLOYEES
         WHERE SUBSTR(JOB_ID, 1, 2) = SUBSTR(E1.JOB_ID, 1, 2))
   AND E1.MANAGER_ID = E2.EMPLOYEE_ID(+);

--3)스칼라
SELECT *
  FROM (SELECT E1.FIRST_NAME,
                       CASE
                         WHEN E1.SALARY > (SELECT AVG(SALARY) AS "AVG_SAL"
                  FROM EMPLOYEES
                 WHERE SUBSTR(JOB_ID, 1, 2) = SUBSTR(E1.JOB_ID, 1, 2)) THEN E1.SALARY
                       END AS "SALARY",
               (SELECT FIRST_NAME
                  FROM EMPLOYEES
                 WHERE E1.MANAGER_ID = EMPLOYEE_ID) AS "E2.FIRST_NAME"
          FROM EMPLOYEES E1)
 WHERE SALARY IS NOT NULL;

--5. EMP 테이블을 이용하여 부서에서 나보다 입사년도가 빠른 사람의 수를 출력하세요.
--단, 선배수가 많은 사람 순으로 정렬하세요.
--1)조인
SELECT E1.EMPNO,
       COUNT(E2.EMPNO) AS "COUNT" 
  FROM EMP E1,
       EMP E2
 WHERE TO_CHAR(E1.HIREDATE, 'YYYY') > TO_CHAR(E2.HIREDATE(+), 'YYYY')
 GROUP BY E1.EMPNO
 ORDER BY "COUNT" DESC;
 
--2)스칼라 서브쿼리
SELECT E1.EMPNO,
       (SELECT COUNT(EMPNO)
          FROM EMP
         WHERE TO_CHAR(HIREDATE, 'YYYY') < TO_CHAR(E1.HIREDATE, 'YYYY')) AS "COUNT"
  FROM EMP E1
 ORDER BY "COUNT" DESC;

결론

인라인 뷰와 스칼라 서브쿼리에 대해서 알아보았다. 이제 서브쿼리쪽은 모두 끝이 났다. 자주 써보면서 개념을 잘 이해해두면 되겠다. 특히나 상호연관 서브쿼리쪽의 메인쿼리와 서브쿼리사이의 관계를 정확히 이해를 해야 오류가 없는 쿼리를 짤 수 있을 것이다.