학원 day33. 서브쿼리

서브쿼리

  - 메인 쿼리 내부에 정의된 쿼리

  - 다른 select문의 내부에 정의된 select문을 서브쿼리라고 한다.

  - 서브쿼리는 보통 조회 조건으로 사용되는 것이고, 인라인뷰는 가상의 테이블로 본다.

 

서브쿼리를 포함시킬 수 있는 곳

  - select절 (스칼라 서브쿼리)

  - where절

  - having절

  - from절(인라인뷰)

 

서브쿼리 형식

  select column, column
  from table
  where column 연산자 (select column
                       from table)

서브쿼리의 특징

  - 서브쿼리는 한번만 실행된다.

  - 서브쿼리는 메인쿼리보다 먼저 실행된다.

  - 서브쿼리의 실행결과는 메인쿼리의 조건식에서 사용된다.

  - 조건식에서 비교값으로 사용되는 값이 쿼리의 실행결과로만 획득할 수 있을 때, 그 비교값을 조회하는 쿼리가 서브쿼리다.

 

서브쿼리 사용시 주의점

  - 서브쿼리는 반드시 괄호로 묶어야 한다.

  - 조건식의 오른쪽에 서브쿼리를 위치시키면 가독성이 높아진다.

  - 서브쿼리의 실행결과가 단일행인지, 다중행인지에 따라 적절한 연산자를 사용해야 한다.

  -- 전체 사원의 평균급여보다 급여를 적게 받는 사원들의 사원아이디, 이름, 급여를 조회하기
  -- 조건식에서 사원의 급여와 비교되는 전체사원의 평균급여는 select문의 실행결과로 획득되는 값이다.
  -- 따라서, where절에 사원들의 전체 평급을 조회하는 select문(서브쿼리)이 필요하다.
  select employee_id, first_name, salary
  from employees
  where salary < (select avg(salary)
                  from employees)

 

서브쿼리의 종류

 * 단일행 서브쿼리

   - 서브쿼리의 실행결과로 한 행만 반환된다. (1행 1열)

   - 단일행 비교 연산자

      =, >, >=, <, <=, <>

 * 다중행 서브쿼리

   - 서브쿼리의 실행결과로 여러 행이 반환된다.

   - 다중행 비교 연산자

      in, any, all

단일행 서브쿼리	다중행 서브쿼리
=	in
<> (!=, 같지 않다)	not in
>	>any, >all
<	<any, <all

--------------------------------------------------------------------------------   
-- 단일행 서브쿼리
--------------------------------------------------------------------------------   
-- 'Neena'가 입사한 해에 입사한 직원 중에서 전체 직원의 평균 급여보다 급여를 적게 받는 직원의 아이디, 이름, 입사일, 급여를 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY
FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                    FROM EMPLOYEES
                                    WHERE FIRST_NAME = 'Neena')
AND SALARY < (SELECT AVG(SALARY)
              FROM EMPLOYEES);
              
--------------------------------------------------------------------------------        
-- 다중행 서브쿼리
--------------------------------------------------------------------------------  
-- 'Seattle'에서 근무중인 직원의 아이디, 이름을 조회하기 (서브쿼리방식)
-- 1. 'Seattle'의 소재지 아이디를 조회한다.
-- 2. 해당 소재지 아이디에 위치한 부서의 아이디를 조회한다.
-- 3. 해당 부서에 근무중인 직원을 조회한다.
SELECT EMPLOYEE_ID, FIRST_NAME
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID -- 다중행 서브쿼리
                        FROM DEPARTMENTS
                        WHERE LOCATION_ID IN (SELECT LOCATION_ID  -- 단일행 서브쿼리이지만 IN에 한해서는 단일행 서브쿼리에도 사용가능, 따라서 안전하게 IN을 쓰는게 좋음
                                             FROM LOCATIONS
                                             WHERE CITY = 'Seattle'));
                                             
