[ORACLE] 다양한 조건 처리를 위한 서브쿼리 활용

[ORACLE] 다양한 조건 처리를 위한 서브쿼리 활용

SUBQUERY에 대해서 이해해보고 단일 행, 다중 행 SUBQUERY를 활용해보자.


Using Subquery


  • Subquery를 사용한 문제 해결
    • Abel보다 급여가 많은 사람은 누구입니까?
      • Main query : Abel 사원의 급여보다 많이 받는 사원은 누구인가?
      • Sub query : Abel 사원의 급여는 얼마인가?
        SELECT  last_name, salary
          FROM  employees
         WHERE  salary > (SELECT  salary
        	                  FROM  employees
                           WHERE  last_name = 'Abel');
      
       LAST_NAMESALARY
      1Hartstein13000
      2Higgins12000
      3King24000
      4Kochhar17000
      5De Haan17000

당장 알 수 없는 조건도 SUBQUERY 를 이용하여 해결할 수 있다.

서브쿼리의 종류는 리턴되는 값의 갯수에 따라 나뉜다.

  • Subquery Type
    • Single-row subquery
      • 서브쿼리 결과가 1개의 행이므로 일대일비교를 하게 된다.
    • Multiple-row subquery
      • 서브쿼리 결과가 여러개의 행이므로 일대다 비교를 하게된다.
      • 대표적으로 IN 이 있다.

Single Row Subquery


  • 단일 행 Subquery
    • 한 행만 반환
    • 단일 행 비교 연산자를 사용
OperatorMeaning
=Equal to
>Greater than
Greater than or equal to
<Less than
Less than or equal to
<>Not equal to
-- 사원 141의 직무 ID와 동일한 직무 ID를 가진 사원을 출력하시오.

SELECT  last_name, job_id
  FROM  employees
 WHERE  job_id = (SELECT  job_id -- 단일 행 비교 연산자 '=' 사용
                    FROM  employees
                   WHERE  employee_id = 141);
 LAST_NAMEJOB_ID
1RajsST_CLERK
2DaviesST_CLERK
3MatosST_CLERK
4VargasST_CLERK

당장 알 수 없는 조건을 서브쿼리화 시키고, 그 결과값이 단일 행일 경우, 단일 행 비교 연산자를 사용하자.

-- "Taylor"와 직무가 같고, 급여는 더 많은 사원을 출력하시오.

SELECT  last_name, job_id, salary
  FROM  employees
 WHERE  job_id = (SELECT  job_id -- 조건 1. 직무가 같다
                    FROM  employees
                   WHERE  last_name = 'Taylor')
   AND  salary > (SELECT  salary -- 조건 2. 급여가 더 많다.
                    FROM  employees
                   WHERE  last_name = 'Taylor');
 LAST_NAMEJOB_IDSALARY
1AbelSA_REP11000

여러개의 조건은 WHERE 절에서 AND 를 사용하여 여러개의 서브쿼리를 사용하여 해결할 수 있다.

GROUP BY 에서의 활용은 다음 예시를 보자.

-- 부서 50의 최저 급여보다 최저 급여가 많은 모든 부서를 출력

SELECT  department_id, MIN(salary)
  FROM  employees
 GROUP  BY department_id
HAVING  MIN(salary) > (SELECT  MIN(salary)
                         FROM  employees
                        WHERE  department_id = 50);
 DEPARTMENT_IDMIN(SALARY)
1(null)7000
2206000
39017000
41108300
5808600

그룹 조건은 WHERE 을 사용하지 못함에 주의하자.

HAVING 절에 서브쿼리를 작성하여 해결하면 된다.

하나 더 보도록 하자.

SELECT  job_id, AVG(salary)
  FROM  employees
 GROUP  BY job_id
HAVING  AVG(salary) = (SELECT  MIN(AVG(salary))
                         FROM  employees
                        GROUP  BY job_id);
 JOB_IDAVG(SALARY)
1ST_CLERK2925

서브쿼리문과 메인문 모두 GROUP BY 를 사용하였다.

서브쿼리 SELECT 문을 잘 보도록 하자.

SELECT  employee_id, last_name
  FROM  employees
 WHERE  salary = (SELECT  MIN(salary)
                    FROM  employees
               GROUP  BY department_id);

위 쿼리문은 제대로 실행되지 않고 오류문을 띄운다.

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

