DB (5) DML(SELECT) SUBQUERY

▶ 서브쿼리(SUBQUERY)

- 하나의 주된 SQL(SELECT, CREATE, INSERT, UPDATE...) 안에 포함된 또 하나의 SELECT문

- 메인 SQL문을 위해서 보조 역할을 하는 SELECT문

 

■ 서브쿼리 구문

- 단일행(단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 오로지 1개일 때(한칸의 컬럼값으로 나올 때)
- 다중행(단일열) 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행일 때
- (단일행)다중열 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 열일 때
- 다중행(다중열) 서브쿼리 : 서브쿼리를 수행한 결과값이 여러 행이면서 여러 열일 때

- 서브쿼리를 수행한 결과가 몇행 몇열이냐에 따라서 사용 가능한 연산자가 달라진다.

 

■ 단일행(단일열) 서브쿼리(SINGLE ROW SUBQUERY)

- 서브쿼리의 조회 결과값이 오직 1개일 때

- 일반 연산자 사용 가능(=, !=, >=, <=, >, <, ...)

● ANSI 구문

-- 노옹철 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회
SELECT EMP_ID 사번, EMP_NAME 이름, DEPT_TITLE 부서명, SALARY
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME = '노옹철');

● 오라클 전용 구문

-- 노옹철 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회
SELECT EMP_ID 사번, EMP_NAME 이름, DEPT_TITLE 부서명, SALARY
FROM EMPLOYEE, DEPARTMENT
WHERE SALARY > (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME = '노옹철') AND DEPT_CODE = DEPT_ID(+);

 

■ 다중행 서브쿼리(MULTI ROW SUBQUERY)

- 서브쿼리의 조회 결과값이 여러 행일 경우

- IN(10, 20, 30) 서브쿼리 : 여러개의 결과값 중에서 하나라도 일치하는 것이 있다면
- (> OR <) ANY(10, 20, 30) 서브쿼리 : 여러개의 결과값 중에서 "하나라도" 클 경우 즉, 여러개의 결과값 중에서 가장 작은 값보다 클 경우
- (> OR <)ALL(10, 20, 30) : 여러개의 결과값의 모든 값보다 클 경우 혹은 작을경우

● IN 서브쿼리

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

● ANY 서브쿼리

-- 대리 직급임에도 불구하고 과장 직급의 급여보다 많이 받는 사원들을 조회(사번, 이름, 직급명, 급여)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY >= ANY(SELECT SALARY
                    FROM EMPLOYEE E, JOB J
                    WHERE E.JOB_CODE = J.JOB_CODE AND JOB_NAME = '과장')
AND JOB_NAME = '대리';

● ALL 서브쿼리

-- 과장직급임에도 불구하고 "모든" 차장직급의 급여보다 더 많이 받는 직원 조회(사번, 이름, 직급명, 급여
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE SALARY >= ALL (SELECT SALARY FROM EMPLOYEE JOIN JOB USING(JOB_CODE) WHERE JOB_NAME = '차장')
AND JOB_NAME = '과장';

 

■ (단일행) 다중열 서브쿼리

- 서브쿼리 조회결과가 값은 한 행이지만 나열된 컬럼의 개수가 여러개일 경우

ex)

-- 박나라 사원과 같은 직급코드, 같은 사수사번을 가진 사원들의 사번, 이름, 직급코드, 사수사번 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID FROM EMPLOYEE WHERE EMP_NAME = '박나라');

 

■ 다중행, 다중열 서브쿼리

- 서브쿼리 조회 결과가 여러행, 여러 컬럼일 경우

ex)

-- 각 직급별 최소급여를 받는 사원들 조회(사번, 이름, 직급코드, 급여)
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY) FROM EMPLOYEE GROUP BY JOB_CODE);

 

■ 인라인뷰(INLINE VIEW)

- FROM 절에 서브쿼리를 제시하면 서브쿼리를 실행한 결과값인 RESULT SET을 테이블을 대신해서 사용하겠다.

ex)

-- 보너스 포함 연봉이 3000만원 이상인 사원들의 사번, 이름, 보너스 포함 연봉, 부서코드를 조회
SELECT "보너스 포함 연봉"
FROM (
        SELECT EMP_ID, EMP_NAME, (SALARY + SALARY * NVL(BONUS, 0)) * 12 "보너스 포함 연봉", DEPT_CODE 
        FROM EMPLOYEE 
        WHERE (SALARY + SALARY * NVL(BONUS, 0)) * 12 >= 30000000
)
WHERE DEPT_CODE IS NULL;

인라인뷰를 주로 사용하는 예

- TOP-N 분석 : 데이터베이스 상에 존재하는 자료 중 최상위 N개의 자료를 보기위해 사용하는 기능

- ROWNUM : 오라클에서 제공해주는 컬럼, 조회된 순서대로 1부터 순터를 부여해주는 컬럼

-- 전 직원 중 급여가 가장 높은 상위 5명(순위, 사원명, 급여)
SELECT ROWNUM, EMP_NAME, SALARY -- ROWNUM으로 순번 부여하기
FROM (
        SELECT *
        FROM EMPLOYEE
        ORDER BY SALARY DESC -- ORDER BY로 정렬먼저 시키고
) 
WHERE ROWNUM <= 5;

-- ROWNUM컬럼을 이용해서 순위를 매길 수 있다.
-- 다만, 정렬이 되지 않은 상태에서는 순위를 매기면 의미가 없으므로 정렬을 먼저 시키고 순위를 나중에 매겨야한다.
-- 우선적으로 인라인뷰로 ORDER BY를 정렬하고 메인쿼리에서 순서를 붙인다.

 

■ 순위 매기는 함수(WINDOW FUNCTION)

- RANK() OVER(정렬기준) : 공동 1위가 3명이라고 한다면 그 다음 순위는 4위로 하겠다.
- DENSE_RANK() OVER(정렬기준) : 공동 1위가 3명이라고 한다면 그 다음 순위는 무조건 2위로 하겠다.

- 정렬 기준 : ORDER BY절(정렬기준 컬럼이름, 오름차순/내림차순), NULL FIRST/NULL LAST옵션은 기술이 불가능하다.
- SELECT 절에서만 기술이 가능하다.