Oracle SQL(서브쿼리)

서브 쿼리란 한 SQL 문장 안에서 보조로 사용되는 또다른 SELECT 문을 의미한다. 최종 결과를 출력하는 쿼리를 메인 쿼리라고 한다면 , 이를 위한 중간 단계 혹은 보조 역할을 하는 SELECT 문을 서브쿼리라 한다. 조인 절에서 소개했던 SQL문 중 괄호 안에 들어있는 SELECT문을 서브 쿼리로 보면 되고 따라서 서브 쿼리는 여러개를 사용할수 있다.

 

서브쿼리는 다양한 형태로 사용된다 

SELECT , FROM , WHERE 절 보두에서 사용가능하다. ISNERT,UPDATE,DELETE 문에서도 사용할수 있다.

 

 

연관성 없는 서브쿼리

메인 쿼리와의 연관성이 없는 쿼리를 말한다. 조인조건이 걸리지 않는 서브쿼리를 말한다

SELECT COUNT(*)
    FROM EMPLOYEES
    WHERE SALARY >= ( SELECT AVG(SALARY) FROM EMPLOYEES);

 위 쿼리는 EMPLOYEES의 평균 급여보다 많이 받는 사원수를 조회 한것이다 . 메인 쿼리와 서브쿼리 모두 사원테이블을 조회하고 있지만 메인 쿼리와 서브 쿼리 사이의 연관성은 없다.

 

 

    SELECT COUNT(*)
    FROM EMPLPOYEES
    WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID 
                            FROM DEPARTMENTS
                            WHERE PARENT_ID IS NULL);

위쿼리는 부서 테이블에서 PARENT_ID NULL인 부서번호를 가진 사원의 총 건수를 반환하는 쿼리다 여기서 메인 테이블은 사원 테이블이며, 괄호안에 있는 서브쿼리는 사원 테이블과 아무런 연관성이 없다 .

유형 1 과 유형 2 쿼리의 차이점을 살펴보면 유형1은 서브 쿼리에서 단일 행을 반환하지만 유형2는 여러행을 반환했다는 점이다.

  SELECT EMPLOYEE_ID , EMP_NAME, JOB_ID
    FROM EMPLOYEES
    WHERE (EMPLOYEE_ID,JOB_ID) IN (SELECT EMPLOYEE_ID, JOB_ID
                                   FROM JOB_HISTORY);

위 커리는 JOB_HISTOREY 테이블에 있는 EMPLOYEE_ID, JOB_ID 두 값과 같은 건을 사원 테이블에서 찾는 서브쿼리로 서브 쿼리는 메인 쿼리와 연관성이 없다. 그리고 동시에 2개 이상의 컬럼 값이 같은 건을 찾고 있다. 물론 IN 앞에 있는 컬럼 개수와 서브 쿼리에서 반환하는 컬럼 개수 그리고 유형은 같아야한다.

 

 

 UPDATE EMPLOYEES
    SET SALARY = (SELECT AVG(SALARY)
                  FROM EMPLOYEES);

전 사원의 급여를 평균 금액으로 갱신

 

  DELETE EMPLOYEES
    WHERE SALARY >= (SELECT AVG(SALARY)
                    FROM EMPLOYEES);

평균 급여 보다 많이 받는 사원 삭제

 

연관성 있는 서브쿼리

 

메인 쿼리와 연관성이 있는 서브쿼리

   SELECT employee_id, emp_name, job_id
      FROM employees
     WHERE (employee_id, job_id ) IN ( SELECT employee_id, job_id
                                        FROM job_history);

 

 

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 );

위의 메인쿼리에서 사용된 부서번호 와 JOB_HISTORY의 부서번호가 같은 건을 조회 하는 쿼리다. 

EXISTS 연산자를 사용해서 서브쿼리 내에 조인 조건이 포함되어 있다. 따라서 결과는 JOB_HISTORY 테이블에 있는 부서만 조회한다.

 

 

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;

위 쿼리는 JOB_HISTORY 테이블을 조회하고 있는데 JOB_HISTORY에는 사번, 부서번호만 존재하므로 사원명과 부서명을 가져오려고 서브 쿼리를 SELECT 절에서 사용하였고 서브 쿼리 안의 WHERE 절에 조건을 추가했다. 이처럼 SELECT 절 자체에도 여러 개의 서브 쿼리를 넣을 수 있다. 또한 각 서브 쿼리가 독립적이므로 두 개의 서브 쿼리에서 사용된 사원 부서 테이블의 별칭을 모두 B로 사용해도 무방하다.

 

 SELECT a.department_id, a.department_name
      FROM departments a
     WHERE EXISTS ( SELECT 1
                      FROM employees b
                     WHERE a.department_id = b.department_id → ①
                       AND b.salary > ( SELECT AVG(salary) → ②
                                          FROM employees )
                   );

이 쿼리는 2개의 서브 쿼리가 사용되었다. 2에서 평균급여를 계산하고 값보다 큰 급여의 사원을 걸러낸 다음,

1에서 평균 급여 이상을 받는 사원이 속한 부서를 추출한것이다.

 

UPDATE EMPLOYEES A
    SET A.SALARY =(SELECT SAL 
                       FROM ( SELECT B.DEPARTMENT_ID, AVG(C.SALARY) AS SAL
    FROM DEPARTMENTS B,
         EMPLOYEES C
         WHERE B.PARENT_ID = 90
         AND B.DEPARTMENT_ID = C.DEPARTMENT_ID) D
         GROUP BY B.DEPARTMENT_ID)D
         WHERE A.DEPARTMENT_ID = D.DEPARTMENT_ID)
         
         WHERE A.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                                    FROM DEPARTMENTS
                                    WHERE PARENT_ID = 90);

상위부서가 기획부에 속하는 모든 사원의 급여를 자신의 부서별 평균 급여로 갱신하는 쿼리다.

SELECT DEPARTMENT_ID, MIN(SALARY),MAX(SALARY)
 FROM EMPLOYEES A
 WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
                         FROM DEPARTMENTS 
                         WHERE PARENT_ID = 90)
                         
  GROUP BY DEPARTMENT_ID;

위쿼리를 실행해보면 부서별 최소와 최대 금액이 모두 같고, 부서별 평균값으로 갱신되었음을 알수 있다.

UPDATE문에서 연관성 있는 서브쿼리를 사용하면 위 쿼리처럼 좀 복잡해지는 단점이 있다. 하지만 이를 MERGE문으로 변경하면 훨씬 적은 코드로 깔끔하게 작성할수 있다.

 

 MERGE INTO EMPLOYEES A
  USING (SELECT B.DEPARTMENT_ID ,AVG(SALARY) AS SAL
         FROM DEPARTMENTS B, EMPLOYEES C
         WHERE B.PARENT_ID = 90;
         AND B.DEPARTMENT_ID = C.DEPARTMENT_ID
         GROUP BU B.DEPARTMENT_ID ) D
         ON (A.DEPARTMENT_ID = D.DEPARTMENT_ID)
         WHEN MATCHED THEN
           UPDATE SET A.SALARY = D.SAL;