D30 오라클5 <서브쿼리>

20241101 D30

<SUBQUERY 서브쿼리>

• 하나의 주된 SQL(SELECT, CREATE, INSERT, UPDATE ...) 안에 포함된 또 하나의 SELECT문
• 메인 SQL문을 보조하기 위해 사용한다.
  SELECT문 -> SELECT, FROM, WHERE, HAVING 등 다양한 위치에사용,그때마다 부르는 명칭이 다르다.
• SELECT -> 스칼라 쿼리 -> 하나의 값(스칼라)을 반환해주는 서브쿼리라는 의미.
• FROM -> 인라인 뷰 -> 서브쿼리 조회결과를 테이블처럼 사용할때 사용.
• WHERE, HAVING -> 스칼라 쿼리, 프래디케이트 쿼리 -> 조건검사에 사용되는 서브쿼리.

-- 노옹철 사원과 같은 부서인 사원들을 조회.
SELECT EMP_NAME, DEPT_CODE
FROM EMPLOYEE 
-- WHERE DEPT_CODE = 'D9'
WHERE DEPT_CODE =(
                  SELECT DEPT_CODE 
                  FROM EMPLOYEE
                  WHERE EMP_NAME = '노옹철'
                 );
 
 -- 전체 사원의 평균 급여보다 더 많은 급여를 받고 있는 사원들의 사번, 이름 직급코드를 조회
 SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY
 FROM EMPLOYEE
 WHERE SALARY > (
                SELECT AVG(SALARY)
                FROM EMPLOYEE
                );

서브쿼리 구문

• 서브쿼리를 수행한 결과값이 몇행 몇열에 따라 분류
• 단일행(단일열) 서브쿼리 : 서브쿼리를 수행 한 결과 값이 1개(스칼라)
• 다중행(단일열) 서브쿼리 : 서브쿼리를 수행 한 결과 값이 여러 행
• (단일행) 다중열 서브쿼리 : 서브쿼리를 수행 한 결과 값이 여러 열
• 다중행 다중열 서브쿼리 : 서브쿼리를 수행 한 결과 값이 여러 행과 열

1. 단일행 (단일열) 서브쿼리 (SINGE ROW SUBQUERY)

• 서브쿼리의 조회 결과 값이 1개인 경우 사용.
• 일반 연사자 사용 가능.(=, !=, <, >)

 -- 최저 급여를 받는 사원의 사번, 사원명, 직급코드, 급여, 입사일 조회
 SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY, HIRE_DATE
 FROM EMPLOYEE
 WHERE SALARY = (
                 SELECT MIN(SALARY)
                 FROM EMPLOYEE
                 );
 
 -- 노옹철 사원의 급여보다 더 많이 받는 사원들의 사번, 이름, 부서명, 급여 조회
 SELECT EMP_ID, EMP_NAME, SALARY, DEPT_TITLE
 FROM EMPLOYEE
 WHERE EMP_NAME = '노옹철';
 
 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 DEPT_CODE, DEPT_TITLE, SUM(SALARY) 
FROM EMPLOYEE
LEFT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
GROUP BY DEPT_CODE, DEPT_TITLE
HAVING SUM(SALARY ) = (
                       SELECT MAX (SUM(SALARY)) 
                       FROM EMPLOYEE 
                       GROUP BY DEPT_CODE
                       );

2. 다중행 (단일열) 서브쿼리 (MULTI ROW SUBQUERY)

• 서브쿼리로 조회한 결과값이 여러행인 경우 사용
• IN (10, 20, 30) 서브쿼리
  : 여러개의 결과 값 중 하나라도 일치하면 참, 없다면 거짓
• (>, <)ANY (10, 20, 30) 서브쿼리
  : 여러개의 결과 값 중 "하나라도" 크거나(>) 작을(<)경우 참/거짓을 반환