-- 'Seattle'에서 근무중인 직원의 아이디, 이름을 조회하기 (조인방식)
-- 서브쿼리로 조회가능했던 내용을 JOIN으로 구현해보기
SELECT E.EMPLOYEE_ID, E.FIRST_NAME
FROM EMPLOYEES E, DEPARTMENTS D, LOCATIONS L
WHERE L.CITY = 'Seattle'
AND L.LOCATION_ID = D.LOCATION_ID
AND D.DEPARTMENT_ID = E.DEPARTMENT_ID;

-- 80번 부서에 소속된 직원의 급여보다 급여를 많이 받는 직원의 아이디, 이름, 급여를 조회하기
-- > all
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY     
FROM EMPLOYEES                       
WHERE SALARY > ALL (SELECT SALARY      -- 다중행 서브쿼리 > ALL은 서브쿼리의 조회결과값 전부에 대해서 큰 값일 때 TRUE로 판정한다.
                    FROM EMPLOYEES     -- WHERE SALARY > ALL (SELECT SALARY ...)는  WHERE SALARY > (SELECT MAX(SALARY) ...)와 동일하다.
                    WHERE DEPARTMENT_ID = 80)
AND DEPARTMENT_ID != 80;    -- 여기서는 이 문장이 굳이 필요없을듯, 어차피 ALL이 전부에 대해서 조회하는 거니까
                   
-- > any
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY      -- 다중행 서브쿼리 > ANY은 서브쿼리의 조회결과값 중에서 어느 한 값보다 큰 값일 때 TRUE로 판정한다.
                    FROM EMPLOYEES     -- WHERE SALARY > ANY (SELECT SALARY ...)는  WHERE SALARY > (SELECT MIN(SALARY) ...)와 동일하다.
                    WHERE DEPARTMENT_ID = 80)
AND DEPARTMENT_ID <> 80;    -- 여기서는 이게 필요함, ANY라서 80부서에서 가장 적게 받는 사원만 빼고 다 나옴

 * 다중열 서브쿼리

  - 두 개 이상의 컬럼값이 조회 조건으로 반환되는 서브쿼리다.

  - 다중열 서브쿼리 형식

  select column, column, ....
  from table1 
  where (column1, column2) in (select column1, column2 
                               from table2)

--------------------------------------------------------------------------------        
-- 다중 열 서브쿼리
--------------------------------------------------------------------------------

-- 다중 열 서브쿼리를 사용하지 않고, 145번 직원과 같은 부서에 일하고, 같은 해에 입사한 직원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID = (SELECT DEPARTMENT_ID
                       FROM DEPARTMENTS
                       WHERE DEP_ID = 145)
AND TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(HIRE_DATE, 'YYYY')
                                  FROM EMPLOYEES
                                  WHERE EMPLOYEE_ID = 145);

-- 다중 열 서브쿼리를 사용해서 145번 직원과 같은 부서에 일하고, 같은 해에 입사한 직원의 아이디, 이름, 입사일을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE
FROM EMPLOYEES
WHERE (DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')) IN (SELECT DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY')
                                                      FROM EMPLOYEES
                                                      WHERE EMPLOYEE_ID = 145);

 

Having절에서 서브쿼리 사용하기

  - group by 절을 사용해서 그룹화하고 그룹함수를 실행한 결과를 필터링하는 having 절에도 서브쿼리를 사용할 수 있다.

--------------------------------------------------------------------------------        
-- HAVING 절에서 서브쿼리
--------------------------------------------------------------------------------
-- 부서별 사원수를 조회했을 때 사원수가 가장 많은 부서의 아이디와 사원수를 조회하기
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
                   FROM EMPLOYEES
                   WHERE DEPARTMENT_ID IS NOT NULL
                   GROUP BY DEPARTMENT_ID);
                   
