[SQL] Oracle - 서브쿼리(SUBQUERY) / 인라인 뷰(INLINE-VIEW)

 

< 서브쿼리 (SUBQUERY) >

 

- 하나의 주된 SQL문(SELECT, INSERT, UPDATE, DELETE, CREATE....)안에 포함된 또 하나의 QEURY문

- 메인 SQL문을 보조해주는 QUERY문

- 가장 안쪽의 서브쿼리부터 순차적으로 결과가 얻어짐

- 서브쿼리문을 통해 얻은 값으로 쿼리문을 처리하기 때문에,

  테이블에서 값이 바뀌면 자동으로 수정됨 => 유지보수에 편리

 

[ 구분 ]

- 서브쿼리를 수행한 결과값이 몇 행, 몇 열이냐에 따라서 분류

- 서브쿼리를 수행한 결과의 반환 행열의 개수에 따라 사용가능한 연산자도 달라짐

단일행 [단일열] 서브쿼리	서브쿼리를 수행한 결과값이 오로지 한 개일 때
다중행 [단일열] 서브쿼리	서브쿼리를 수행한 결과값이 여러 행일 때
[단일행] 다중열 서브쿼리	서브쿼리를 수행한 결과값이 여러 열일 때
다중행 다중열 서브쿼리	서브쿼리를 수행한 결과값이 여러 행, 여러 열일 때

 

---

< 단일행 서브쿼리 (SINGLE ROW SUBQUERY) >

 

- 조회값이 오로지 한 개인 서브쿼리

- 일반연산자(대소비교연산자, 동등비교연산자..) 사용 가능

 

-- 최저 급여를 받는 사원의 사번, 사원명, 직급코드, 급여, 입사일 조회
--1) 최저 급여를 받는 사원
SELECT MIN(SALARY) -- 그룹함수의 결과값은 언제나 단일행!
  FROM EMPLOYEE;

-- 2) 최저급여를 받는 사번, 사원명, 직급코드, 급여, 입사일 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, HIRE_DATE
  FROM EMPLOYEE
 WHERE SALARY = 1380000;

-- 3) 합치기 (최저급여값 자리에, 최저급여값의 결과를 내는 쿼리문을 넣어줌)
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, HIRE_DATE
  FROM EMPLOYEE
 WHERE SALARY = (SELECT MIN(SALARY)
                   FROM EMPLOYEE);
--> 결과는 2)번과 같음

서브쿼리의 결과값 - 단일행

---

< 다중행 서브쿼리 (MULTI ROW SUBQUERY) >

 

- 조회 결과값이 여러 행인 서브쿼리

- IN 과 NOT IN연산자 사용

   IN : 여러개의 결과값 중에서 한 개로 일치하는 값이 있으면 해당

   NOT IN : 결과값 중에서 일치하는 값이 아예 없으면 해당

- ANY 연산자 사용

    > ANY : 여러개의 결과값 중에서 "하나라도" 클 경우 해당 값 조회

    < ANY : 여러개의 결과값 중에서 "하나라도" 작을 경우 해당 값 조회

 

--> 다중행 서브쿼리 + ANY 사용

-- 대리 직급임에도 불구하고 과장 직급의 급여보다 많이 받는 직원 조회 (사번, 이름, 직급명, 급여)

-- 1) 과장 직급의 급여를 조회
SELECT SALARY
  FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND JOB_NAME = '과장';   -- 2200000, 2500000, 37600000

-- 2) 위의 급여보다 높은 급여를 받는 직원들 조회(사번, 이름, 직급명, 급여)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
  FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   -- AND SALARY > 376000 OR SALARY > 2500000 OR SALARY > 22000000;
   AND SALARY > ANY(3760000, 2500000, 2200000); - ()안의 값 중, 하나라도 클 경우

-- 3) 합치기
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
  FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
   AND SALARY > ANY(SELECT SALARY
                      FROM EMPLOYEE E, JOB J
                     WHERE E.JOB_CODE = J.JOB_CODE
                       AND JOB_NAME = '과장')
   AND JOB_NAME = '대리';

서브쿼리의 결과값 - 다중행

--> 다중행 서브쿼리 + IN 사용

-- 각 부서별 최고 급여를 받는 사원의 이름, 직급코드, 급여 조회
-- 1) 각 부서별 최고급여
SELECT MAX(SALARY)
  FROM EMPLOYEE
 GROUP BY DEPT_CODE;  -- 2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000
 
-- 2) 사원의 이름, 직급코드, 급여 -> 위의 급여를 받는 사원들만
SELECT EMP_NAME, JOB_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY IN (2890000, 3660000, 8000000, 3760000, 3900000, 2490000, 2550000);

-- 3) 합치기
SELECT EMP_NAME, JOB_CODE, SALARY
  FROM EMPLOYEE
 WHERE SALARY IN (SELECT MAX(SALARY)
                    FROM EMPLOYEE
                   GROUP BY DEPT_CODE);

서브쿼리의 결과값 - 다중행

---

 

< 다중열 서브쿼리 >

 

- 조회 결과값은 한 행이지만, 나열된 컬럼 수가 여러 개인 서브쿼리

