💻Programming/Oracle

[Oracle/오라클] 서브쿼리

horang_dev 2021. 3. 20. 13:55

서브쿼리

  - SQL 구문 안에 또 다른 SQL 구문이 존재하는 경우

  - 반드시 ( ) 안에 기술해야 함

  - JOIN 사용을 대신하거나 구문의 복잡성을 줄일 수 있다.

  - WHERE 절에서 사용될 경우 연산자 오른쪽에 사용

  - 서브쿼리가 사용되는 곳 : SELECT절 , WHERE절 => 일반적 서브쿼리, 

                                      FROM절 => 인라인 (IN-LINE 서브쿼리, 독립적으로 실행 가능해야 함)

  - 종류 : 연관 서브쿼리, 비연관 서브쿼리, 단일행 | 다중행/단일열 | 다중열 서브쿼리 (반환하는 행과 열의 수가 기준)

 

1. 비연관 서브쿼리 

  - 메인쿼리 (밖의 쿼리)와 서브쿼리에 사용된 테이블이 JOIN으로 연결되지 않는 경우

 

ex) 부서테이블에서 상위부서번호 (parent_id)가 NULL인 부서에 속한 사원정보를 조회하시오.

      Alias는 사원번호, 사원명, 소속부서번호 , 부서명

-- 메인쿼리 : 부서테이블과 사원테이블에서 사원정보를 조회
    SELECT A.employee_id AS 사원번호,
           A.emp_name AS 사원명,
           A.department_id AS 소속부서번호,
           B.department_name AS 부서명
      FROM employees A, departments B
     WHERE A.department_id = (서브쿼리)
     
-- 서브쿼리 : 상위부서번호가(parent_id)가 NULL인 부서번호
    SELECT department_id
      FROM departments
     WHERE parent_id IS NULL;
     
-- 결합
    SELECT A.employee_id AS 사원번호,
           A.emp_name AS 사원명,
           A.department_id AS 소속부서번호,
           B.department_name AS 부서명
      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 IS NULL);

2. 연관성 있는 서브쿼리 

  - 메인쿼리에 사용되는 테이블과 서브쿼리에 사용된 테이블이 JOIN으로 연결된 서브쿼리

 

ex) 직무이력테이블 (job_history) 에 존재하는 부서를 조회하시오.

      Alias는 부서코드 , 부서명이다.

-- 메인쿼리 : 부서테이블에서 부서코드, 부서명 조회
    SELECT A.department_id AS 부서코드,
           A.department_name AS 부서명
      FROM departments A
     WHERE A.department_id = (서브쿼리)
     
-- 서브쿼리 : 직무이력테이블(job_history)에서 부서 조회
    SELECT department_id
      FROM job_history;
     
-- 결합 :EXISTS연산자 사용(다중행 처리 가능, 존재 여부 판단)
    SELECT A.department_id AS 부서코드,
           A.department_name AS 부서명
      FROM departments A
     WHERE EXISTS(SELECT 1  -- 존재 여부만 판단하기 때문에 컬럼 의미 없음
                    FROM job_history B
                   WHERE A.department_id = B.department_id);

3. 단일행 서브쿼리 

  - 서브쿼리의 결과가 1개의 행으로 구성된 경우 

  - 관계연산자가 사용된 경우 (조건)

 

ex) 회원테이블에서 회원의 평균 마일리지보다 많은 마일리지를 보유한 회원정보를 조회하시오.

     Alias는 회원번호, 회원명, 마일리지, 평균마일리지이다.

    SELECT mem_id AS 회원번호,
           mem_name AS 회원명,
           mem_mileage AS 마일리지,
           (SELECT ROUND(AVG(mem_mileage))
              FROM member) AS 평균마일리지
      FROM member
     WHERE mem_mileage >= (SELECT ROUND(AVG(mem_mileage)) --비교되는 데이터의 갯수가 동일해야 함
                             FROM member)
     GROUP BY mem_id,mem_name, mem_mileage
     ORDER BY 1;

 

4. 다중행 서브쿼리
 - 복수개의 결과를 반환하는 서브쿼리
 - IN, ANY, EXISTS 등의 연산자와 결합

 

ex) 사원테이블에서 2007년 이후에 입사한 사원 중 2004년도에 입사한 사원들의 최저 급여보다 더 많은 급여를 받는

     사원을 조회하시오.

     Alias는 사원번호, 사원명, 입사일, 급여, 직책명이다.

-- ANY 연산자 사용
    SELECT A.employee_id AS 사원번호, 
           A.emp_name AS 사원명, 
           A.hire_date AS 입사일, 
           A.salary AS 급여, 
           B.job_title AS 직책명
      FROM employees A , jobs B
     WHERE A.job_id = B.job_id
       AND A.hire_date >= TO_DATE('20070101')
       AND A.salary >=ANY (SELECT salary 
                             FROM employees
                            WHERE EXTRACT(YEAR FROM hire_date) = 2004);
                            
-- EXIST 연산자 사용
    SELECT A.employee_id AS 사원번호, 
           A.emp_name AS 사원명, 
           A.hire_date AS 입사일, 
           A.salary AS 급여, 
           B.job_title AS 직책명
      FROM employees A , jobs B
     WHERE A.job_id = B.job_id
       AND A.hire_date >= TO_DATE('20070101')
       AND EXISTS (SELECT 1 
                     FROM employees B
                    WHERE EXTRACT(YEAR FROM B.hire_date) = 2004
                      AND A.salary >= B.salary);

 

반응형