SUBQUERY 정의

하나의 SQL문 안에 포함된 또다른 SQL문으로 메인 쿼리를 위해 보조하는 역할

SUBQUERY 진행과정 예제
1-1)
부서코드가 노옹철사원과 같은 소속의 직원 명단 조회

1) 노옹철 사원의 부서코드 (보조)
SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';

2) D9에 속한 직원 명단 조회
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

--3) 1+2
SELECT EMP_NAME
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                    FROM EMPLOYEE
                    WHERE EMP_NAME = '노옹철');

SUBQUERY 유형

1. 단일 행 서브쿼리

서브쿼리 조회 결과 행 개수가 1개일 때

예제 1-1)

전 직원의 급여 평균보다 적은 급여를 받는 직원의 이름, 직급코드, 부서코드, 급여 조회(직급 순 정렬)

SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY < (SELECT AVG(SALARY)
                    FROM EMPLOYEE)
ORDER BY 2;

예제 1-2)

부서별 급여 합계 중 가장 큰 부서의 부서 명, 급여 합계 조회
(서브쿼리는 SELECT, FROM, WHERE, HAVING절에서도 사용 가능)

SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY))
                        FROM EMPLOYEE
                        GROUP BY DEPT_CODE);

2. 다중 행 서브쿼리

일반 비교 연산자 사용 불가능

사용가능한 연산자

- IN / NOT IN : 여러 개의 결과 값 중 한 개라도 일치하는 값이 있다면/없다면 
- > ANY / < ANY : 여러 개의 결과 값 중 한 개라도 큰/작은 경우
- > ALL / < ALL : 모든 값 보다 큰/작은 경우
- EXIST / NOT EXIST : 값이 존재한다면 / 존재하지 않는다면

예제 2-1)

관리자와 일반 직원에 해당하는 사원 정보 추출 : 사번, 이름, 부서명, 직급, 구분(관리자/직원)

-- 1) 관리자의 사번 조회
--먼저 관리자 사번 추출 (중복 제거)
SELECT DISTINCT MANAGER_ID
FROM EMPLOYEE
WHERE MANAGER_ID IS NOT NULL;

--2) 사번, 이름, 부서명, 직급, 구분 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_NAME
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE);
    
--3) 관리자에 해당하는 직원 정보 추출
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_NAME, '관리자' 구분
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE)
WHERE EMP_ID IN (SELECT DISTINCT MANAGER_ID
                    FROM EMPLOYEE
                    WHERE MANAGER_ID IS NOT NULL);
--IN 안에 매니저에 해당하는 사번을 가져오기

--4) 관리자에 해당하지 않는 직원 정보 추출 
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_NAME, '직원' 구분
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE)
WHERE EMP_ID NOT IN (SELECT DISTINCT MANAGER_ID
                    FROM EMPLOYEE
                    WHERE MANAGER_ID IS NOT NULL);

--5) 3,4번 합치기 
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_NAME, '관리자' 구분
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE)
WHERE EMP_ID IN (SELECT DISTINCT MANAGER_ID
                    FROM EMPLOYEE
                    WHERE MANAGER_ID IS NOT NULL)
UNION
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_NAME, '직원' 구분
FROM EMPLOYEE
    LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    JOIN JOB USING(JOB_CODE)
WHERE EMP_ID NOT IN (SELECT DISTINCT MANAGER_ID
                    FROM EMPLOYEE
                    WHERE MANAGER_ID IS NOT NULL);

SELECT절에 SUBQUERY 사용해서 다시 작성

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);

예제 2-2)

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

SELECT EMP_ID, EMP_NAME, JOB_NAME, 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 = '과장');

3. 다중 열 서브쿼리

예제 3-1)

퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급코드, 부서코드, 입사일 조회

SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE
                        FROM EMPLOYEE
                        WHERE ENT_YN = 'Y')
    AND JOB_CODE = (SELECT JOB_CODE
                        FROM EMPLOYEE
                        WHERE ENT_YN = 'Y')
    AND ENT_YN != 'Y';

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 ENT_YN = 'Y')
    AND ENT_YN != 'Y';