- 비교할 조건을 괄호 안에 나열함

 

-- 하00 사원과 같은 부서코드 AND 직급코드에 해당하는 사원들 조회
-- 1)
SELECT DEPT_CODE, JOB_CODE
  FROM EMPLOYEE
 WHERE EMP_NAME = '하00'; --  D5, J5 -> 한 행인데, 컬럼이 여러개 

-- 2) 부서코드가 D5이면서 직급코드가 J5인 사원 조회
-- 이름 부서코드 직급코드 입사일
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5';

-- 3) 합치기
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
  FROM EMPLOYEE
 WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE EMP_NAME = '하00') 
   AND JOB_CODE = (SELECT JOB_CODE FROM EMPLOYEE WHERE EMP_NAME = '하00');
   -- 이렇게 합치면 굉장히 번거롭지만....
            
--> 다중열 서브쿼리
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
  FROM EMPLOYEE
 WHERE (DEPT_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE
                                  FROM EMPLOYEE
                                 WHERE EMP_NAME = '하00');

서브쿼리 결과값 - 다중열 [단일행]

 

---

< 다중행 다중열 서브쿼리 >

 

- 조회 결과값이 여러 행, 여러 컬럼인 서브쿼리

- 비교할 조건을 괄호 안에 나열하고, 해당하는 범위를 지정

 

-- 각 직급별 최소 급여를 받는 사원들 조회(사번, 이름, 직급코드, 급여)
-- 1) 각 직급별 최소 급여 조회
SELECT JOB_CODE, MIN(SALARY)
  FROM EMPLOYEE
 GROUP BY JOB_CODE;
 
-- 2) 위의 목록들 중에 일치하는 사원
-- 이름
SELECT EMP_NAME
  FROM EMPLOYEE
 WHERE JOB_CODE = 'J2' AND SALARY = 3700000
    OR JOB_CODE = 'J7' AND SALARY = 1380000
    OR JOB_CODE = 'J3' AND SALARY = 3400000
    OR JOB_CODE = 'J6' AND SALARY = 2000000
    OR JOB_CODE = 'J5' AND SALARY = 2200000
    OR JOB_CODE = 'J1' AND SALARY = 8000000
    OR JOB_CODE = 'J4' AND SALARY = 1550000;
-- 이렇게라도 간단하게...
SELECT EMP_NAME
  FROM EMPLOYEE
 WHERE (JOB_CODE, SALARY) IN (('J2', 3700000), ('J7', 1380000), ('J3', 3400000),
                             ('J6', 2000000), ('J5', 2200000), ('J1', 8000000),
                             ('J4', 1550000));

--> 다중행다중열 서브쿼리
SELECT EMP_NAME
  FROM EMPLOYEE
 WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                                FROM EMPLOYEE
                               GROUP BY JOB_CODE);

서브쿼리의 결과값 - 다중행 다중열

 

---

< 인라인 뷰 (INLINE-VIEW) >

 

- FROM절에 서브쿼리를 제시하여, 서브쿼리를 수행한 결과(Result Set)를 테이블 대신 사용함

- 단, 제시한 Result Set에 해당하는 값만 SELECT문에서 조회 가능함

- 주로 TOP-N분석*시 사용

* TOP-N 분석 : 데이터베이스 상에 존재하는 자료 중 최상위 몇 개의 자료를 추려내는 것

- ROWNUM*을 이용하면 편리

* ROWNUM : 오라클에서 제공해주는 컬럼. 조회된 순서대로 1부터 차례대로 순번을 부여해주는 컬럼

-- 전 직원 중 급여가 가장 높은 5명
--> ORDER BY로 정렬한 테이블을 가지고 / ROWNUM 순번 부여 후에 / 조건 ROWNUM <= 5
SELECT ROWNUM, EMP_NAME, SALARY
  FROM (SELECT *
          FROM EMPLOYEE
         ORDER BY SALARY DESC)  
 WHERE ROWNUM <= 5;

 

---

 

< 순위 함수 >

 

- 공동순위 처리방식에 따라 RANK()와 DENSE_RANK()가 나뉨

- RANK() OVER : 공동 순위가 N명이면 그 다음 순위는 N개 제외한 순위

- DENSE_RANK() OVER : 공동 순위가 N명이라도 다음 순위로 이어감

 

[ 표현법 ]

RANK() OVER(정렬기준)

DENSE_RANK() OVER(정렬기준)

 

-- 사원들의 급여가 높은 순서대로 순위를 매겨서, 사원명, 급여, 순위 조회
SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
  FROM EMPLOYEE;
  
SELECT EMP_NAME, SALARY, DENSE_RANK() OVER(ORDER BY SALARY DESC) "순위"
  FROM EMPLOYEE;

--> 인라인 뷰 사용
SELECT *
  FROM (SELECT EMP_NAME, SALARY, RANK() OVER(ORDER BY SALARY DESC) "순위"
        FROM EMPLOYEE)
 WHERE "순위" <= 5;

RANK() 처리 결과, 공동 19위가 두 명 다음 순위는 21위

DENSE_RANK() 처리 결과, 공동 19위가 두 명 다음 순위는 20위