시소당

33. Nested Query(중첩질의) 오라클/자바[ERP]

§ Nested Query

1. 중첩질의(nested query)의 개요

중첩질의(nested query or embeded query)는 다음과 같이 query 속에 또 다른 query가 있는 구조이다.

<예제-1>의 내용를 다시 한번 보도록 하자.

<employee>테이블에서 갑을병의 직책이 과장이므로 <예제-1>의 query는

              “직책이 과장인 사원들의 이름과 직책을 모두 검색하라”

라고 하면 될 것 같은데, 왜 이리 복잡하게 중첩질의를 만들어서 표현하는 것일까. 이것은 우리가 query를 작성할 때에 ‘갑을병’의 직책을 아직 모를 수도 있고, 또는 ‘갑을병’의 직책이 지금 과장인 것은 알고 있지만, 직책의 변화가 예상되고 직책이 변화되었을 때에도 역시 이 query가 유효하려면 <예제-1>과 같이 작성하는 것이 보다 적절하기 때문이다. 보다 근본적으로 말하자면 우리가 query 를 작성할 때, 다른 query의 결과로 나온 값이나 애트리뷰트, 테이블 등을 참조해야 하는 경우가 있다. 이러한 경우 query 속에 query가 있는 중첩질의가 나타나게 되는 것이다. 외부질의에 포함되는 내부질의는 그 자체로 다시 내부질의를 가질 수도 있다..  중첩질의는 <예제-1>의 형태뿐만 아니라, 다른 형태도 추가적으로 가지고 있으며, 매우 다양한 query에 쓰여진다. 

2. 내부질의(subquery)의 형태

내부질의(subquery)는 다음과 같이 세 가지의 형태로 쓰인다.

2-1. scalar형

내부질의(subquery)의 scalar형은 질의의 결과로 단일값을 반환한다. 단일값이 쓰이는 곳이면 어디든지 쓰일 수 있으며, 대부분 SELECT절과 WHERE절에 쓰인다.

2-1-1. SELECT절에 쓰인 경우

<방법-2>

다음의 <QUERY-4>은 <QUERY-3>와 같은 결과값을 반환한다.

☞ 참고

위 그림은 <QUERY-1>, <QUERY-3>, <QUERY-4>에 대한 MS-SQL의 쿼리분석기의 실행계획이다. <QUERY-3>은 Left Semi Join, <QUERY-4>는 Inner Join이 쓰였으며, 쿼리비용은 동등하게 평가되고 있다. 결과가 같게 나오는 서로 다른 QUERY가 있다는 것은 QUERY수행자에게 선택을 요구한다. 어떤 QUERY를 선택할 것인가. to choose or not to choose, that’s a question.

☞ Advice

EXISTS는 비교구문이 아니기 때문에 IN, SOME(ANY), ALL과 달리 EXISTS 앞에 subquery-1의 결과값과 비교하는 값인 value-1이 없다는 것에 주목하라.

<department>테이블에는 1번(영업)부터 2번(기획), 3번(개발), 4번(총무)까지 총 4개의 부서가 존재한다. 그러나 <employee>테이블에는 4번(총무) 부서가 나타나지 않는다.

▶ step1

부서번호와 이름을 검색하므로 → SELECT dept_no, dept_name

▶ step2

dept_no와 dept_name이 <department>테이블에 있으므로

→ FROM department

▶ step3

dept_no 와 dept_name을 하나씩 추출할 때, 예를 들면 (1, 영업)을 추출할 때, (1, 영업)에 해당되는(소속되는) 사원이 존재하는 가를 판단해야 한다. 그러나 <department>테이블에는 사원의 정보가 없으며, 따라서 당연히 사원테이블인 <employee>테이블을 참조하여야 한다. 우선 <employee>테이블에서 사원정보를 추출한다.

SELECT * (← 편의상 *을 사용한 것이지만 emp_no 등 다른 것도 관계없다)

FROM employee

▶ step4

<QUERY-1>은 IN과 조인을 사용하여 각각 다음의 <QUERY-2>, <QUERY-3>과 같이 바꿀 수도 있다.