[5장] Sub Query (서브쿼리)

 

 

1. Sub Query란?

 

 - Sub Query : 쿼리 안에 또 다른 쿼리가 담겨 있는 것

 

[문법] SELECT  select_list

FROM  table 또는 view

WHERE  조건  연산자  ( SELECT  select_list

                                  FROM  table

                                  WHERE  조건 ) ;

 

 

 - 위 문법에서 괄호 안에 있는 쿼리를 Sub Query(또는 Inner Query)라고 부르고,

    나머지 괄호 밖에 있는 쿼리를 Main Query(또는 Outer Query)라고 부름

 

 

예제 1) Emp 테이블에서 'SCOTT' 보다 급여를 많이 받는 사람의 이름과 급여를 출력하세요.

 

 

 - Sub Query가 먼저 수행되어서 결과 값을 Main Query 로 전해 주고 그 값을 받아서 Main Query 가 수행됩니다

   ( 이 순서는 Sub Query의 종류에 따라 달라집니다)

 

※ Sub Query 를 작성할 때 몇 가지 주의 사항

  - Sub Query 부분은 Where 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 합니다.
  - 특별한 경우 (Top-n 분석 등)를 제외하고는 Sub Query 절에 Order by 절이 올 수 없습니다.
  - 단일 행 Sub Query 와 다중 행 Sub Query 에 따라 연산자를 잘 선택해야 합니다.

 

 

 

 

2. Sub Query의 종류

 

 1) 단일 행 Sub Query

 

 - 단일 행 Sub Query : Sub Query의 결과가 1 개의 행만 나오는 것

 - 즉, Sub Query 를 수행한 결과가 1건만 나오고 이 결과를 Main Query로 전달해서 Main Query를 수행

 

 

 예제 1) Student 테이블과 department 테이블을 사용하여 이윤나 학생과 1 전공(deptno1)이 동일한 학생들의 이름과

                  1전공 이름을 출력하세요.

 

 

 

 예제 2) Professor 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의

           이름과 급여, 입사일을 출력하세요.

 

 

 

 2) 다중 행 Sub Query

 

 - 다중 행 Sub Query : Sub Query의 결과가 2건 이상 출력되는 것

 - 즉, Sub Query 를 수행한 결과가 1건만 나오고 이 결과를 Main Query로 전달해서 Main Query를 수행

 

 

연 산 자	의    미
IN	같은 값을 찾음
>ANY	최소값을 반환함
<ANY	최대값을 반환함
<ALL	최소값을 반환함
>ALL	최대값을 반환함
EXISY	Sub Query의 값이 있을 경우 반환함

 

< 다중 행 Sub Query 연산자 >

 

 

예제 1) Emp2 테이블과 Dept2 테이블을 참조하여 근무지역(dept2 테이블의 area 컬럼)이 서울 지사인 모든 사원들의

          사번과 이름, 부서번호를 출력하세요.

 

 

 

 

예제 2) Emp2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람 이름과 직급,

           연봉을 출력하세요.

                단,  연봉 출력 형식은 아래와 같이 천 단위 구분 기호와 원 표시를 하세요.

 

 

 

예제 3) Student 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중에서 가장 적게 나가는 학생보다

                 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.

 

 

 

 

  3) 다중 컬럼 Sub Query

 

 - 다중 컬럼 Sub Query : Sub Query의 결과가 여러 컬럼인 경우

 

 

예제 1) Student 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요.

 

 

 

예제 2) Professor 테이블을 조회하여 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요.

           (학과이름순으로 오름차순 정렬하세요)

 

 

 

 

예제 3) Emp2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다

           적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

 

 

  4) 상호 연관 Sub Query

 

 - 상호 연관 Sub Query : Main Query 값을 Sub Query에 주고 Sub Query를 수행 한 후

                                    그 결과를 다시 Main Query로 반환해서 수행하는 Sub Query

 

 

 

예제 1) Emp2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의 이름과

          직급, 현재 연봉을 출력하세요.

 

 

※ Sub Query 는 오는 위치에 따라서 그 이름이 다릅니다.

- SELECT ( Sub Query ) <- 1 행만 반환할 경우 Scalar Sub Query(스칼라 서브쿼리)
- FROM ( Sub Query ) <- Inline View (인라인 뷰) – View 장에서 배웁니다
- WHERE ( Sub Query ) <- Sub Query 라고 부릅니다.

 

 

 

 

3. Scalar Sub Query (스칼라 서브쿼리)

 

- 스칼라 서브쿼리는 Select 절에 오는 서브쿼리로 한번에 결과를 1 행씩 반환함

 

 

 

1. Main Query 를 수행한 후 Scalar Sub Query 에 필요한 값을 제공합니다.
2. Scalar Sub Query 를 수행하기 위해 필요한 데이터가 들어있는 블록을 메모리로 로딩합니다.
3. Main Query 에서 주어진 조건을 가지고 필요한 값을 찾습니다.

   그 결과를 메모리에 입력값과 출력값으로 메모리 내의 Query execution cache라는 곳에 저장해 둠

   여기서 입력값은 Main Query에서 주어진 값이고 출력값은 Scalar Sub Query를 수행 후 나온 결과 값

4. 다음 조건이 Main Query에서 Scalar Sub Query로 들어오면 해쉬 함수를 이용해서 해당 값이 캐쉬에 존재하는지 찾고

   있으면 즉시 결과 값을 출력하고 없으면 다시 블록을 액세스해서 해당 값을 찾은 후 다시 메모리에 캐시

5. Main Query가 끝날 때까지 반복

 

 

- 스칼라 서브쿼리가 빠른 이유?

  : 찾는 데이터가 메모리에 만들어져 있는 값을 찾아오기 때문.

 

 

 - 실행 계획 : 쿼리를 수행하는 서버 프로세스가 쿼리를 수행하는 순서나 방법을 적어 놓은 것

                   옵티마이저가 실행계획을 생성해 주는데 이 실행 계획에 따라 쿼리의 수행 속도가 결정됨.

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.178-189