SUBQUERY(서브쿼리)

▶ 서브쿼리(SUBQUERY)

• 서브쿼리(SUBQUERY)  : 하나의 SQL문 안에 포함된 또 다른 SQL(SELECT)문
• 메인쿼리(기존 쿼리)를 위해 보조 역할을 하는 쿼리문
• SELEC, FROM, WHERE, HAVING 절에서 사용 가능
  
  
• EX) 서브쿼리 예시 : 부서코드가 노옹철 사원과 같은 소속의 사원 명, 부서코드 조회
• 1) 사원 명이 노옹철인 사원의 부서 코드 조회

SELECT DEPT_CODE
FROM EMPLOYEE
WHERE EMP_NAME = '노옹철'; -- 결과 : D9

• 2) 부서코드가 'D9'인 사원 조회

SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

• 3) 부서코드가 노옹철 사원과 같은 소속의 사원 명, 부서코드 조회
  → 1, 2단계를 하나의 쿼리로 작성

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

▶ 서브쿼리 유형

• 단일행 (+ 단일열) 서브쿼리 : 서브쿼리에서 조회한 결과 행의 개수가 1개일 때
• 다중행 (+단일열) 서브쿼리 : 서브쿼리의에서 조회한 결과 행의 개수가 여러 개일 때
• 다중열 서브쿼리 : 서브쿼리의 SELECT절에 나열된 컬럼의 수(= 열의 개수)가 여러 개일 때
• 다중행 다중열 서브쿼리 : 서브쿼리에서 조회한 결과 행의 개수와 열의 개수가 여러 개일 때 
• 상관 서브쿼리 : 서브쿼리에서 조회한 결과 값을 메인쿼리가 비교연산할 때
                            메인쿼리 테이블의 값이 변경되면 서브쿼리의 결과값도 바뀌는 서브쿼리
• 스칼라 서브쿼리 : 상관 쿼리이면서 결과값이 하나인 서브쿼리
• ※ 서브쿼리 유형에 따라 서브쿼리 앞에 붙은 연산자가 다름

▶ 1. 단일행 서브쿼리 (SINGLE ROW SUBQUERY)

• 서브쿼리의 조회 결과값의 개수가 1개인 서브쿼리
• 단일행 서브쿼리 앞에는 비교 연산자(<, >, <=, >=, =, !=, ^=, <>) 사용

-- EX) 전 직원의 급여 평균보다 많은 급여를 받는 사원의
-- 이름, 직급, 부서, 급여를 직급 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE)
WHERE SALARY > (SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEE)
ORDER BY JOB_CODE;

-- EX) 부서별(부서가 없는 사람 포함) 급여의 합계 중 가장 큰 부서의
-- 부서명, 급여 합계 조회
SELECT DEPT_TITLE, SUM(SALARY) FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE SUM(SALARY) = (SELECT MAX(SUM(SALARY)) FROM EMPLOYEE GROUP BY DEPT_CODE;

▶ 2. 다중행 서브쿼리 (MULTI ROW SUBQUERY)

• 다중행 서브쿼리(MULTI ROW SUBQUERY) : 서브쿼리의 조회 결과 값의 개수가 여러 행인 경우
• 다중행 서브쿼리 앞에는 일반 비교 연산자 사용 X
• IN / NOT IN : 여러 개의 결과값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면, 이라는 의미
• > ANY / < ANY : 여러 개의 결과값 중에서 한 개라도 큰 경우, 가장 작은 값보다 큰가?
                              / 여러 개의 결과값 중에서 한 개라도 작은 경우, 가장 큰 값보다 작은가?
• > ALL / < ALL : 여러 개의 결과값의 모든 값보다 큰 경우, 가장 큰 값보다 큰가?
                           / 여러 개의 결과값의 모든 값보다 작은 경우, 가장 작은 값보다 작은가?
• EXISTS / NOT EXISTS : 값이 존재하는가? / 존재하지 않는가?

-- EX-1) 부서별 최고 급여를 받는 사원의 이름, 직급, 부서, 급여를 부서 순으로 정렬하여 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY FROM EMPLOYEE
WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE)
ORDER BY DEPT_CODE;

