ORACLE 05. SELECT_ 서브쿼리(SUBQUERY)

•  서브쿼리(SUBQUERY)
      - 하나의 SQL문 안에 포함된 또 다른 SELECT문 
      - 메인 SQL문을 보조역할 하는 쿼리문 
      - 비교연산자의 오른쪽에 기술해야하며, 반드시 괄호로 묶어야함
      - 서브쿼리와 비교할 항목은 반드시 서브쿼리의 SELECT한 항목의 개수와 자료형을 일치시켜야함 

•   서브쿼리의 구분 
      서브쿼리 수행 결과값이 몇행, 몇열이냐에 따라서 분류됨 
        
      - 단일행[단일열] 서브쿼리 : 서브쿼리의 조회 결과값이 오로지 1개일 때 (1행1열)
      - 다중행[단일열] 서브쿼리 : 서브쿼리의 조회 결과값이 여러행일 때 (여러행 1열(컬럼1개))
      -[단일행] 다중열 서브쿼리 : 서브쿼리의 조회 결과값이 한행이지만 컬럼이 여러개일 때 (한행, 여러열)
      - 다중행 다중열 서브쿼리  : 서브쿼리의 조회 결과값이 여러행 여러컬럼일 때

---

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

서브쿼리의 조회 결과값이 오로지 1개일 때
 일반 비교연산자 사용 가능  (=  !=  ^=  <>   >  <  >=  <=)

예시1) 전 직원의 평균 급여보다 급여를 더 적게 받는 사원들의 사원명, 직급코드, 급여 조회

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

예시2)전지연 사원과 같은 부서원들의 사번, 이름, 입사일, 직급명 조회(전지연은 제외) --> JOIN 사용 

SELECT EMP_ID, EMP_NAME,HIRE_DATE, JOB_NAME 
FROM EMPLOYEE E
JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
WHERE DEPT_CODE = (SELECT DEPT_CODE 
                     FROM EMPLOYEE 
                    WHERE EMP_NAME = '전지연')
AND EMP_NAME != '전지연';

예시 3) 부서별 급여합이 가장 큰 부서의 부서코드, 급여합 조회

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

 

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

    서브쿼리를 수행한 결과값이 여러행일 때 (컬럼은 하나)
     다중행 서브쿼리 앞에는 일반 비교연산자 사용불가 
   

 - IN 서브쿼리 : 여러개의 결과값 중에서 한개라도 일치하는 값이 있으면 조회 
    
      비교대상 IN (값1, 값2, 값3)
      비교대상 = 값1 OR 비교대상 = 값2 OR 비교대상 = 값3 
    
  -  >  ANY 서브쿼리 : 여러개의 결과값 중에서 "한개라도" 클 경우 (여러개의 결과값 중에 가장 작은값보다 클경우)
      <  ANY 서브쿼리 : 여러개의 결과값 중에서 "한개라도" 작을 경우 (여러개의 결과값 중에서 가장 큰값보다 작을 경우)
      
      비교대상 > ANY (값1,값2,값3)
      비교대상 > 값1 OR 비교대상 > 값2 OR 비교대상 > 값3
      
  -  >  ALL 서브쿼리 : 여러개의 "모든" 결과값들 보다 클 경우 
      <  ALL 서브쿼리 : 여러개의 "모든" 결과값들 보다 작을 경우
      
      비교대상 > ALL(값1,값2,값3)
      비교대상 값1 AND 비교대상 > 값2 AND 비교대상 >값3 

 

예시1) 유재식 또는 윤은해 사원과 같은 직급인 사원들 사번, 사원명, 직급코드 , 급여

SELECT EMP_ID, EMP_NAME, DEPT_CODE,SALARY
FROM EMPLOYEE
WHERE JOB_CODE IN (SELECT JOB_CODE
                    FROM EMPLOYEE
                   WHERE EMP_NAME IN ('유재식', '윤은해'));

 

예시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) -- 2200000,2500000,3760000
                   WHERE JOB_NAME = '과장');

 

3. 다중열 서브쿼리 

 결과값은 한행이지만 나열된 칼럼수가 여러개일 경우  
 비교연산자 사용 가능

예시 1) 하이유사원과 같은부서, 같은 직급인 사원들의 사원명, 부서코드 ,직급코드,입사일 조회

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 = '하이유');

 

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

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

예시 1) 각 직급별 최소급여를 받는 사원 조회 (사번, 사원명, 직급코드, 급여)

SELECT EMP_ID, EMP_NAME, JOB_CODE, SALARY
FROM EMPLOYEE
WHERE (JOB_CODE,SALARY) IN (SELECT JOB_CODE ,MIN(SALARY)
                             FROM EMPLOYEE
                         GROUP BY JOB_CODE);

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

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