[SQL] SUB-QUERY 서브쿼리

한 SQL 문장 안에서 보조로 사용되는 또다른 SELECT 문

- SELECT, FROM, WHERE절 모두에 사용 가능
- INSERT, UPDATE, DELET, MERGE 문에서 사용 가능

WHERE절 예시

-- 전 사원의 평균 급여 이상을 받는 사원 수의 조회
-- WHERE절에 사용하려면 데이터 디멘션을 같게 맞춰야한다~
SELECT COUNT(*) FROM EMPLOYEES
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEES);

-- PARENT_ID가 없는 부서에 속한 사람의 수는?
SELECT COUNT(*) FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID 
                        FROM DEPARTMENTS 
                        WHERE PARENT_ID IS NULL);

-- 한 구문에서 두개 이상의 칼럼을 같이 조회 가능할까?
-- JOB_HISTORY 테이블에 있는 EMPLOYEE_ID,JOB_ID 
-- 두 값과 같은 건을 EMPLOYEES 테이블에서 찾아보자.
SELECT EMPLOYEE_ID,JOB_ID FROM EMPLOYEES
WHERE (EMPLOYEE_ID,JOB_ID) IN (SELECT EMPLOYEE_ID,JOB_ID 
                               FROM JOB_HISTORY);
                               
SELECT * FROM JOB_HISTORY;                               

-- SUB-QUERY 연관성이 있는 경우
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME
FROM DEPARTMENTS A
WHERE EXISTS (SELECT 1
              FROM JOB_HISTORY B
              WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID);

SELECT A.EMPLOYEE_ID,
      (SELECT B.EMP_NAME
       FROM EMPLOYEES B
       WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID) AS EMP_NAME,
       A.DEPARTMENT_ID,
      (SELECT B.DEPARTMENT_NAME
        FROM DEPARTMENTS B
        WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID) AS DEP_NAME
FROM JOB_HISTORY A;

-- 상위부서 ID가 90인 부서의 평균급여
SELECT DEPARTMENT_ID, ROUND(AVG(SALARY),1)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE PARENT_ID = 90)
GROUP BY DEPARTMENT_ID;

-- 부서이름도 같이 반환하고 싶다면?
SELECT A.DEPARTMENT_ID,B.DEPARTMENT_NAME, ROUND(AVG(A.SALARY),1)
FROM EMPLOYEES A, DEPARTMENTS B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE PARENT_ID = 90)
GROUP BY A.DEPARTMENT_ID, B.DEPARTMENT_NAME;

FROM 절 예시

-- 인라인뷰 :  FROM 절에 사용하는 서브쿼리
-- DEPT_ID가 90인 부서 산하에 있는 부서에 속한 사원의 평균 급여보다
-- 많은 급여를 받는 사원 목록
SELECT A.EMPLOYEE_ID, A.EMP_NAME, 
       B.DEPARTMENT_NAME, A.SALARY, E.AVG_SAL
FROM  EMPLOYEES A, DEPARTMENTS B,
     (SELECT ROUND(AVG(SALARY),1) AS AVG_SAL 
      FROM EMPLOYEES C, DEPARTMENTS D
      WHERE C.DEPARTMENT_ID = D.DEPARTMENT_ID
            AND D.PARENT_ID = 90
     ) E
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
      AND A.SALARY > E.AVG_SAL;