• (<, >)ALL (10, 20, 30) 서브쿼리
  : 여러개의 결과 값 모두 크거나, 작은 경우 참 / 거짓을 반환

 -- 각 부서별 최고급여를 받는 사원의 이름, 직급코드, 급여 조회
 SELECT DEPT_CODE, MAX(SALARY)
 FROM EMPLOYEE
 GROUP BY DEPT_CODE;
 
 SELECT EMP_NAME, JOB_CODE, SALARY
 FROM EMPLOYEE
 WHERE SALARY IN ( SELECT MAX(SALARY)
                   FROM EMPLOYEE
                   GROUP BY DEPT_CODE);
            --(8000000, 3900000, 3760000, ...)
  
  -- 선동일 또는 유재식 사원과 같은 부서인 사원들을 조회(사원명, 부서코드. 급여)
  SELECT EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE 
  WHERE DEPT_CODE = 'D9';
  
  SELECT EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE 
  WHERE DEPT_CODE = 'D6';
  
  SELECT EMP_NAME, DEPT_CODE, SALARY
  FROM EMPLOYEE
  WHERE DEPT_CODE IN (
                     SELECT DEPT_CODE
                     FROM EMPLOYEE
                     WHERE EMP_NAME IN('선동일' ,'유재식')
                     );
  
 -- 이오리 또는 하동운 사원과 같은 직급인 사원들을 조회( 사원명 , 직급코드, 부서코드, 급여)
 SELECT EMP_NAME, JOB_CODE, DEPT_CODE, SALARY
 FROM EMPLOYEE
 WHERE JOB_CODE IN (
                    SELECT JOB_CODE
                    FROM EMPLOYEE 
                    WHERE EMP_NAME IN('하동운', '이오리')
                    );
 
-- 대리 직급 임에도 불구하고, 과장 직급의 급여보다 많이 받는 사원들을 조회
-- 사번, 이름, 직급명, 급여
--1) 전체 과장직급의 급여 조회
 SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
 FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
    AND JOB_NAME = '과장';
    
    
 --2) 대리직급 급여 조회   
 SELECT  SALARY
 FROM EMPLOYEE E, JOB J
 WHERE E.JOB_CODE = J.JOB_CODE
    AND JOB_NAME = '대리';    
 
 --3) 위 코드 하나로 합치기 1) + 2)
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
    AND JOB_NAME = '과장'
    AND SALARY <= ANY(SELECT  SALARY
                                FROM EMPLOYEE E, JOB J
                                WHERE E.JOB_CODE = J.JOB_CODE
                                AND JOB_NAME = '대리' ); 
 
 
 
-- 과장 직급임에도 불구 모든 차장 직급의 급여보다 더 많이 받는 직원 조회
--사번, 이름, 직급명, 급여
SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E, JOB J
WHERE E.JOB_CODE = J.JOB_CODE
    AND JOB_NAME = '과장' 
    AND SALARY >= ANY(  
                      SELECT SALARY
                      FROM EMPLOYEE E, JOB J
                      WHERE E.JOB_CODE = J.JOB_CODE
                      AND JOB_NAME = '차장'
                      );


SELECT EMP_ID, EMP_NAME, JOB_NAME, SALARY
FROM EMPLOYEE E
JOIN JOB J USING(JOB_CODE);
WHERE JOB_NAME = '과장'
    AND SALARY >= ALL(SELECT SALARY
                      FROM EMPLOYEE 
                      JOIN JOB USING(JOB_CODE)
                      WHERE  JOB_NAME = '차장'
                     );
 
 
 -- 사수가 존재하는 사원의 사번, 사원명 사수번호
 -- EXIST(서브쿼리)
 -- 서브쿼리로 반환된 행이 존재 하는 경우 TRUE , 하나도 없는 경우 FALSE
 SELECT EMP_ID, EMP_NAME, MANAGER_ID
 FROM EMPLOYEE E1
 WHERE EXISTS ( SELECT 1
                       FROM EMPLOYEE E2
                       WHERE E1.MANAGER_ID IS NOT NULL
                       AND E1.EMP_ID = E2.EMP_ID
                        );

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

• 서브쿼리 조회 결과가 한 행이지만, 나열된 컬럼의 갯수가 여러개인 겨우

-- 1) 하이유 사원의 같은 부서코드와 같은 직급코드에 해당되는 사원 조회
-- 사원명 부서코드 직급코드 고용일
SELECT DEPT_CODE, JOB_CODE
FROM EMPLOYEE 
WHERE EMP_NAME = '하이유';  -- D5/J5
 
