📚 서브쿼리 활용하기

서브쿼리(sub query)

---

• 메인 쿼리 내부에 정의된 쿼리
• 다른 select 문의 내부에 정의된 select문

💡 일반적으로는 서브쿼리보다 조인의 사용을 권장하지만, 경우에 따라 더 성능이 좋고 적절한 방법을 선택할 것. 물론, 조인이 아닌 서브쿼리로만 풀 수 있는 문제도 있다.
SQL Developer의 ‘계획설명’ 탭에서 각 쿼리 별 실행 순서 및 비용(cost)을 확인할 수 있다. 데이터베이스의 규모가 클 수록 성능 튜닝, 최적의 실행 방법을 사용하는 것이 중요하다.

📌 서브 쿼리의 기본 형식과 특징

• 서브쿼리 형식

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

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

    • where 절

    • having 절

    • from 절 (서브쿼리보다는 인라인 뷰라고 부른다.)

      💡 뷰 : 가상의 테이블

    • select문의 from절 내부의 select문으로 만들어진 뷰(가상의 테이블)를
      인라인 뷰라고 부르고, 이는 내부의 select문이 종료되면 사라진다.

    • DB에 뷰를 미리 저장해두면 계속 남아있어 사용할 수 있다.

• 서브쿼리의 특징
  • 서브쿼리는 한번만 실행되고, 메인쿼리보다 먼저 실행된다.
  • 서브쿼리의 실행결과는 메인쿼리의 조건식에서 사용된다.
  • 조건식에서, 비교값으로 사용되는 값이 쿼리의 실행결과로만 획득할 수 있을 때 사용
    • 쿼리의 실행결과를 비교값으로 사용한다.
• 서브쿼리 사용시 주의점
  • 서브쿼리는 반드시 괄호로 묶는다.
  • 서브쿼리의 실행결과가 단일행인지, 다중행인지에 따라 적절한 연산자를 사용해야 한다.
    • 다중행인데 어쩌다 행이 하나만 나오는 것인지, 단일행인지 구분할 수 있어야 한다.

📌 서브 쿼리의 종류

• 단일행 서브쿼리
  • 실행결과로 한 행만 반환된다. (실행결과가 1행, 1열이다.)
• 다중행 서브쿼리
  • 실행결과로 여러 행이 반환된다. (실행결과가 여러 행, 1열이다.)
  • 단일행 서브쿼리, 다중행 서브쿼리에서 사용하는 비교연산자 구분
    

단일행 서브쿼리	다중행 서브쿼리
=	in
<> 오라클에서는 !=	not in
>	>any, >all
<	<any <all

💡 IN 연산자는 단일행 서브쿼리에서도 = 대신 사용할 수 있다.
따라서 단일행, 다중행 서브쿼리에 상관없이 안전하게 쓸 수 있으므로
동등 비교에 대해서는 = 보다는 IN을 사용한다.

• 다중열 서브쿼리
  • 두 개 이상의 컬럼값이 조회조건으로 반환된다. (행의 수는 상관 없다.)
  • 다중열 서브쿼리 형식

    • 비교 연산자: IN, NOT IN (크기 비교 연산은 불가능하다.)

    • 두 컬럼은 데이터 타입이 동일하거나, 컬럼의 값이 묵시적 형변환이 가능해야 한다.

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

    • IN : 아래 두 경우를 모두 만족하는 행을 조회

      colum1=column3 and colum2=colum4
      

    • NOT IN : 아래 두 경우 중 하나라도 만족하지 않는 경우(하나라도 값이 다른 경우)를 조회

      column1≠column3 or column2≠column4

      • where절 좌항의 column1과 서브쿼리 내 column3의 값이 같은 경우
      • where절 좌항의 column2과 서브쿼리 내 column4의 값이 같은 경우

서브쿼리의 활용

---

📌 여러 개의 서브 쿼리를 사용해서 조회하기

복수 개의 조건에 대하여 개별적으로 서브쿼리를 사용할 수 있다.

-- 80번 부서에서 근무하고, 80번 부서의 평균급여보다 급여를 많이 받은 직원이고, 80번 부서의 관리자와 같은 해에 입사한 직원을 조회하기
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 80
AND SALARY > (SELECT AVG(SALARY)
              FROM EMPLOYEES
              WHERE DEPARTMENT_ID = 80)
AND TO_CHAR(HIRE_DATE, 'YYYY') = (SELECT TO_CHAR(E.HIRE_DATE, 'YYYY')
                                 FROM EMPLOYEES E, DEPARTMENTS D
                                 WHERE E.EMPLOYEE_ID = D.MANAGER_ID
                                 AND D.DEPARTMENT_ID = 80);

📌 HAVING절에서 서브쿼리를 사용하기

다중행 함수의 중첩은 SELECT절에서만 허용된다.

따라서 HAVING COUNT(*) = MAX(COUNT(*))라고 그대로 쓸 수 없고, 서브쿼리를 이용한다.

SELECT JOB_ID, COUNT(*)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(*) > 10
ORDER BY JOB_ID;

-- 직종 별로 사원수를 조회했을 때 사원수가 가장 많은 직종을 조회하기
SELECT JOB_ID, COUNT(*) CNT
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) 
                   FROM EMPLOYEES
                   GROUP BY JOB_ID);

📌 WITH절에서 서브쿼리를 사용하기

WITH절을 사용하면 SQL문에서 여러 번 사용되는 SQL문 혹은 실행결과를 가상의 테이블로 생성하고, 그 실행결과를 임시로 저장할 수 있다.

WITH 가상테이블별칭
AS (SELECT 컬럼명1, 컬럼명2, 표현식 별칭1, 표현식 별칭2
     FROM 테이블명
     WHERE 조건식)
 SELECT 컬럼명1, 컬럼명2, 별칭1, 별칭2
 FROM 가상테이블별칭

-- 직종 별로 사원수를 조회했을 때 사원수가 가장 많은 직종을 조회하기
-- 위의 SQL을 WITH절을 사용해서 변경하기
WITH JOB_EMPS   -- SELECT문의 결과를 가상의 테이블로 지정. 여러 가상 테이블을 만들 수 있다.
AS (SELECT JOB_ID, COUNT(*) CNT
    FROM EMPLOYEES
    GROUP BY JOB_ID)
SELECT JOB_ID, CNT
FROM JOB_EMPS
WHERE CNT = (SELECT MAX(CNT)
            FROM JOB_EMPS);

📌 의사컬럼(Pseudo Columns)에서 서브쿼리를 사용하기

• 의사컬럼은 실제 테이블에 저장되어있지 않은 컬럼이다.
• 의사컬럼은 SQL이 실행되는 동안 사용할 수 있는 컬럼이다.
  • ROWNUM: 의사컬럼 중 하나로, 조회 결과의 각 행에 순서대로 1번부터 시작되는 순번을 붙여주는 컬럼

-- 직종 별로 사원수를 조회했을 때 사원수가 가장 많은 직종을 조회하기
-- 의사칼럼 ROWNUM의 순번을 이용해서 조회하기. 순번이 1번인 행이 사원수가 가장 많은 직종의 행이다.
SELECT JOB_ID, CNT
FROM (SELECT JOB_ID, COUNT(*) CNT
     FROM EMPLOYEES
     GROUP BY JOB_ID
     ORDER BY CNT DESC) -- 직종 별 사원수를 내림차순으로 정렬한 가상의 테이블
WHERE ROWNUM = 1;       -- 조회결과의 순번 1인 행을 조회 (ROWNUM 컬럼이 있다는 것을 가정)