이는 서브쿼리문의 결과가 2개 이상의 다중 행을 리턴하기 때문에, 일대다 매칭이 되어 제대로된 값 비교가 되지 않기 때문이다.

이를 올바르게 출력하기 위해서는 = 대신에 IN 을 사용하여 “다중 행 서브쿼리”를 사용하여 해결할 수 있다.

Multiple Row Subquery


  • 다중 행 Subquery
    • 한 개 이상의 행을 반환 <- 원래 두 개 이상이라고 배웠었는데, 수정하였음.
    • 여러 행 비교 연산자 사용
OperatorMeaning
IN리스트의 임의 멤버와 같음
ANY=, ≠, >, <, ≤, ≥ 연산자가 앞에 있어야 한다. 값 하나를 리스트의 값 또는 query에서 반환된 값과 각각 비교. query에서 반환된 행이 없으면 FALSE로 평가된다.
ALL=, ≠, >, <, ≤, ≥ 연산자가 앞에 있어야 한다. 값 하나를 리스트의 모든 값 또는 query에서 반환된 모든 값과 비교. query에서 반환된 행이 없으면 TRUE로 평가된다.
EXISTSEXISTS 연산자는 테이블에 특정 행이 있는지 여부에 따라 query 결과가 달라지는 query에 사용된다.

ANY 연산


-- IT 프로그래머가 아닌 사원 중 급여가 IT 프로그래머보다 적은 사원은?