※ SELECT MAX(SALARY) FROM EMPLOYEE GROUP BY DEPT_CODE;
    : 다중 행(7행 1열) 결과값을 출력

• EX-2) 사수에 해당하는 직원의 정보(사번, 이름, 부서명, 직급명, 구분(사수/직원) 조회
• 1) 사수에 해당하는 사원 번호 조회

SELECT MANAGER_ID
FROM EMPLOYEE
WHERE MANAGER_ID IN NOT NULL;
-- [주의] WHERE MANAGER_ID != NULL; -> 오류 발생

• 사원의 사번, 이름, 부서명, 직급명 조회 ( 부서 없는 사람 포함)

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE);

• 3) 사수에 해당하는 직원에 대한 정보 조회(이때, 구분은 '사수')

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, 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);

• 4) 일반 직원에 해당하는 사원 정보 조회(이때, 구분은 '사원')

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, 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 EPMPLOYEE WHERE MANAGER_ID IS NOT NULL);

• 5) 3번과 4번의 조회 결과를 하나로 합침 → SELECT절 SUBQUERY(★ ★ ★ SELECT절에도 서브쿼리 사용 가능!)

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_NAME,
    CASE
        WHEN EMP_ID NOT 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);

• UNION 사용할 경우

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, 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_TITLE, 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 EPMPLOYEE WHERE MANAGER_ID IS NOT NULL);

• EX-3) 대리 직급의 사원 중에서 과장 직급의 최소 급여보다 많이 받는 사원의 사번, 이름, 직급, 급여를 조회
• 1) 모든 대리 직급 사원의 사번, 이름, 직급명, 급여 조회

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

• 2) 모든 과장 직급 사원의 급여 조회

SELECT SALARY
FROM EMPLOYEE
JOIN JOB USIGN (JOB_CODE)
WHERE JOB_NAME = '과장'

• 3) 모든 대리 직급 사원 중에서 과장 직급의 최소 급여보다 많이 받는 직원의
• 3-1) MIN을 이용하여 단일행 서브쿼리를 만듦

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

• 3-2) ANY를 이용하여 과장 직급 중에서 가장 급여가 적은 직원을 초과하는 대리 직급 사원 조회

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 WHERE JOB_NAME = '과장')
ORDER BY SALARY;

• A > ANY B : A가 B의 여러 결과 중에서 하나라도 크면 TRUE → A가 B의 최소값보다 큰 경우
• A < ANY B : A가 B의 여러 결과 중에서 하나라도 작으면 TRUE → A가 B의 최대값보다 작은 경우

-- EX) 차장 직급의 가장 높은 급여보다 많이 받는 과장 직급 사원의 정보(사번, 이름, 직급명, 급여) 조회
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY FROM EMPLOYEE
JOIN JOB USING (JOB_CODE)
WHERE JOB_NAME = '과장'
AND SALARY > ALL (SELECT SALARY FROM EMPLOYEE NATURAL JOIN JOB WHERE JOB_NAME = '차장');

• 서브쿼리 중첩 사용
• EX) LOCATION 테이블에서 NATIONAL_CODE가 KO인 경우, LOCAL_CODE와
         DEPARTMENT 테이블의 LOCATION_ID가 동일한 DEPT_ID가
         EMPLOYEE 테이블의 DEPT_CODE와 동일한 사원을 구하시오.

SELECT EMP_NAME, DEPT_CODE, DEPT_ID, LOCAL_CODE
FROM EMPLOYEE
JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN LOCATION ON (LOCATION_ID = LOCAL_CODE)
WHERE NATIONAL_CODE = 'KO';