-- 2) 부서코드가 D5이고, 직급코드가 J5인 사원 조회
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' AND JOB_CODE = 'J5';
 
-- 다중열 서브쿼리 
-- 비교할 값들의 순서를 맞춰서 동시비교
-- (비교대상칼럼1, 비교대상칼럼2) = (비교값1, 비교값2) 
-- 단, 비교값 들은 반드시 서브쿼리 형식으로 제시해야함.
SELECT EMP_NAME, DEPT_CODE, JOB_CODE, HIRE_DATE
FROM EMPLOYEE
WHERE (DEPT_CODE, JOB_CODE ) = (SELECT DEPT_CODE, JOB_CODE 
                                FROM EMPLOYEE 
                                WHERE EMP_NAME= '하이유' );
                                               
-- 박나라 사원과 같은 직급코드, 같은 사수사번을 가진 사원들의 
--  (사번 이름 직급코드 조회)
SELECT EMP_NAME, DEPT_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE EMP_NAME = '박나라';  --D5 / 207

SELECT EMP_NAME, JOB_CODE, MANAGER_ID
FROM EMPLOYEE
WHERE (JOB_CODE, MANAGER_ID) = (SELECT JOB_CODE, MANAGER_ID
                                FROM EMPLOYEE
                                WHERE EMP_NAME = '박나라'
                                );

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

• 조회결과가 여러행 여러열로 이루어진 서브쿼리

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

-- 2) 위 리스트중 일치하는 사원을 조회.
SELECT EMP_NAME, EMP_ID, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE, SALARY) IN (SELECT JOB_CODE, MIN(SALARY)
                             FROM EMPLOYEE
                             GROUP BY JOB_CODE
                             );

-- 각 부서별 최고 급여를 받는 사원들 조회 (사번, 이름, 부서코드, 급여)
-- 다중행 다중열 서브쿼리 활용
-- 부서가 없을 경우 NULL값이 아닌 부서코드를 '없음' 으로 조회

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

SELECT EMP_ID, EMP_NAME, NVL(DEPT_CODE, '없음'), SALARY
FROM EMPLOYEE
WHERE (NVL(DEPT_CODE, 1), SALARY) IN  (SELECT NVL(DEPT_CODE, 1) , MAX(SALARY)
                                       FROM EMPLOYEE
                                       GROUP BY DEPT_CODE
                                       )

5. 스칼라 쿼리

• 단일행 단일열 서브쿼리의 일종으로 SELECT절에서 사용되는 서브쿼리를 지칭함.
• SELECT 절이 실행 될때 마다 서브쿼리문이 실행되면서 조회결과 값을 반환함.
• 현재 조회 된 행의 "칼럼값"을 서브쿼리 내부에서 기술 할 수 있다.
• 단점으로는, 매 행 마다 실행되기 때문에 대규모 데이터 처리시 성능이 떨어 질수 있다.

-- 직원번호, 직원명, 부서명(스칼라쿼리)
-- JOIN 방식
SELECT EMP_ID,
       EMP_NAME,
       DEPT_TITLE
FROM EMPLOYEE
JOIN DEPARTMENT ON DEPT_CODE = DRPT_ID;

--서브 쿼리 방식
SELECT EMP_ID, --2번실행
       EMP_NAME,
      (SELECT DEPT_TITLE FROM DEPARTMENT WHERE DEPT_CODE = DEPT_ID ) 부서명
FROM EMPLOYEE; --1번실행

-- 사번, 사원명, 부서코드, 직급명 조회
SELECT EMP_ID, EMP_NAME, DEPT_CODE
      ,(SELECT JOB_NAME FROM JOB WHERE JOB_CODE = EMPLOYEE.JOB_CODE) 직급명
FROM EMPLOYEE;        

6. 인라인 뷰(INLINE VIEW)

• FROM절에서 사용하는 서브쿼리를 지칭.
• 서브쿼리를 수행한 결과를 테이블 대신에 사용( 파생테이블)

-- 보너스 포함 연봉이 3000만원 이상인 사원들의 사번, 이름, 보너스포함 연봉, 부서코드
SELECT 
	EMP_ID,
    EMP_NAME,
    (SALARY + SALARY * NNL(BONUS, 0)) *12 보너스포함연봉, 
    DEPT_CODE