4. 다중 행 다중 열 서브쿼리

예제 4-1)

자기 직급의 평균 급여를 받고 있는 직원의 사번, 이름, 직급코드, 급여 조회
단, 급여 평균은 십만원 단위로 계산 (TRUNC(컬럼명, -5)

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, TRUNC(AVG(SALARY), -5)
                FROM EMPLOYEE
                GROUP BY JOB_CODE); 
--괄호 안 직급(코드) 별 평균 급여 
--여러개 비교 이기 때문에 절대 =쓰지말고 IN 쓰기

💡괄호 안 직급(코드) 별 평균 급여
💡여러개 비교 이기 때문에 절대 =쓰지말고 IN 쓰기

인라인 뷰

FROM 절에서 서브쿼리 사용

예제 1-1)

전 직원 중 급여가 높은 상위 5명의 순위, 이름, 급여, 조회

SELECT ROWNUM, EMP_NAME, SALARY
FROM (SELECT * -- ⭐or EMP_NAME, SALARY사용 가능 // 근데 이 경우 FROM절 밖 SELECT에서 이외 다른 컬럼 조회 안됨
    FROM EMPLOYEE
    ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

💡 순위를 매기는 상황에서는, 순서때문에 FROM 초창기에 정해진 순위 안에서 SALARY가 돌기 때문에 애초에 테이블을 짤 때 원하는 순위를 FROM절에서부터 서브쿼리 사용해서 지정해주고 짜야함

SELECT ROWNUM, 이름, 급여
FROM (SELECT EMP_NAME 이름, SALARY 급여 -- 인라인뷰에서 별칭을 SELECT에 해주었다면 FROM 절 밖에서도 별칭으로 적어줘야함// 이미 이 안에서 쿼리명 수정이 이루어졋기 떄문에
    FROM EMPLOYEE
    ORDER BY SALARY DESC)
WHERE ROWNUM <= 5;

SELECT ROWNUM, 전체.* -- ⭐*는 단독으로만 사용할 수 있지 ROWNUM 같이 기타 등등과 같이 쓸 수 없음 
-- 이 경우 FROM절의 서브쿼리에 대한 별칭을 지어주고 별칭.* 해주면 됨
FROM (SELECT EMP_NAME 이름, SALARY 급여
    FROM EMPLOYEE  
    ORDER BY SALARY DESC) 전체 
WHERE ROWNUM <= 5;

예제 1-2)

급여 평균 3위 안에 드는 부서의 부서코드와 부서명, 평균 급여 조회

SELECT DEPT_CODE, DEPT_TITLE, 평균급여
FROM(SELECT DEPT_CODE, DEPT_TITLE, AVG(SALARY) 평균급여
    FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    GROUP BY DEPT_CODE, DEPT_TITLE
    ORDER BY AVG(SALARY)DESC)
WHERE ROWNUM <= 3;

2 WITH : 서브쿼리 이름 붙일 때

WITH AVG3 AS (SELECT DEPT_CODE, DEPT_TITLE, AVG(SALARY) 평균급여
    FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
    GROUP BY DEPT_CODE, DEPT_TITLE
    ORDER BY AVG(SALARY)DESC)
SELECT DEPT_CODE, DEPT_TITLE, 평균급여
FROM AVG3
WHERE ROWNUM <= 3;

RANK() OVER / DENSE_RANK() OVER

• RANK() OVER

  같은 값이 있으면 공동 순위를 매기고 그 다음은 인원수만큼 제외함

SELECT RANK() OVER (ORDER BY SALARY DESC) 순위, EMP_NAME, SALARY
FROM EMPLOYEE;
-- 19등 19등 21등

• DENSE_RANK() OVER

  같은 값이 있으면 공동 순위를 매기고 그 다음은 인원수 제외않고 바로 집어넣음

SELECT RANK() OVER (ORDER BY SALARY DESC) 순위, EMP_NAME, SALARY
FROM EMPLOYEE;
-- 19등 19등 20등