05_OracleDB SQL : SUBQUERY(서브쿼리)

---

SUBQUERY

 

하나의  SELECT 문장의 절 안데 포함된 또 하나의 SELECT 문장

메인쿼리가 실행되기 이전에 한번만 실행됨

비교연산자의 오른쪽에 기술해야 하고 괄호로 묶어야 함

서브쿼리와 비교할 항목은 서브쿼리의 SELECT한 항목의 개수와 자료형이 일치해야 함

SELECT, FROM, WHERE, HAVING, ORDER BY절에서 사용 가능

 

 

---

 

1 .  단일행 서브쿼리

• 서브쿼리의 조회 결과 값이 1개 행일 때
• 단일행 서브쿼리는 앞에 일반 비교 연산자를 사용 (>, <, >=, <=, =, !=/^=/<>)

(1)노옹철 사원의 급여보다 많이 받는 직원의
   사번, 이름, 부서, 직급, 급여를 조회하기

SELECT
       EMP_ID
     , EMP_NAME
     , DEPT_CODE
     , JOB_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY > (SELECT SALARY
                   FROM EMPLOYEE
                  WHERE EMP_NAME = '노옹철'
                );
                
(2) 가장 적은 급여를 받는 직원의
    사번, 이름, 직급, 부서, 급여, 입사일을 조회하기

SELECT
       EMP_ID
     , EMP_NAME
     , JOB_CODE
     , DEPT_CODE
     , SALARY
     , HIRE_DATE
  FROM EMPLOYEE
 WHERE SALARY = (SELECT 
                        MIN(SALARY)
                   FROM EMPLOYEE
                );

 

 

2 . 다중행 서브쿼리

• 서브쿼리의 조회 결과 값의 행이 여러 개 일때
• 다중행 서브쿼리 앞에서는 일반 비교 연산자를 사용할 수 없으므로 사용가능 한 연산자를 사용해야 함.

사용가능 연산자	기능
IN / NOT IN	여러 개의 결과 값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면의 의미
> ANY / < ANY	여러 개의 결과 값 중에서 한 개라도 큰 | 작은 경우 가장 작은 값보다 큰가? | 가장 큰 값보다 작은가?
> ALL / < ALL	모든 값보다 큰 | 작은 경우 가장 큰 값보다 큰가? | 가장 작은 값보다 작은가?
EXIST / NOT EXIST	서브쿼리에만 사용하는 연산자로 값이 존재하는가? | 존재하지 않는가?

 

• IN 사용한 예제

부서별 최고 급여를 받는 직원의 이름, 직급, 부서, 급여 조회
SELECT
       EMP_NAME
     , JOB_CODE
     , DEPT_CODE
     , SALARY
  FROM EMPLOYEE
 WHERE SALARY IN (SELECT 
                        MAX(SALARY)
                   FROM EMPLOYEE
                  GROUP BY DEPT_CODE
                 );

 

• > ANY 사용한 예제

대리 직급의 직원들 중에서 
과장 직급의 최소 급여보다 많이 받는 직원의
사번, 이름, 직급명, 급여를 조회하세요.

SELECT
       EMP_ID
     , EMP_NAME
     , JOB_NAME
     , SALARY
  FROM EMPLOYEE
  JOIN JOB USING(JOB_CODE)
 WHERE JOB_NAME = '대리'
   AND SALARY > ANY (SELECT
                            SALARY
                       FROM EMPLOYEE
                       JOIN JOB USING(JOB_CODE)
                      WHERE JOB_NAME = '과장'
                    );

 

 

• > ALL 사용 한 예제

차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의
사번, 이름, 직급명, 급여를 조회하세요.
SELECT
       EMP_ID
     , EMP_NAME
     , JOB_NAME
     , SALARY
  FROM EMPLOYEE
  JOIN JOB USING(JOB_CODE)
 WHERE JOB_NAME = '과장'
   AND SALARY > ALL (SELECT
                            SALARY
                       FROM EMPLOYEE
                       JOIN JOB USING(JOB_CODE)
                      WHERE JOB_NAME = '차장'
                    );

 

 

 

3 . 다중열 서브쿼리