FROM EMPLOYEE
WHERE 보너스포함연봉 >= 3000000; --2 (에러발생);

SELECT *         --5실행
FROM (            --1실행
   SELECT EMP_ID, 
   		  EMP_NAME,
     (SALARY + SALARY * NVL(BONUS, 0)) * 12  "보너스포함연봉", DEPT_CODE --3실행
   FROM EMPLOYEE  --2실행
     ) E
WHERE 보너스포함연봉 >= 30000000;   --4실행 

-- 인라인뷰 사용 예시
-- TOP-N 분석 : 데이터베이스 상에 있는 자료 중 최상위 N개의 자료를 보기 위해 사용하는 기능
-- 전 직원 중 급여가 가장 높은 상위 5명을 조회 
-- (순위, 사원명, 급여)


SELECT EMP_NAME,
       SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;
--오라클 기준 
-- * ROWNUM : 오라클에서 제공하는 칼럼, 조회된 순서대로 1부터 순번을 부여해주는 칼럼.
SELECT   ROWNUM,
         EMP_NAME,
         SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC;

-- 인라인뷰 사용 INLINE VIEW
SELECT ROWNUM, EMP_NAME, SALARY
FROM(
     SELECT   
        EMP_NAME,
        SALARY
     FROM EMPLOYEE
     ORDER BY SALARY DESC
    )E
WHERE ROWNUM <= 5;

-- 각 부서별 평균 급여가 높은 3개의 부서의 부서코드, 평균급여 조회
--1) 부서별 평균 급여 > 높은순으로
SELECT DEPT_CODE, ROUND(AVG(SALARY)) 평균급여
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY 평균급여 DESC
;

-- 2) 1번의 쿼리문을 인라인뷰로 사용한 후 순위부여
SELECT ROWNUM, /*E.* */  평균급여
FROM( 
    SELECT DEPT_CODE, ROUND(AVG(SALARY)) 평균급여
    FROM EMPLOYEE
    GROUP BY DEPT_CODE
    ORDER BY 평균급여 DESC
     ) E
WHERE ROWNUM <= 3;         
-- 어떤 순위를 부여할 때에는 먼저 정렬된 결과값 (RESULT TABLE)을 
-- INLINE VIEW로 만들고 그 후 메인쿼리문에서 ROWNUM 을 사용하여 순위를 정한다

-- 가장 최근에 입사한 사원 5명 조회  사원명, 급여, 입사일
SELECT ROWNUM, E.*
FROM
   (SELECT EMP_NAME, SALARY, HIRE_DATE
    FROM EMPLOYEE
    ORDER BY HIRE_DATE DESC ) E
WHERE ROWNUM <= 5; 

-- WITH 절
-- 서브쿼리의 결과를 미리 정의하여 재사용 하는 테이블을 작성하는 방법 == 인라인뷰를 저장하여 사용하는 방식
-- WITH 임시테이블명 AS (서브쿼리)
WITH EMP_HIRE AS (
    SELECT EMP_NAME, SALARY, HIRE_DATE
    FROM EMPLOYEE
    ORDER BY HIRE_DATE DESC
)
SELECT EMP_NAME, SALARY, HIRE_DATE
FROM EMP_HIRE
WHERE ROWNUM <= 5;

-- 윈도우 함수를 통한 순위 매기기.
-- SALARY를 가장 많이 받는 상위 5명 사원 조회
SELECT EMP_NAME, SALARY, ROW_NUMBER() OVER(ORDER BY SALARY DESC) 순위
FROM EMPLOYEE;
--WHERE 순위 <= 5; 사용 불가
--ROW_NUMBER() OVER(ORDER BY SALARY DESC)
-- 직접 호출도 불가능 윈도우 함수는 항상 SELECT절에서만 사용

WITH EMP_SAL_DESC AS(SELECT 
						EMP_NAME, 
                        SALARY, 
                        ROW_NUMBER() OVER(ORDER BY SALARY DESC) 순위
     				FROM EMPLOYEE
                  	)
SELECT EMP_NAME, SALARY, 순위
FROM EMP_SAL_DESC
WHERE 순위 <=5;