[ORACLE] 다양한 조건 처리를 위한 서브쿼리 활용
![[ORACLE] 다양한 조건 처리를 위한 서브쿼리 활용](/assets/img/study_Oracle/2022-10-11-%5BORACLE%5D_%EB%8B%A4%EC%96%91%ED%95%9C%20%EC%A1%B0%EA%B1%B4%20%EC%B2%98%EB%A6%AC%EB%A5%BC%20%EC%9C%84%ED%95%9C%20%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC%20%ED%99%9C%EC%9A%A9/logo.png){kind=logo}
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_NAME SALARY 1 Hartstein 13000 2 Higgins 12000 3 King 24000 4 Kochhar 17000 5 De Haan 17000
- Abel보다 급여가 많은 사람은 누구입니까?
당장 알 수 없는 조건도 SUBQUERY 를 이용하여 해결할 수 있다.
서브쿼리의 종류는 리턴되는 값의 갯수에 따라 나뉜다.
- Subquery Type
- Single-row subquery
- 서브쿼리 결과가 1개의 행이므로 일대일비교를 하게 된다.
- Multiple-row subquery
- 서브쿼리 결과가 여러개의 행이므로 일대다 비교를 하게된다.
- 대표적으로
IN이 있다.
- Single-row subquery
Single Row Subquery
- 단일 행 Subquery
- 한 행만 반환
- 단일 행 비교 연산자를 사용
| Operator | Meaning |
|---|---|
| = | 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_NAME | JOB_ID | |
|---|---|---|
| 1 | Rajs | ST_CLERK |
| 2 | Davies | ST_CLERK |
| 3 | Matos | ST_CLERK |
| 4 | Vargas | ST_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_NAME | JOB_ID | SALARY | |
|---|---|---|---|
| 1 | Abel | SA_REP | 11000 |
여러개의 조건은 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_ID | MIN(SALARY) | |
|---|---|---|
| 1 | (null) | 7000 |
| 2 | 20 | 6000 |
| 3 | 90 | 17000 |
| 4 | 110 | 8300 |
| 5 | 80 | 8600 |
그룹 조건은 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_ID | AVG(SALARY) | |
|---|---|---|
| 1 | ST_CLERK | 2925 |
서브쿼리문과 메인문 모두 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
- 한 개 이상의 행을 반환 <- 원래 두 개 이상이라고 배웠었는데, 수정하였음.
- 여러 행 비교 연산자 사용
| Operator | Meaning |
|---|---|
| IN | 리스트의 임의 멤버와 같음 |
| ANY | =, ≠, >, <, ≤, ≥ 연산자가 앞에 있어야 한다. 값 하나를 리스트의 값 또는 query에서 반환된 값과 각각 비교. query에서 반환된 행이 없으면 FALSE로 평가된다. |
| ALL | =, ≠, >, <, ≤, ≥ 연산자가 앞에 있어야 한다. 값 하나를 리스트의 모든 값 또는 query에서 반환된 모든 값과 비교. query에서 반환된 행이 없으면 TRUE로 평가된다. |
| EXISTS | EXISTS 연산자는 테이블에 특정 행이 있는지 여부에 따라 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_ID | LAST_NAME | JOB_ID | SALARY | |
|---|---|---|---|---|
| 1 | 144 | Vargas | ST_CLERK | 2500 |
| 2 | 143 | Matos | ST_CLERK | 2600 |
| 3 | 142 | Davies | ST_CLERK | 3100 |
| 4 | 176 | Taylor | SA_REP | 8600 |
여기서 서브쿼리문의 결과행은 9000, 6000, 4200이다.
ANY 는 OR 과 비슷한 의미를 가진다.
결과 값으로는 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_ID | LAST_NAME | JOB_ID | SALARY | |
|---|---|---|---|---|
| 1 | 144 | Rajs | ST_CLERK | 3500 |
| 2 | 143 | Davies | ST_CLERK | 3100 |
| 3 | 142 | Matos | ST_CLERK | 2600 |
| 4 | 176 | Vargas | ST_CLERK | 2500 |
ALL 은 AND 와 비슷한 의미를 가진다.
결과값으로는 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_ID | DEPARTMENT_NAME | MANAGER_ID | LOCATION_ID | |
|---|---|---|---|---|
| 1 | 190 | Contracting | (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를 출력시킨다.
즉 결과는 다음과 같다.
| RANK | LAST_NAME | SALARY | |
|---|---|---|---|
| 1 | 1 | King | 24000 |
| 2 | 2 | Kochhar | 17000 |
| 3 | 3 | De Haan | 17000 |
하지만 이 때 결과를 보면 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;
