05. 서브쿼리

JAVA 웹 개발/2. Oracle

05. 서브쿼리

코딩 펭귄 2022. 6. 8. 14:28

📅 2021.09.13 ~ 2021.09.14

SUBQUERY

하나의 sql문 안에 또 다른 sql문이 포함되는 것으로, 메인 쿼리가 실행되기 전 한 번만 실행되며, 반드시 괄호로 묶어 주어야 한다.
서브 쿼리와 비교할 항목은 반드시 서브 쿼리의 결과 값의 개수와 자료형을 일치시켜야 한다. 이때 비교할 항목이란 Result Set의 결과물을 말하는 것이 아니며, 서브 쿼리와 비교되는 행의 개수를 의미한다.
서브 쿼리는 어떤 절에든 들어갈 수 있다.
서브 쿼리의 유형
1. 단일행 서브 쿼리 : 서브 쿼리의 조회 결과 값의 개수가 1개인 서브 쿼리를 말한다.
  일반적으로 단일행 서브 쿼리 앞에는 일반 연산자(<. >, <=, >=, =, !=/<>/^=)가 들어간다.
```
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT SALARY
                FROM EMPLOYEE
                WHERE EMP_NAME = '김사원');
```
2. 다중행 서브 쿼리 : 서브 쿼리의 조회 결과 값의 행이 여러 개인 서브 쿼리를 말한다.
  일반 비교 연산자 사용이 불가능하며, 다중행 서브 쿼리에 사용되는 연산자는 다음과 같다.
  IN/NOT IN : 여러 개의 결과 값 중 한 개라도 일치하는 값이 있는/없는 경우를 말하며, 여러 개를 비교하고 싶은 경우 사용한다.
  ANY, < ANY : 여러 개의 결과 값 중에서 하나라도 큰/작은 경우를 말하며, 가장 작은 값보다 큰가/가장 큰 값보다 작은가를 판단할 때 사용한다.
  ALL, < ALL : 모든 값보다 큰/작은 경우를 말하며, 가장 큰 값보다 큰가/가장 작은 값보다 작은가를 판단할 때 사용한다.
  EXISTS/NOT EXISTS : 여러 개의 결과 값 중 일치하는 값이 존재하는/존재하지 않는 경우를 판단할 때 사용된다. IN과 EXISTS의 차이점 : IN의 경우 일치하는 값을 찾아 반환하지만, EXISTS의 경우 일치하는 값이 있으면 TRUE, 없으면 FALSE를 반환한다.
```
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME,
       CASE WHEN EMP_ID IN (SELECT DISTINCT MANAGER_ID
                            FROM EMPLOYEE
                            WHERE MANAGER_ID IS NOT NULL) THEN '관리자'
       ELSE '직원'
    END 구분
FROM EMPLOYEE
     LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
     JOIN JOB USING(JOB_CODE);
```
3. 다중열 서브 쿼리 : 서브 쿼리의 조회 결과 컬럼의 개수가 여러 개인 서브 쿼리를 말한다. 다중열 서브쿼리 역시 컬럼 값이 서브 쿼리의 조회 결과에 속하면 되는 경우 IN을 사용한다.
```
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE) IN (SELECT DEPT_CODE, JOB_CODE 
                                FROM EMPLOYEE
                                WHERE SUBSTR(EMP_NO, 8, 1) = 2 AND ENT_YN = 'Y')
      AND EMP_NAME != (SELECT EMP_NAME        
                       FROM EMPLOYEE
                       WHERE SUBSTR(EMP_NO, 8, 1) = 2 AND ENT_YN = 'Y');
```
4. 다중행 다중열 서브 쿼리 : 서브 쿼리의 조회 결과 컬럼의 개수와 행의 개수가 여러 개인 서브 쿼리를 말한다.
```
SELECT ROWNUM, EMP_NAME, SALARY 
FROM (SELECT *          
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;
```
5. 상(호연)관 서브 쿼리 : 서브 쿼리가 만든 결과 값을 메인 쿼리가 비교 연산할 때 메인 쿼리 테이블의 값이 변경되면 서브 쿼리의 결과 값도 바뀌는 서브 쿼리를 의미한다.
6. 스칼라 서브 쿼리 : 상관 쿼리이면서 결과 값이 한 개인 서브 쿼리를 의미한다.

인라인 뷰(INLINE-VIEW)

FROM 절에 서브쿼리가 들어간 경우를 의미한다.
TOP-N 분석 : 서브쿼리에서 확장된 문법으로 컬럼에서 가장 큰/작은 N개의 값을 요청하는 경우 가지고 오는 방법을 의미한다.
해당 경우 행 번호를 가지고 오는 ROWNUM을 사용하여야 한다. 하지만, ROWNUM은 FROM 절이 실행될 때 부여되기 때문에 FROM 이후 정렬한다면 해당 정렬 결과는 반영되지 않는다. 따라서 급여가 높은/적은 순서 등 정렬할 기준이 존재하는 경우 FROM절에 서브쿼리를 삽입하여 해당 기준으로 먼저 정렬한 테이블을 만든 후 진행하여야 한다.

아래와 같이 FROM절에서 테이블을 생성하는 경우 메인 쿼리에서는 서브 쿼리의 결과로 만들어진 테이블의 컬럼 명만 참조할 수 있기 때문에 필요한 컬럼 명을 모두 넣어 만들어 주어야 한다.
```
SELECT ROWNUM, EMP_NAME, SALARY 
FROM (SELECT EMP_NAME, SALARY  -- 메인 쿼리에서 사용하는 컬럼명이 존재하지 않는 경우 에러 발생       
      FROM EMPLOYEE
      ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;
```
만약 서브 쿼리에서 사용한 컬럼 명이 함수인 경우에는 별칭을 만들어 준 후 메인 쿼리에서 사용하여야 한다. 함수 그대로 메인 쿼리에서 사용하면 메인 쿼리에서 해당 부분을 컬럼 명이 아닌 함수로 인식하기 때문에 오류가 발생한다.
```
SELECT DEPT_CODE, DEPT_TITLE, 평균급여 -- AVG(SALARY)를 사용하는 경우 오류 발생
FROM (SELECT DEPT_CODE, DEPT_TITLE, AVG(SALARY) 평균급여 
      FROM EMPLOYEE 
           JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
      GROUP BY DEPT_CODE, DEPT_TITLE
      ORDER BY AVG(SALARY) DESC)
WHERE ROWNUM <= 3;
```

WITH

서브 쿼리에 이름을 붙여 준다. 같은 서브 쿼리가 여러 번 사용되는 경우 WITH 사용으로 중복 작성을 줄일 수 있으며, 실행 속도 또한 빨라진다.

아래와 같이 메인 쿼리 위에 WITH로 이름을 붙인 후 서브 쿼리로 사용한다.

WITH TOPN_SAL AS (SELECT *          
                  FROM EMPLOYEE
                  ORDER BY SALARY DESC)

SELECT ROWNUM, EMP_NAME, SALARY 
FROM TOPN_SAL;

RANK() OVER/DENSE_RANK() OVER

순위를 간단하게 구할 수 있는 방법이다.
RANK() OVER : 동일한 순위가 2명 이상일 경우 다음 등수를 해당 인원만큼 건너뛰고 계산한다. (ex. 1등이 2명일 경우 다음 등수는 3등이 된다.)
DENSE_RANK() OVER : 동일한 등수가 2명 이상인 경우에도 다음 등수에 +1만 하여 계산한다. (ex. 1등이 2명일 경우에도 다음 등수는 2등이 된다.)

저작자표시 비영리 동일조건 (새창열림)