-- 부서별 사원수를 조회했을 때 사원수가 가장 많은 부서의 아이디와 사원수를 조회하기 
-- with ~ as는 쿼리에서 여러번 사용되는 조회 결과를 미리 조회해서 메모리에 저장시키고, 테이블처럼 사용할 수 있도록 한다.
-- 위에 코딩보다 쿼리성능측에서 더 좋음. 그룹핑하고 count(*)를 한번만 하고 그것을 갖다가 쓰는 것이기 때문에
WITH EMPLOYEE_COUNT
AS (
    SELECT DEPARTMENT_ID, COUNT(*) CNT
    FROM EMPLOYEES
    WHERE DEPARTMENT_ID IS NOT NULL
    GROUP BY DEPARTMENT_ID
)
SELECT DEPARTMENT_ID, CNT
FROM EMPLOYEE_COUNT
WHERE CNT = (SELECT MAX(CNT)
             FROM EMPLOYEE_COUNT);            

-- 에러                  
SELECT DEPARTMENT_ID, COUNT(*)
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
HAVING MAX(COUNT(*));     -- 조건식의 형태가 되어야 한다. 비교대상이 있어야 한다.

상호연관 서브쿼리

  - 서브쿼리가 메인쿼리문에서 사용(참조)되는 컬럼을 참조할 때 상호연관 서브쿼리가 된다.

  - 상호연관 서브쿼리는 메인쿼리문에서 처리되는 각 행에 대해서 한번씩만 실행된다.

  - 일반적인 서브쿼리는 메인쿼리보다 먼저 실행되고, 단 한번만 실행되지만, 상호연관 서브쿼리는 그렇지 않다.

 

스칼라 서브쿼리

  - SELECT절에서 사용되는 서브쿼리를 특별히 스칼라 서브쿼리라고 한다.
  - 스칼라 서브쿼리의 실행결과는 반드시 단일행, 단일열이어야 한다. 

(SELECT절에 적는 것은 무조건 1행 1열이어야 함. 값 하나만 반환해야 함.)

--------------------------------------------------------------------------------        
-- 상호연관 서브쿼리
--------------------------------------------------------------------------------

-- 각 부서에서 최저급여를 받는 직원의 부서아이디, 사원아이디, 이름, 급여를 조회하기
SELECT A.DEPARTMENT_ID, A.EMPLOYEE_ID, A.FIRST_NAME, A.SALARY
FROM EMPLOYEES A
WHERE A.SALARY = (SELECT MIN(B.SALARY)
                  FROM EMPLOYEES B
                  WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID)
ORDER BY A.DEPARTMENT_ID;   

-- 모든 부서의 부서아이디, 이름, 소속사원수를 조회하기(GROUP BY, 인라인뷰, 포괄조인을 사용하였음)
SELECT Y.DEPARTMENT_ID, Y.DEPARTMENT_NAME, NVL(X.CNT, 0) CNT 
FROM (SELECT DEPARTMENT_ID, COUNT(*) CNT
      FROM EMPLOYEES
      WHERE DEPARTMENT_ID IS NOT NULL
      GROUP BY DEPARTMENT_ID) X, DEPARTMENTS Y
WHERE X.DEPARTMENT_ID(+) = Y.DEPARTMENT_ID
ORDER BY Y.DEPARTMENT_ID;

-- 모든 부서의 부서아이디, 이름, 소속 직원수를 조회하기(상호연관 서브쿼리이면서 스칼라 서브쿼리임)
-- * SELECT절에서 사용되는 서브쿼리를 특별히 스칼라 서브쿼리라고 한다.
-- * 스칼라 서브쿼리의 실행결과는 반드시 단일행, 단일열이어야 한다. 
SELECT A.DEPARTMENT_ID, A.DEPARTMENT_NAME, (SELECT COUNT(*)
                                            FROM EMPLOYEES B
                                            WHERE B.DEPARTMENT_ID = A.DEPARTMENT_ID) CNT
FROM DEPARTMENTS A
ORDER BY A.DEPARTMENT_ID;

* 포괄조인은 일치하는 값이 없어서 조인이 안되는 것이니까 NULL에다가 넣는 것이다. Y의 값이 다 나오게 하려고 X에 NULL을 추가하는 것이다. 즉, 반드시 Y가 NULL값이 아니더라도 X에 일치하는 값이 없으면 NULL을 넣는 것임!