• 1) LOCATION 테이블에서 NATIONAL_CODE가 KO인 경우의 LOCAL_CODE

SELECT LOCAL_CODE
FROM LOCATION
WHERE NATIONAL_CODE = 'KO';

• 2) DEPARTMENT 테이블에서 위의 결과와 동일한 LOCATION_ID를 가진 DEPT_ID 조회

SELECT DEPT_ID
FROM DEPARTMENT
WHERE LOCATION_ID = (SELECT LOCAL_CODE FROM LOCATION WHERE NATIONAL_CODE = 'KO');

• 3) EMPLOYEE 테이블에서 위의 결과와 동일한 DEPT_CODE를 가진 사원 정보 조회

SELECT EMP_NAME, DEPT_CODE, DEPT_ID, LOCAL_CODE
FROM EMPLOYEE
WHERE DEPT_CODE IN (SELECT DEPT_ID FROM DEPARTMENT 
                    WHERE LOCATION_ID = (SELECT LOCAL_CODE FROM LOCATION
                                         WHERE NATIONAL_CODE = 'KO');

• → 한국에 있는 부서에서 일하는 사원 정보 조회와 같은 결과 값

▶ 다중열 서브쿼리 (단일행 = 결과값은 한 행)

• 다중열 서브쿼리 : 서브쿼리 SELECT절에 나열된 컬럼의 수가 여러 개일 때
  
  
• EX) 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원의 이름, 직급, 부서, 입사일 조회
• 1) 퇴사한 여직원 조회

SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE SUBSTR(EMP_NO,8,1) = 2;
AND ENT_YN = 'Y';

• 2) 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 사원

SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE
                   WHERE SUBSTR(EMP_NO,8,1) = 2
                   AND ENT_YN = 'Y')
AND JOB_CODE = (SELECT JOB_CODE FROM EMPLOYEE
                WHERE SUBSTR(EMP_NO,8,1) = 2
                AND ENT_YN = 'Y');

• 다중열 서브쿼리로 작성할 경우

SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CDOE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE FROM EMPLOYEE
                               WHERE SUBSTR(EMP_NO,8,1) = 2
                               AND ENY_YN = 'Y');

• [주의] 다중열 서브쿼리에서 WHERE절 조건 작성 시 컬럼은 반드시 순서대로 작성!

▶ 다중행 다중열 서브쿼리

• 다중행 다중열 서브쿼리 : 서브쿼리 조회 결과 행의 개수와 열의 개수가 여러 개인 경우
  
  
• EX) 본인 직급의 평균 급여를 받고 있는 직원의 사번, 이름, 직급, 급여를 조회
         단, 급여와 급여 평균은 만원 단위로 계산 [ TRUNC(컬럼명,-4) ]
• 1) 급여를 200만원, 600만원 받는 직원 (200만원, 600만원이 평균 급여라고 가정)

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (2000000, 6000000);

• 2) 직급별 평균 급여 (만원 단위)

SELECT JOB_CODE, TRUNC(AVG(SALARY),-4)
FROM EMPLOYEE
GROUP BY JOB_CODE;

• 3) 본인 직급의 평균 급여를 받고 있는 직원

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, TRUNC(AVG(SALARY),-4) FROM EMPLOYEE
                             GROUP BY JOB_CODE);

▶ 상[호연]관 서브쿼리

• 상관 서브쿼리에선 메인쿼리가 사용하는 테이블 값을 서브쿼리가 이용해서 결과를 만듦
• 메인쿼리의 테이블 값이 변경되면 서브쿼리의 결과 값도 바뀌게 되는 구조
  
  
• 1) 메인쿼리 1행 해석
• 2) 해석된 메인쿼리 1행을 이용해서 서브쿼리 조회
• 3) 서브쿼리 결과를 이용해서 메인쿼리 해석 중인 1행을 대상으로 조회
  
  
• EX) 직급별 급여 평균보다 급여를 많이 받는 직원의 이름, 직급코드, 급여 조회
• 1) 직급별 평균 급여 조회

