SQL - 09 서브쿼리 1

SUBQUERY

하나의 SQL문 안에 포함된 또다른 SQL(오직 SELECT)문

서브쿼리의 예시

-- 1) 사원명이 노옹철인 사람의 부서코드 조회
SELECT DEPT_CODE FROM EMPLOYEE
WHERE EMP_NAME = '노옹철';

-- 2) 부서코드가 D9인 직원을 조회
SELECT EMP_NAME, DEPT_CODE FROM EMPLOYEE
WHERE DEPT_CODE = 'D9';

-- 3) 부서코드가 노옹철사원과 같은 소속의 직원 명단 조회   
--> 위의 2개의 단계를 하나의 쿼리로바꾼다 
--> 1) 쿼리문을 서브쿼리로 전환
SELECT EMP_NAME, DEPT_CODE FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE
                   WHERE EMP_NAME = '노옹철'); --  서브쿼리

☆ 서브쿼리가 먼저 실행되고('D9') 그 다음 바깥쪽 SELECT가 수행된다. ☆

서브쿼리의 유형

1. 단일행(+단일열, 1행 1열) 서브쿼리
2. 다중행(+단일열) 서브쿼리
3. 다중열(+단일행) 서브쿼리
4. 다중행 다중열 서브쿼리
5. 상관 서브쿼리
6. 스칼라 서브쿼리

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

서브쿼리의 조회 결과 값의 개수가 1개인 서브쿼리

단일행 서브쿼리 앞에는 비교 연산자 사용한다

결과 값이 한 행이다

예시

-- 가장 적은 급여를 받는 직원의
-- 사번, 이름, 직급, 부서코드, 급여, 입사일을 조회
SELECT EMP_ID, EMP_NAME, JOB_CODE, DEPT_CODE, SALARY, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);

WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE);가
True가 되는 경우에만 테이블에서 보여준다.

단일행 서브쿼리 조건 나누기

문제

1. 부서별(부서가 없는 사람 포함) 급여의 합계 중 가장 큰 부서의 [내부 조건]
2. 부서명, 급여 합계를 조회 [외부 조건]

-- 1) 부서별 급여 합 중 가장 큰값 조회

/*
SELECT DEPT_CODE, SUM(SALARY) 
GROUP BY 하지 않으면 행의 갯수가 달라 오류가 난다
FROM EMPLOYEE
GROUP BY DEPT_CODE;
*/

SELECT MAX(SUM(SALARY)) 
FROM EMPLOYEE
GROUP BY DEPT_CODE;


-- 2) 부서별 급여합이 17700000인 부서의 부서명과 급여 합 조회
SELECT DEPT_TITLE, SUM(SALARY)
FROM EMPLOYEE LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
-- 문제의 조건 (부서가 없는 사람 포함)을 따라 DEPT_CODE가 없는 사람을
-- 추가하기 위해서 LEFT JOIN
GROUP BY DEPT_TITLE
HAVING SUM(SALARY) = 17700000;
-- 그룹으로 만들어진 행들에 조건 달기


-- 3) >> 위의 두 서브쿼리 합쳐 부서별 급여 합이 큰 부서의 부서명, 급여 합 조회
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);

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

서브쿼리의 조회 결과 값의 개수가 여러개일 때 사용한다

다중행 서브쿼리 앞에는 일반 비교연산자를 사용하지 않는다.

다중행 서브쿼리의 연산자

IN / NOT IN

여러 개의 결과값 중에서 한 개라도 일치하는 값이 있다면 혹은 없다면 이라는 의미 (가장 많이 사용한다)

문제

1. 사수에 해당하는 직원에 대해 조회 [내부 조건]
2. 사번, 이름, 부서명, 직급명, 구분(사수 / 직원) [외부 조건]

-- 1) 사수에 해당하는 사원 번호 조회
SELECT DISTINCT MANAGER_ID
FROM EMPLOYEE
WHERE MANAGER_ID IS NOT NULL;

-- 2) 직원의 사번, 이름, 부서명, 직급 조회(LEFT JOIN)
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_CODE
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE  = DEPT_ID)
JOIN JOB USING(JOB_CODE);


-- 3) 사수에 해당하는 직원에 대한 정보 추출 조회 (이때, 구분은 '사수'로)
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_CODE, '사수' 구분
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE  = DEPT_ID)
JOIN JOB USING(JOB_CODE)
-- WHERE EMP_ID IN (200, 201, 204 ...);
WHERE EMP_ID IN (SELECT DISTINCT MANAGER_ID -- 다중행 쿼리라서 IN 대신 
                                            -- '='가 안된다
                 FROM EMPLOYEE
                 WHERE MANAGER_ID IS NOT NULL);

-- 4) 일반 직원에 해당하는 사원들 정보 조회 (이때, 구분은 '사원'으로)
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_CODE, '사원' 구분
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE  = DEPT_ID)
JOIN JOB USING(JOB_CODE)
-- WHERE EMP_ID IN (200, 201, 204 ...);
WHERE EMP_ID NOT IN (SELECT DISTINCT MANAGER_ID
                 FROM EMPLOYEE
                 WHERE MANAGER_ID IS NOT NULL);

이때 3)과 4)는 CASE - END 구문을 사용해 하나로 합칠 수 있다.

SELECT EMP_ID, EMP_NAME, DEPT_TITLE, JOB_CODE, 

    --  CASE = if문과 유사
    CASE WHEN EMP_ID IN(SELECT DISTINCT MANAGER_ID
                        FROM EMPLOYEE
                        WHERE MANAGER_ID IS NOT NULL) -- 200, 201 ... 등 결과
        THEN '사수'
        ELSE '사원'

    END 구분

FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON (DEPT_CODE  = DEPT_ID)
JOIN JOB USING(JOB_CODE);