• 서브쿼리의 조회 결과 값의 컬럼이 여러 개 일 때

퇴직한 여직원과 같은 부서, 같은 직급에
해당하는 사원의 이름, 직급, 부서, 입사일을 조회

SELECT
       EMP_NAME
     , JOB_CODE
     , DEPT_CODE
     , HIRE_DATE
  FROM EMPLOYEE
 WHERE (DEPT_CODE, JOB_CODE) = (SELECT
                                       DEPT_CODE
                                     , JOB_CODE
                                  FROM EMPLOYEE
                                 WHERE SUBSTR(EMP_NO, 8, 1) = 2
                                   AND ENT_YN = 'Y'
                                );

 

 

 

4. 다중행다중열 서브쿼리

• 조회 결과 행 수와 열 수가 여러 개 일 때

 

 

 

5 . 상[호연]관 서브쿼리

• 서브쿼리가 만든 결과 값을 메인쿼리가 비교 연산
• 상관 서브쿼리는 메인쿼리가 사용하는 테이블의 값을 서브쿼리가 이용해서 결과를 만듬
• 메인쿼리 테이블의 값이 변경 되면, 서브쿼리의 결과 값도 바뀌게 됨

관리자 사번이 EMPLOYEE 테이블에 존재하는 직원에 대한 조회

SELECT
       E.EMP_ID
     , E.EMP_NAME
     , E.DEPT_CODE
     , E.MANAGER_ID
  FROM EMPLOYEE E
 WHERE EXISTS (SELECT
                      E2.EMP_ID
                 FROM EMPLOYEE E2
                WHERE E.MANAGER_ID = E2.EMP_ID
               );

 

 

6 .  스칼라 서브쿼리

• 상관쿼리 이면서 결과값이 한 개인 서브쿼리 (단일행 서브쿼리 + 상관쿼리)
• WHERE절과 SELECT절에서 스칼라 서브쿼리 사용

(1) WHERE절에서 사용

동일 직급의 급여 평균보다 급여를 많이 받고 있는
직원의 직원명, 직급코드, 급여를 조회하세요

SELECT
       EMP_NAME
     , JOB_CODE
     , SALARY
  FROM EMPLOYEE E
 WHERE SALARY > (SELECT TRUNC(AVG(E2.SALARY), -5)
                   FROM EMPLOYEE E2
                  WHERE E.JOB_CODE = E2.JOB_CODE
                 );
                 
(2)SELECT절에서 사용

모든 사원의 사번, 이름, 관리자 사번, 관리자명을 조회하세요
SELECT
       EMP_ID
     , EMP_NAME
     , MANAGER_ID
     , NVL((SELECT EMP_NAME
              FROM EMPLOYEE E2
             WHERE E.MANAGER_ID = E2.EMP_ID
            ), '없음') 관리자명
  FROM EMPLOYEE E
 ORDER BY 1;

 

(2) SELECT절 예제 결과

 

 

7 . 인라인뷰

• FROM절에서 서브쿼리를 사용
• 서브쿼리의 결과(RESULT SET)가 테이블 대신 사용
• 별칭을 사용했다면 해당 별칭으로 조회해야 함

인라인뷰로 직급별 평균 급여를 계산한 테이블을 만들고
EMPLOYEE와 JOIN할 때 본인의 급여와 동일하면 조인하게 조건을 줘서
직급별 평균 급여에 맞는 급여를 받고 있는 직원의 경우 조회되는 구문

SELECT
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM (SELECT
               JOB_CODE
             , TRUNC(AVG(SALARY), -5) AS JOBAVG
          FROM EMPLOYEE
         GROUP BY JOB_CODE) V
  JOIN EMPLOYEE E ON (V.JOBAVG = E.SALARY AND V.JOB_CODE = E.JOB_CODE)
  (직급별 평균 SALARY과 EMPLOYEE 월급과 연결 서브쿼리 잡코드와 EMPLOYEE 잡코드 연결)
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 ORDER BY J.JOB_NAME;
 
 별칭 사용
 SELECT
        EMP_NAME
      , 부서명
      , 직급명 