SELECT JOB_CODE, AVG(SALARY)
FROM EMPLOYEE
GROUP BY JOB_CODE;

• 2) 메인쿼리의 각 행을 이용해 서브쿼리 WHERE절 조건 변경
       → 다시 메인쿼리 각 행 조건 비교 → 결과 값 조회

SELECT EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE E1
WHERE SALARY > (SELECT AVG(SALARY) FROM EMPLOYEE E2
                 WHERE E2.JOB_CODE = E1.JOB_CODE);

※ 서브쿼리가 각 JOB_CODE에 대한 평균 급여를 계산[단일 결과 값 반환]하고 있기 때문에, GROUP BY로 묶을 필요 없음

• EX-2) 부서별 입사일이 가장 빠른 사원의
  사번, 이름, 부서명(NULL인 경우 '소속없음'), 직급명, 입사일 조회
  단, 입사일은 가장 빠른 순서로 정렬 / 퇴사한 직원은 제외
• 1) 특정 부서에서 가장 빠른 입사일 (예를 들어 부서코드가 'D1'인 부서에서 가장 빠른 입사일)

SELECT MIN(HIRE_DATE)
FROM EMPLOYEE
WHERE DEPT_CODE = 'D1';

• 상관쿼리로 풀이

SELECT EMP_ID, EMP_NAME, NVL(DEPT_TITLE, '소속없음')
FROM EMPLOYEE E1
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
JOIN JOB USING (JOB_CODE)
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEE E2
                    WHERE (E2.DEPT_CODE = E1.DEPT_CODE
                    OR (E1.DEPT_CODE IS NULL AND E2.DEPT_CODE IS NULL))
                    AND ENT_YN = 'N')
ORDER BY HIRE_DATE;

• EX-3) 사수가 있는 직원의 사번, 이름, 부서명, 사수사번 조회
• EXISTS : 서브쿼리에 해당하는 행이 1개라도 존재하면 조회결과에 포함
• 1) 사수 사번과 일치하는 사번 ( 예를 들어 사수 사번이 200번인 경우)

SELECT * FROM EMPLOYEE
WHERE EMP_ID = 200;

• 상관쿼리로 풀이

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, MANAGER_ID
FROM EMPLOYEE E1
LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
WHERE EXISTS (SELECT EMP_ID FROM EMPLOYEE E2
               WHERE E2.EMP_ID = E1.MANAGER_ID);

※ 존재하는 사번만 조회됨

▶ 스칼라 서브쿼리

• SELECT절에 사용되는 서브쿼리 결과로 1행(단일행)만 반환
  → SELECT절에 작성하는 단일행 서브쿼리
  ※ SQL에서 단일 값을 '스칼라'라고 함

-- EX) 각 직원들이 속한 직급의 평균 급여 조회 (스칼라 + 상관쿼리)
SELECT EMP_NAME, JOB_CODE, SALARY, (SELECT FLOOR(AVG(SALARY)) FROM EMPLOYEE E2 WHERE E2.JOB_CODE = E1.JOB_CODE) 평균급여
FROM EMPLOYEE E1

-- EX-2) 모든 사원의 사번, 이름, 관리자 사번, 관리자명을 조회
-- 단, 관리자가 없는 경우, '없음'으로 표시
SELECT EMP_ID 사번, EMP_NAME 사원명, NVL(MANAGER_ID,'없음') "관리자 사번", 
NVL((SELECT E2.EMP_NAME FROM EMPLOYEE E2 WHERE E2.EMP_ID = E1.MANAGER_ID),'없음') '관리자명'
FROM EMPLOYEE E1;

▶ 인라인 뷰 (INLINE-VIEW)