커서가 한 행(row)씩 이동하며 조회한다.
그 뒤 CASE 구문이 True라면 '사수'를 쓰고, False라면 '사원'을 쓴다.

ANY

여러 결과값의 요소(SUB)중 중 하나라도 다른 결과값(MAIN)의 요소보다 큰가 or 작은가

[비교 연산자] ANY -의 형태로 쓰인다.

문제

1. ANY 연산자를 사용해서
2. 대리 직급의 직원들 중에서(N개의 대리 중) [내부 조건1]
3. 과장 직급의 최소 급여보다 많이 받는 직원의 [내부 조건2]
4. 사번, 이름, 직급, 급여를 조회하세요[외부 조건]

-- 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 USING(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
              JOIN JOB USING(JOB_CODE)
              WHERE JOB_NAME = '과장');
             -- 여러 행이 나오면 비교 연산자 사용 불가
             -- 단일행이어야 사용 가능

-- 3-2) ANY를 이용하여 과장 중 가장 급여가 적은 직원 초과하는 대리를 조회
SELECT 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 = '과장');
-- 과장 급여가 220 250 376인데, 이 중 어느것 하나보다도 크면
-- MIN은 그룹 함수이고 ANY는 연산자라 더 빠르다

ALL

여러 결과값의 요소(SUB)가 다른 결과값(MAIN)의 모든 요소보다 큰가 or 작은가

[비교 연산자] ALL -의 형태로 쓰인다

문제

1. ALL 연산자를 이용해서
2. 차장 직급의 급여의 가장 큰 값보다 많이 받는 과장 직급의 직원의 [내부 조건]
3. 사번, 이름, 직급, 급여를 조회하세요 [외부 조건]

-- 외부 조건
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 
                 JOIN JOB USING (JOB_CODE)
                 WHERE JOB_NAME = '차장');

-- MAX로 바꾸면
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE
JOIN JOB USING(JOB_CODE)
WHERE JOB_NAME = '과장'
AND SALARY > (SELECT MAX(SALARY) 
              FROM EMPLOYEE 
              JOIN JOB USING (JOB_CODE)
              WHERE JOB_NAME = '차장');

EXISTS / NOT EXISTS

값이 존재하는가? / 존재하지 않는가?

서브쿼리에 해당하는 행이 1개라도 존재하면 조회결과에 포함한다.

-- EXIST: 서브쿼리에 해당하는 행이 1개라도 존재하면 조회결과에 포함
SELECT EMP_ID, EMP_NAME, DEPT_TITLE, MANAGER_ID
FROM EMPLOYEE MAIN
LEFT JOIN DEPARTMENT ON(DEPT_CODE = DEPT_ID)
-- 그냥 조회하면 사라진 번호가 나옴
-- 따라서 EMP_ID에 있는지 확인한 후 조회해야한다
WHERE EXISTS (SELECT EMP_ID FROM EMPLOYEE SUB
              WHERE MAIN.MANAGER_ID = SUB.EMP_ID);
--                 (214,216...)

서브쿼리 중첩사용 응용

문제

LOCATION 테이블에서 NATIONAL_CODE가 KO인 경우의 LOCAL_CODE와

DEPARTMENT 테이블의 LOCATION_ID와 동일한 DEPT_ID가

EMPLOYEE테이블의 DEPT_CODE와 동일한 사원을 구하시오.

-- 1) LOCATION 테이블을 통해 NATIONAL_CODE가 KO인 LOCAL_CODE 조회
SELECT LOCAL_CODE
FROM LOCATION
WHERE NATIONAL_CODE = 'KO'; -- L1 (단일행 서브쿼리)

-- 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
FROM EMPLOYEE
-- WHERE DEPT_CODE IN ('D1', 'D2', 'D3', 'D4', 'D9');
WHERE DEPT_CODE IN (SELECT DEPT_ID -- 다중행
                    FROM DEPARTMENT
                    WHERE LOCATION_ID = (SELECT LOCAL_CODE
                    /*D1 D2 D3 D4...*/   FROM LOCATION
                                         WHERE NATIONAL_CODE = 'KO')); -- 단일행
                                         /*L1*/

다중열 서브쿼리 (MULTI COLUMN SUBQUERY)

서브쿼리의 SELECT 절에 나열된 항목수가 여러개 일 때 사용한다

문제

1. 퇴사한 여직원과 같은 부서, 같은 직급에 해당하는 [내부 조건]
2. 사원의 이름, 직급, 부서, 입사일을 조회 [외부 조건]

-- 1) 퇴사한 여직원 조회
SELECT EMP_NAME, JOB_CODE, DEPT_CODE, HIRE_DATE
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_CODE, JOB_CODE) = (SELECT DEPT_CODE, JOB_CODE -- 다중열 서브쿼리
    /*조회되는 컬럼 맞추기*/     FROM EMPLOYEE
                               WHERE SUBSTR(EMP_NO, 8, 1) = 2
                               AND ENT_YN = 'Y');

다중행 다중열 서브쿼리

조회 결과 행 수와 열 수가 여러개일 때 사용한다

문제

1. 본인 직급의 평균 급여를 받고 있는 직원의 [내부 조건]
2. 사번, 이름, 직급, 급여를 조회하세요 [외부 조건]
3. 단, 급여와 급여 평균은 만원단위로 계산하세요 TRUNC(컬럼명, -4)

-- 1) 급여를 200, 600만 받는 직원 (200만, 600만이 평균급여라 생각 할 경우)
SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE SALARY IN (2000000, 60000000);

-- 2) 직급별 평균 급여
SELECT JOB_CODE, TRUNC( AVG(SALARY), -4 /*1000의자리까지 날아감*/ )
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);
-- 다중행이니 IN, ANY, ALL 사용