05. 서브쿼리

<서브쿼리>

하나의 SQL문 안에 포함된 또 다른 SELECT문

메인 SQL문을 위에 보조 역할 하는 쿼리

 

예시1)

노옹철 사원과 같은 부서에 속한 사원들을 조회

 

SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';
 

 

노옹철 사원은 어떤 부서일까?

 

SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';

위에 두 쿼리를 하나의 쿼리로 변경하자

SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '노옹철');

서브쿼리의 구분

서브쿼리를 수행한 결과값이 몇행 몇 열로 나오느냐에 따라서 분류

 

★ 단일행 서브쿼리 : 서브쿼리의 조회 결과값이 오로지 1개일 때

★ 다중행 서브쿼리 : 서브쿼리의 조회 결과값이 여러행일 때 (여러행 한열)

★ 다중열 서브쿼리 : 서브쿼리의 조회 결과값이 한행이지만 컬럼이 여러개일 때

★ 다중행 다중열 서브쿼리 : 서브쿼리의 조회결과 값이 여러 행 여러 컬럼일 떄

 

>> 서브쿼리의 결과값에 따라서 서브쿼리 앞쪽에 연산자가 달라진다.

 

단일행 서브쿼리

서브쿼리에 조회 결과값이 오로지 1개일 때 (한행 한열)

일반 비교연산자 사용가능

=, !=, >, <= ...

 

예시1)

노옹철 사원의 급여보다 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회

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 = '노옹철');

 

예시2)

'전지연'사원과 같은 부서의 사람들의 사번, 사원명, 전화번호, 입사일, 부서명 조회
단, 전지연 사원 제외

SELECT EMP_ID, EMP_NAME, PHONE, HIRE_DATE, DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '전지연')
                    AND EMP_NAME != '전지연';

 

다중행 서브쿼리

  서브쿼리를 수행한 결과값이 여러행일 때 (컬럼은 한개)
    IN (서브쿼리) : 여러개의 결과값 중에서 한개라도 일치하는 값이 있다면 조회
    > ANY (서브쿼리) : 여러개의 결과값 중에서 한개라도 클 경우 조회
    < ANY (서브쿼리) : 여러개의 결과값 중에서 한개라도 작을 경우 조회
        비교대상 > ANY (서브쿼리의 결과값 -> 값1, 값2, 값3...)
        
    > ALL(서브쿼리) : 여러개의 모든 결과값들 보다 클 경우 조회
    < ALL(서브쿼리) : 여러개의 모든 결과값들 보다 작을 경우 조회

 

예시1)

대리직급임에도 과장 직급 급여들 중 최소 급여보다 많이 받는 사원들의 사원 사번 직급 급여 조회

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '대리'
    AND SALARY > ANY(SELECT SALARY
                     FROM EMPLOYEE
                     JOIN JOB USING(JOB_CODE)
                     WHERE JOB_NAME = '과장');
 

다중열 서브쿼리

결과값은 한 행이지만 나열된 컬럼수가 여러개일 경우

 

예시1)

박나리 사원과 같은 직급코드, 같은 사수를 가지고 있는 사원들의 사번, 사원명, 직급코드, 사수번호

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 = '박나라')
                               AND EMP_NAME != '박나라';

 

 

다중행 다중열 서브쿼리

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

 

예시1)

각 부서별 최고급여를 받는 사원들의 사번, 사원명, 부서코드, 급여

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE (DEPT_CODE, SALARY) IN (SELECT DEPT_CODE, MAX(SALARY)
                              FROM EMPLOYEE
                              GROUP BY DEPT_CODE);

인라인 뷰

FROM절에 서브쿼리를 작성하는 것

서브쿼리를 수행한 결과를 마치 테이블처럼 사용

(ROWNUM : 오라클에서 제공하는 컬럼, 조회된 순서대로 1부터 순번을 부여한다.)

 

예시1)

전 직원중 급여가 가장 높은 5명만 조회

SELECT ROWNUM, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE ROWNUM <= 5
ORDER BY SALARY DESC;

 

 

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

    RANK() OVER(정렬기준) | DENSE_RANK() OVER(정렬기준)
    RANK() OVER(정렬기준) : 동일한 순위 이후의 등수를 동일한 인원 수 만큼 건너뛰고 순위계산
    DENSE_RANK() OVER(정렬기준) : 동일한 순위가 있다고해도 그 다음 등수를 무조건 1씩 증가
    
    무조건 SELECT에서만 사용