05_SUBQUERY

SUBQUERY

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

서브쿼리는 메인쿼리가 샐행되기 이전에 한번만 실행되며

비교연산자의 오른쪽에 기술해야 하고 반드시 괄호로 묶어야 한다.

또한 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야 한다.

 

부서 코드가 노옹철 사원과 같은 직원 명단 조회

직원 명단 조회 → SELECT EMP_NAME FROM EMPLOYEE

부서코드가 노옹철과 같은 → 위 문장의 조건은 해당 직원들의 부서코드는 이름이 노옹철인 사람의 부서코드와 같아야 한다.

SELECT
       EMP_NAME
  FROM EMPLOYEE
 WHERE DEPT_CODE = (SELECT
                           DEPT_CODE
                      FROM EMPLOYEE
                     WHERE EMP_NAME = '노옹철'
                   );

서브쿼리의 유형

단일행, 다중행, 다중열, 다중행 다중열 서브쿼리

 

단일행 서브쿼리 : 서브쿼리 조회 결과가 1개인 경우

단일행 서브쿼리는 일반 비교 연산자를 사용한다.

 

HAVING 절에서 사용되는 경우

부서별 급여의 합계 중 합계가 가장 큰 부서의 부서명, 급여 합계를 조회

부서명은 DEPARTMENT에 있다. 그냥 조인하면 부서명이 없는 사원들이 사라지니까 LEFT JOIN을 한다.

GROUP BY 에는 SELECT에 쓴 컬럼명이 와야 한다.

SELECT
	   DEPT_TITLE
     ,  SUM(SALARY)
  FROM  EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT
				              MAX(SUM(SALARY))
			            FROM  EMPLOYEE
                        GRUOP BY DEPT_CODE
                      );

 

다중행 서브쿼리 : 서브쿼리의 조회 결과가 여러 개인경우

다중행 서브쿼리 앞에는 일반 비교 연산자를 사용할 수 없다.

IN / NOT IN : 하나라도 있는지

> ANY / < ANY : 여러 개의 결과 값 중에서 한 개라도 큰 | 작은 경우

> ALL / < ALL : 모든 값보다 큰 | 작은 경우

EXIST | NOT EXIST : 서브쿼리에만 사용하는 연산자. 값이 존재하는가?

 

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

직급명은 JOB 테이블에 있으므로 JOIN

과장들의 급여 중 한 금액보다 큰 경우 → 최소 급여보다 많이 받는다

반대로 가장 큰 급여보다 많이 받는 경우는 ALL을 사용한다.

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 = '과장'
                     );

다중열 서브쿼리 : 서브쿼리의 조회 결과 컬럼의 개수가 여러 개인 경우

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

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'
                                 );

FROM 절에서 서브쿼리 사용

서브쿼리 결과(REWULT SET)을 테이블 대신 사용할 수 있으며 인라인 뷰(INLINE VIEW)라고도 한다.

인라인뷰로 직급별 평균급여를 계산한 테이블 V 를 만든다.

V.JOBAVG = E.SALARY 평균급여와 본인의 급여가 같고

V.JOB_CODE = E.JOB_CODE 해당 직급인 직원 조회

JOB_NAME은 JOB 테이블에 있으니까 또 조인한다

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)
  JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
 ORDER BY J.JOB_NAME;

인라인뷰 사용 시 유의할 점

• 테이블로서 인라인뷰의 결과만이 남아 있으므로 서브쿼리에서 조회하지 않은 컬럼은 조회할 수 없으며 별칭을 사용했다면 해당 별칭으로 조회해야 한다.(SELECT 절)

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

ORDER BY한 결과에 ROWNUM을 붙인다. (내부적으로 갖고 있는 행 번호)

SALARY 기준 내림차순 정렬 - 큰 값부터

ROWNUM은 WHERE 절에서 붙여졌기 때문에 정렬과 상관없다.

SELECT
       ROWNUM
     , EMP_NAME
     , SALARY
  FROM EMPLOYEE
 ORDER BY SALARY DESC;

FROM에서 서브쿼리(인라인뷰)를 사용해 행들을 정렬을 해서, 그 순서대로 ROWNUM이 붙여진다.

SELECT 
       ROWNUM
     , V.EMP_NAME
     , V.SALARY
  FROM (SELECT E.*
          FROM EMPLOYEE E
         ORDER BY E.SALARY DESC
       ) V
 WHERE ROWNUM <= 5; -> 1~5위

6위에서 10위까지 조회

WHERE절에서 ROWNUM은 1로 시작하고, 해당 값이 FALSE가 되어 다음 행을 확인할 때 다시 1로 시작하여 모든 행이 6~10 사이가 되지 않아 결과가 0행이 된다.

SELECT 
       ROWNUM
     , V.EMP_NAME
     , V.SALARY
  FROM (SELECT E.*
          FROM EMPLOYEE E
         ORDER BY E.SALARY DESC
       ) V
 WHERE ROWNUM BETWEEN 6 AND 10;

아예 숫자가 주어진 컬럼으로 만들어서 조회가 가능하게 한다.

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;

 

RANK() OVER( ~ ): 동일한 순위 이후의 등수를 동일한 인원수만큼 건너 뛰고 다음 순위 계산 (1 2 3 3 5)

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

(1 2 3 3 4)

→ ORDER BY 절을 순위로 매기면 ROWNUM 처럼 TOP-N 선택 가능

→WINDOW함수임

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;

상[호연]관 서브쿼리

일반적으로는 서브 쿼리가 만든 결과 값을 메인 쿼리가 비교 연산한다.

상관 서브 쿼리는 메인 서브 쿼리가 사용하는 테이블의 값을 서브 쿼리가 이용해서 결과를 만든다.

메인 쿼리 테이블의 값이 변경 되면, 서브 쿼리의 결과 값도 바뀌게 된다.

관리자 사번이 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
              );

스칼라 서브쿼리

단일행 서브쿼리 + 상관 쿼리

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

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
                );

SELECT 절에서 스칼라 서브쿼리 사용

→ SELECT 절에서 서브쿼리 사용 시, 결과 값은 반드시 1행으로 나와야 하기 때문에 스칼라 서브쿼리를 사용해야 한다.

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;