SELECT  employee_id, last_name, job_id, salary
  FROM  employees
 WHERE  salary < ANY (SELECT  salary -- 9000, 6000, 4200
                        FROM  employees
                       WHERE  job_id = 'IT_PROG'
   AND  job_id <> 'IT_PROG';
 EMPLOYEE_IDLAST_NAMEJOB_IDSALARY
1144VargasST_CLERK2500
2143MatosST_CLERK2600
3142DaviesST_CLERK3100
4176TaylorSA_REP8600

여기서 서브쿼리문의 결과행은 9000, 6000, 4200이다.

ANYOR 과 비슷한 의미를 가진다.

결과 값으로는 9000, 6000, 4200 보다 작은 값, 즉 9000보다 작으면 행이 출력된다.

AND 연산을 보자.

AND 연산


-- 직무 ID가 IT_PROG인 모든 사원보다 급여가 적고 직무가 IT_PROG가 아닌 사원은?

SELECT  employee_id, last_name, job_id, salary
  FROM  employees
 WHERE  salary < ALL (SELECT  salary -- 9000, 6000, 4200
                        FROM  employees
                       WHERE  job_id = 'IT_PROG'
   AND  job_id <> 'IT_PROG';
 EMPLOYEE_IDLAST_NAMEJOB_IDSALARY
1144RajsST_CLERK3500
2143DaviesST_CLERK3100
3142MatosST_CLERK2600
4176VargasST_CLERK2500

ALLAND 와 비슷한 의미를 가진다.

결과값으로는 9000, 6000, 4200 모두에 작은 값, 즉 4200보다 작은 값이 출력된다.

조건을 모두 만족해야한다.

EXISTS 연산


  • EXISTS 연산자는 테이블에 특정 행이 있는지 여부에 따라 query 결과가 달라지는 query에 사용
  • subquery에서 최소한 한 개의 행을 반환하면 TRUE로 평가
SELECT  *
  FROM  departments
 WHERE  NOT EXISTS (SELECT  *
                      FROM  employees
                     WHERE  employees.department_id = departments.department_id);
  • DEPARTMENTS 테이블의 각 행에 대해 EMPLOYEES 테이블에 부서 ID가 동일한 행이 있는지를 확인하는 조건 검사가 수행
  • 그러한 행이 없으면 해당 행은 조건을 충족하는 것이므로 선택
  • EMPLOYEES 테이블에 그러한 행이 있을 경우 해당 행은 선택되지 않는다.

서브쿼리에서는 메인문의 FROM 절에 있는 테이블을 참조할 수 있다.

이를 상호 연관 서브쿼리라고 한다.

결과는 다음과 같다.

 DEPARTMENT_IDDEPARTMENT_NAMEMANAGER_IDLOCATION_ID
1190Contracting(null)1700

사실상 정직원들은 모두 부서번호가 있을 것이기 때문에, 사원이 없는 하나의 행만 출력된다.

Using Inline view


  • 인라인 뷰(Inline view)
    • FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 유사
    • FROM 절에 있는 서브쿼리가 인라인 뷰를 생성
    • FROM 절에 Create 명령어로 만들지 않고 해당 라인에 직접 기술
    • 효율적인 검색 기능

FROM 절에서 서브쿼리를 쓰는 방법이라고 생각할 수 있다.

SELECT 를 통해서 만들어진 결과집합을 임시 메모리에 저장하여 사용한다.

Top-N 구문


인라인 뷰에서 파생된 구문이다.

TOP-N 질의는 칼럼의 값 중 n개의 가장 큰 값 또는 작은 값을 질의하는 것이다.

이번 분기에 가장 많이 팔린 제품을 조회하거나 할 때 사용된다.

SELECT  [column_list], ROWNUM
  FROM  (SELECT  [column_list]
           FROM  table
          ORDER  BY Top-N_column)
 WHERE  ROWNUM <= N
  • 사용방법
    • 데이터를 정렬하는 질의를 subquery나 inline 뷰로 생성
  • 필수 요소
    • Rownum (Row 순서를 나타내는 가상칼럼)
    • From 절상의 SubQuery (Inline View)
    • SubQuery 내의 Order by 절
    • 사용가능 비교연산자 : <, ≤
SELECT  rownum AS rank, last_name, salary
  FROM  (SELECT  last_name, salary
           FROM  employees
          ORDER  BY salary DESC) -- 지금 생성된 임시 테이블, rownum 새롭게 설정
 WHERE  rownum <= 3;

모든 테이블은 기본적을 rownum 이라는 순번 칼럼을 출력할 수 있다.

rownum 값은 각 행의 번호를 출력하는데, 각 행의 값의 크기와는 상관없다.

따라서 이를 DESC 를 통해 출력하면, rownum 의 값은 뒤죽박죽이 된다.

위 쿼리에서는 FROM 절에 서브쿼리를 사용하면서 rownum 을 새롭게 설정시켰다.

이제 rownum 은 가장 높은 연봉을 받는 순서대로 값을 받게 되므로, WHERE 절의 조건에 의해 가장 높은 연봉 TOP 3를 출력시킨다.

즉 결과는 다음과 같다.

 RANKLAST_NAMESALARY
11King24000
22Kochhar17000
33De Haan17000

하지만 이 때 결과를 보면 2등과 3등은 ‘salary’값이 같다.

즉 동등 처리가 되지 않았음을 알 수 있다.

이는 Analyze Function을 통해 구현할 수 있다.

Analyze Function


SELECT  EMPNO, ENAME, SAL, 
        RANK()       OVER(ORDER BY SAL DESC) AS RANK, -- AS 전까지가 기본 문법이다.
        DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK,
        ROW_NUMBER() OVER(ORDER BY SAL DESC) AS ROW_NUMBER
  FROM  EMP;

| EMPNO | ENAME | SAL | RANK | DENSE_RANK | ROW_NUMBER | | — | — | — | — | — | — | | 7839 | KING | 5000 | 1 | 1 | 1 | | 7788 | SCOTT | 3000 | 2 | 2 | 2 | | 7902 | FORD | 3000 | 2 | 2 | 3 | | 7566 | JONES | 2975 | 4 | 3 | 4 | | 7698 | BLAKE | 2850 | 5 | 4 | 5 | | 7782 | CLARK | 2450 | 6 | 5 | 6 |

  • RANK()
    • 동등 처리 + 차등 처리
    • 3등이 없는 것을 확인할 수 있다.
  • DENSE_RANK()
    • 동등처리
    • 2등이 두 명이지만 뒤에 3등이 온다.
  • ROW_NUMBER
    • 그냥 랭킹

Top-N과 Analyze Function 비교


-- 6~10위 사이의 급여 랭킹을 구하시오.

-- Top-N
SELECT  ranking, employee_id, last_name, salary
  FROM  (SELECT  rownum ranking, employee_id, last_name, salary
           FROM  (SELECT  employee_id, last_name, salary
                    FROM  employees
                   ORDER  BY salary DESC))
 WHERE  ranking BETWEEN 6 and 10;

-- Analyze Function
SELECT  rank, employee_id, last_name, salary
  FROM  (SELECT  employee_id, last_name, salary
                ,row_number() OVER(ORDER BY salary DESC) AS rank
           FROM  employees)
 WHERE  rank BETWEEN 6 and 10;

© 2022. All rights reserved. 신동민의 블로그