SQL - SUBQUERY

SUBQUERY

• 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
• 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행된다.
• 비교연산자의 오른쪽에 기술해야하며, 반드시 괄호로 묶어야 한다.
• SELECT, FROM, WHERE, HAVING, ORDER BY 절에서 사용할 수 있다.

 

 

단일행 서브쿼리

 

• 서브쿼리의 조회 결과 값이 1개의 행일 때
• 보통 단일행 서브쿼리 앞에는 일반 비교 연산자를 사용한다.
• >, <, >=, <=, =, !=

 

-- 단일행 코드
-- 사원명이 노옹철인 사람의 부서 조회
SELECT
       DEPT_CODE
  FROM EMPLOYEE
 WHERE EMP_NAME = '노옹철';
 
-- 부서코드가 D9인 직원을 조회
SELECT
       EMP_NAME
  FROM EMPLOYEE
 WHERE DEPT_CODE = 'D9';

-- 위의 두 쿼리를 하나로  
SELECT
       EMP_NAME
  FROM EMPLOYEE
 WHERE DEPT_CODE = (SELECT
                           DEPT_CODE
                      FROM EMPLOYEE
                     WHERE EMP_NAME = '노옹철'
                   );

---

 

 

다중행 서브쿼리

 

• 서브쿼리의 조회 결과 값이 여러개의 행일 때
• 서브쿼리 앞에서는 일반 비교 연산자를 사용할 수 없다.
• 다중행 서브쿼리에 사용되는 연산자
  IN      : 여러 개의 결과 값 중에서 메인 쿼리와 한 개라도 일치하는 값이 존재하는지 판별
  ANY   : 여러 개의 결과 값 중에서 메인 쿼리가 한 개라도 큰 값이 존재하는지 판별
  ALL    : 여러 개의 결과 값이 메인 쿼리가 모두 큰 값인지를 판별
  EXIST : 서브쿼레만 사용하는 연산자로 값이 존재하는지 판별

IN 연산자 사용

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

 

 

ANY 연산자 사용

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

SELECT
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '과장';

SELECT
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '대리';


SELECT
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '대리'
   AND E.SALARY > ANY (SELECT
                              E2.SALARY
                         FROM EMPLOYEE E2
                         JOIN JOB J2 ON(E2.JOB_CODE = J2.JOB_CODE)
                        WHERE J2.JOB_NAME = '과장'
                      );

 

 

ALL 연산자 사용

-- 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의
-- 사번, 이름, 직급, 급여를 조회하세요

SELECT 
       E.EMP_ID
     , E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM EMPLOYEE E
  JOIN JOB J ON(E.JOB_CODE = J.JOB_CODE)
 WHERE J.JOB_NAME = '과장'
   AND E.SALARY >ALL (SELECT E2.SALARY
                        FROM EMPLOYEE E2
                        JOIN JOB J2 ON(E2.JOB_CODE = J2.JOB_CODE)
                       WHERE J2.JOB_NAME = '차장'
                     );

 

 

EXISTS 사용

-- 관리자 사번이 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 
       E.EMP_ID
     , E.EMP_NAME
     , E.JOB_CODE
     , E.SALARY
  FROM EMPLOYEE E
 WHERE (E.JOB_CODE, E.SALARY) IN (SELECT 
                                         E2.JOB_CODE
                                       , TRUNC(AVG(E2.SALARY), -5)
                                    FROM EMPLOYEE E2
                                   GROUP BY E2.JOB_CODE
                                 );

---

 

 

다중열 서브쿼리

 

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

 

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

SELECT
       E.EMP_ID
     , E.EMP_NAME
     , E.DEPT_CODE
     , E.JOB_CODE
  FROM EMPLOYEE E
 WHERE SUBSTR(E.EMP_NO, 8, 1) = 2
   AND E.ENT_YN = 'Y';
   
-- 다중열을 이용하지 않고 쿼리 작성
SELECT
       E.EMP_ID
     , E.JOB_CODE
     , E.DEPT_CODE
     , E.HIRE_DATE
  FROM EMPLOYEE E
 WHERE E.DEPT_CODE = (SELECT E2.DEPT_CODE
                        FROM EMPLOYEE E2
                       WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
                         AND E2.ENT_YN = 'Y'
                     )
   AND E.JOB_CODE = (SELECT E3.JOB_CODE
                        FROM EMPLOYEE E3
                       WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
                         AND E3.ENT_YN = 'Y'
                     )
   AND E.EMP_ID != (SELECT E4.EMP_ID
                        FROM EMPLOYEE E4
                       WHERE SUBSTR(E4.EMP_NO, 8, 1) = 2
                         AND E4.ENT_YN = 'Y'
                     );

-- 다중열 서브쿼리로 변경
SELECT
       E.EMP_ID
     , E.JOB_CODE
     , E.DEPT_CODE
     , E.HIRE_DATE
  FROM EMPLOYEE E
 WHERE (E.DEPT_CODE, E.JOB_CODE) = (SELECT E2.DEPT_CODE
                                         , E2.JOB_CODE
                                      FROM EMPLOYEE E2
                                     WHERE SUBSTR(E2.EMP_NO, 8, 1) = 2
                                       AND E2.ENT_YN = 'Y'
                                    )
   AND E.EMP_ID != (SELECT E3.EMP_ID
                      FROM EMPLOYEE E3
                     WHERE SUBSTR(E3.EMP_NO, 8, 1) = 2
                       AND E3.ENT_YN = 'Y'
                   );

 

---

 

인라인 뷰(INLINE VIEW)

 

• FROM 절에서 서브쿼리를 사용(테이블 대신 사용)
• 서브쿼리가 만든 결과 집합에 대한 출력
• 인라인뷰를 이용한 TOP-N 분석이 가능하다.(ROWNUM의 이용)

 

SELECT
       E.EMP_NAME
     , J.JOB_NAME
     , E.SALARY
  FROM (SELECT E2.JOB_CODE
             , TRUNC(AVG(E2.SALARY), -5) AS JOBAVG
          FROM EMPLOYEE E2
         GROUP BY E2.JOB_CODE
       ) V
   JOIN EMPLOYEE E ON (V.JOBAVG = E.SALARY AND E.JOB_CODE = V.JOB_CODE)    
   JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
  ORDER BY J.JOB_NAME;   
  
SELECT
       V.EMP_NAME
     , V.부서명
     , V.직급이름
  FROM (SELECT EMP_NAME
             , DEPT_TITLE AS 부서명
             , JOB_NAME AS 직급이름
          FROM EMPLOYEE E
          LEFT JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
          JOIN JOB J ON (E.JOB_CODE = J.JOB_CODE)
        ) V
 WHERE V.부서명 = '인사관리부';

 

 

인라인뷰를 이용한 ROWNUM의 이용

-- 행 번호를 ROWNUM을 이용하여 붙임
-- 행 번호가 붙여진후 ORDER BY 정렬이 이루어 지므로 순서대로 출력이 안됨
SELECT 
       ROWNUM
     , E.EMP_NAME
     , E.SALARY
  FROM EMPLOYEE E
 ORDER BY E.SALARY DESC;

-- 행 번호 1~5 출력
-- 인라인뷰를 이용하여 정렬을 한 후 행 번호를 붙임
SELECT
       ROWNUM
     , V.EMP_NAME
     , V.SALARY
  FROM (SELECT E.*
          FROM EMPLOYEE E
         ORDER BY E.SALARY DESC
       ) V
 WHERE ROWNUM <= 5;

-- 6~10까지 조회(잘못 된 예)
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;

-- 6위부터 10위까지 조회
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 6 AND 10;

 

---