• FROM절에서 서브쿼리를 사용하는 경우로, 서브쿼리가 만든 결과의 집합(RESULT SET)을
  테이블 대신에 사용
  
  
• EX) 인라인 뷰를 활용한 TOP-N 분석
  전 직원 중 급여가 높은 상위 5명의 순위, 이름, 급여 조회
• 1) 급여 높은 순서로 조회

SELECT SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;

• 2) 조회되는 행 앞에 1부터 순서대로 1씩 증가하는 번호 붙이기
  - ROWNUM : 행 번호를 나타내는 가상 컬럼(1부터 1씩 증가)

SELECT ROWNUM, EMP_NAME
FROM EMPLOYEE;

• 3) ROWNUM을 조건에 사용하기

SELECT ROWNUM, EMP_NAME
FROM EMPLOYEE
WHERE ROWNUM <= 5;

• 4) 1,2,3번을 토대로 급여 상위 5명 조회 시도

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

※ SELECT문 해석 순서를 고려하지 않아서 원하는 결과 값이 조회되지 않았음
    → 이를 해결하기 위해서 [인라인 뷰] 필요

• 해결 1) 급여 내림차순 조회

SELECT EMP_NAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;

※ 조회 결과(RESULT SET)를 가상의 테이블(= VIEW)로 취급할 예정

• 2) 해결 1번의 조회 결과를 FROM절에 사용한 후 상위 5행만 조회

SELECT EMP_NAME, SALARY
FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY DESC)
--> FROM절 내부에 포함된 가상의 테이블 == 인라인 뷰
WHERE ROWNUM <= 5;

-- EX) 평균 급여가 상위 3위 안에 드는 부서의 부서코드와 부서명, 평균 급여 조회
SELECT ROWNUM, DEPT_CODE, DEPT_TITLE, 평균급여
FROM (SELECT DEPT_CODE, DEPT_TITLE, FLOOR(AVG(SALARY)) 평균급여 FROM EMPLOYEE
       LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
       GROUP BY DEPT_CODE, DEPT_TITLE
       ORDER BY 평균급여 DESC)
WHERE ROWNUM <= 3;

※ FROM절에서 평균급여 별칭 부여 후, 내부쿼리의 ORDER BY절과 메인쿼리의 SELECT절에서 별칭 사용
메인쿼리의 SELECT절에서 별칭 미사용 시 오류 발생

▶ WITH

• 서브쿼리에 이름을 붙여주고 사용 시 이름을 사용
• 인라인 뷰로 사용될 서브쿼리에 주로 사용됨 / 실행속도가 빨라지는 장점이 있음

-- EX) 전 직원의 급여 높은 순으로 순위, 이름, 급여 조회
WITH TOP_SAL AS (SELECT EMP_NAME, SALARY FROM EMPLOYEE
                 ORDER BY SALARY DESC)
                 
SELECT ROWNUM, EMP_NAME, SALARY
FROM TOP_SAL;

※ WITH로 이름 지정하고 ;로 마감처리하면 안 됨 [오류 발생]

▶ RANK( ) OVER / DENSE_RANK( ) OVER

• RANK( ) OVER : 동일한 순위 이후의 등수를 동일한 인원 수만큼 건너뛰고 순위 계산
                              예를 들어, 공동 1위가 2명이면 다음 순위는 3위

SELECT RANK() OVER(ORDER BY SALARY DESC) AS 순위, EMP_NAME, SALARY
FROM EMPLOYEE;

※ 19등이 2명일 때, 바로 다음 순위 21등으로 건너 뜀

• DENSE_RANK( ) OVER : 동일한 순위 이후의 등수를 이후의 순위로 계산
                                            예를 들어, 공동 1위가 2명이어도 다음 순위는 2위

SELECT DENSE_RANK() OVER(ORDER BY SALARY DESC) AS 순위, EMP_NAME, SALARY
FROM EMPLOYEE;

※ 19등이 2명일 때, 바로 다음 순위 20등