FROM ( SELECT
                EMP_NAME
              , DEPT_TITLE AS 부서명
              , JOB_NAME AS 직급명
          FROM EMPLOYEE
          LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
          JOIN JOB USING(JOB_CODE))
  WHERE 부서명 ='인사관리부';

 

 

 인라인뷰를 사용한 TOP-N 분석

• ORDER BY한 결과에 ROWNUM을 붙임
• ROWNUM은 행 번호를 의미

SELECT
         ROWNUM
       , EMP_NAME
       , SALARY
   FROM EMPLOYEE
  ORDER BY SALARY DESC; 
=> WHERE절에서 ROWNUM이 결정되어 급여를 많이 받는 순서와 관계 없는 번호를 가짐
   


데이터를 먼저 추출(인라인뷰 사용)하고 ROWNUM을 사용해야함
SELECT 
       ROWNUM
     , V.EMP_NAME
     , V.SALARY
  FROM (SELECT E.*
          FROM EMPLOYEE E
         ORDER BY E.SALARY DESC
        ) V
 WHERE ROWNUM <= 5;
 
 
 
 11위에서 15위까지 조회 
 SELECT 
         V2.RNUM
       , V2.EMP_NAME
       , V2.SALARY
   FROM (SELECT
                ROWNUM RNUM
              , V.EMP_NAME
              , V.SALARY
           FROM (SELECT E.*
                    FROM EMPLOYEE E
                  ORDER BY E.SALARY DESC
                 ) V
         ) V2
   WHERE RNUM BETWEEN 11 AND 15;
   
   
   
 STOPKEY 이용
 SELECT 
         V2.RNUM
       , V2.EMP_NAME
       , V2.SALARY
   FROM (SELECT
                ROWNUM RNUM
              , V.EMP_NAME
              , V.SALARY
           FROM (SELECT E.*
                    FROM EMPLOYEE E
                  ORDER BY E.SALARY DESC
                 ) V
            WHERE ROWNUM < 11  <-STOPKEY
         ) V2
   WHERE RNUM BETWEEN 6 AND 10;

 

 

 

• 인라인뷰/ ROWNUM 예제

급여 평균 3위 안에 드는 부서의 
부서코드와 부서명, 평균 급여를 조회하세요
 
 SELECT
        V.DEPT_CODE
      , V.DEPT_TITLE
      , V.평균급여 
     FROM(SELECT
                 E.DEPT_CODE
               , D.DEPT_TITLE
               , AVG(E.SALARY) 평균급여
           FROM EMPLOYEE E
           JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
          GROUP BY E.DEPT_CODE, D.DEPT_TITLE
          ORDER BY AVG(E.SALARY) DESC
          ) V
 WHERE ROWNUM <= 3;
 
 인라인뷰에 평균급여 계산과 높은 순으로 정렬하기 위한 구문 작성
 미리 정렬하지 않으면 ROWNUM시 높은 순으로 정렬 되지 않기 때문

 

 

 

 

8.  RANK() /  DENSE_RANK()

 

• RANK() : 동일한 순위 이후의 등수를 동일한 인원수만큼 건너 뛰고 다음 순위를 계산

 SELECT
        EMP_NAME
      , SALARY
      , RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

 

• DENSE_RANK() : 중복 되는 순위 이후의 등수를 이후 등수로 처리

SELECT
        EMP_NAME
      , SALARY
      , DENSE_RANK() OVER(ORDER BY SALARY DESC) 순위
  FROM EMPLOYEE;

 

 

9 . WITH 이름 AS (쿼리문)

• 서리쿼리에 이름을 붙여주고  붙여준 이름으로 사용 가능
• 인라인뷰로 사용 될 서브쿼리에서 이용되며, 같은 서브쿼리가 여러 번 사용 될 경우 중복해서 작성하지 않아도 되고, 실행 속도도 빨라짐

 WITH
        TOPN_SAL
     AS (SELECT 
                E.EMP_ID
              , E.EMP_NAME
              , E.SALARY
          FROM EMPLOYEE E
        ORDER BY E.SALARY DESC
        )
 SELECT
         ROWNUM
       , T.EMP_NAME
       , T.SALARY
  FROM TOPN_SAL T;