No.007 서브쿼리(SUBQUERY) [2017-06-07]

1. SUBQUERY

 알려지지 않은 기준에 의한 데이터 검색을 위한 NESTED SUBQUERY 와 데이터 조작 문장에 SUBQUERY 를 사용하는 방법을 배우고 SUBQUERY에 의해 검색된 데이터 정렬에 대해 다루기로 한다.

 

1.1 SUBQUERY의 개념

 다른 SELECT 문장의 절에 내장된 SELECT 문장 입니다. SUBQUERY는 여러 절에서 사용 가능하며 SELECT 문장 안에 기술된 SELECT 문장이다. NESTED SUBQUERY는 MAIN QUERY 이전에 한번만 수행되며 SUBQUERY의 결과를 MAIN QUERY에 의해 조건으로 사용된다. SUBQUERY를 사용하여 간단한 문장을 강력한 문장으로 만들 수 있고 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 아주 유용하다.

 

1.2 Syntax

 1) SUBQUERY는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장이다.

 2) 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는데 유용하다.

 3) SUBQUERY는 MAIN QUERY 이전에 한 번 실행한다.

 4) SUBQUERY의 결과는 MAIN OUTER QUERY에 의해 사용된다.

 

Guidelines

 1) SUBQUERY는 괄호로 묶어야 한다.

 2) 두 종류의 비교 연산자들이 SUBQUERY에 사용된다.

 ① 단일 행 연산자 =, >, >= <, <=, <>, !=

 ② 복수 행 연산자 IN, NOT IN, ANY, ALL, EXISTS

 3) SUBQUERY는 연산자의 오른쪽에 나타나야 한다.

 4) SUBQUERY는 많은 SQL 명령에서 사용 가능하다.

 5) SUBQUERY는 ORDER BY절을 포함할 수 없다.

 

1.3 SUBQUERY를 사용할 수 있는 절

 1) WHERE, HAVING, UPDATE

 2) INSERT 구문의 INT

 3) UPDATE 구문의 SET

 4) SELECT나 DELETE의 FROM 절

 

1.4 SUBQUERY의 유형

 1) 단일 행 SUBQUERY : SELECT 문장으로부터 오직 하나의 행만을 검색하는 질의입니다.

 2) 다중 행 SUBQUERY : SELECT 문장으로부터 하나 이상의 행을 검색하는 질의 입니다.

 3) 다중 열 SUBQUERY : SELECT 문장으로부터 하나 이상의 열을 검색하는 질의 입니다.

 

1.5 단일 행 SUBQUERY

 단일 행 SUBQUERY는 내부 SELECT 문장으로부터 하나의 행을 검색하는 질의입니다. 이런 유형의 SUBQUERY는 단일 행 연산자를 사용합니다. 이때 WHERE절에 기술된 열의 개수와 데이터 타입은 SELECT 절에 기술된 열과 좌측부터 1 대 1 대응되며 데이터 타입이 일치해야한다.

 

문제 ) EMP 테이블에서 SCOTT의 급여보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 급여를 출력하여라.

 

문제 1) EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934 보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.

 

1.5.1) SUBQUERY에서 그룹 함수 사용

 담일 행을 RETURN하는 SUBQUERY에 그룹 함수를 사용하여 MAIN QUERY로부터 데이터를 출력할 수 있다.

 

문제 2) EMP 테이블에서 급여의 평균보다 적은 사원의 정보를 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하여라.

 

1.5.2) SUBQUERY 가진 HAVING절

 SUBQUERY를 WHERE 절 뿐만 아니라 HAVING절에서도 사용 가능하다. 오라클 서버는 SUBQUERY를 실행하고 MAIN QUERY의 HAVING절에 RETURN한다.

 

문제3) EMP 테이블에서 20번 부서의 최소 급여보다 많은 모든 부서를 출력하여라.

 

문제 4) EMP 테이블에서 업무별로 가장 적은 급여를 출력하여라.

 

1.6 다중 행 SUBQUERY

 하나 이상의 행을 RETURN 하는 SUBQUERY를 다중 행 SUBQUERY 라고 부릅니다. 다중 행 SUBQUERY는 단일 행 연산자 대신에 다중 행 연산자를 사용합니다. 다중 행 연산자는 하나 이상의 값을 요구합니다.

 

 

 위 SELECT 문의 문제점은 SUBQUERY에서 RETURN 되는 ROW가 1개 이상이다. 이런 경우는 다중 행 SUBQUERY 연산자를 이용하여야 한다

 

 

1.6.1) 다중 행 SUBQUERY 연산자

 1) IN 연산자

 2) ANY 연산자

 3) ALL 연산자

 4) EXISTS 연산자

 

1.6.2) IN 연산자

 2개 이상의 값을 RETURN 하는 SUBQUERY에 대하여 비교 연산자(=, !=, <, <=, >, >-)를 기술하면 ERROR가 발생한다. 이런 경우 SUBQUERY 에서 RETURN 된 목록의 각각과 비교하여 QUERY 를 수행하는 연산자가 IN이다.

 

문제 5) EMP 테이블에서 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라

 

1.6.3) ANY 연산자

 2개 이상의 값을 RETURN하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=, !=, <, <=, >, >-)와 SUBQUERY 사이에 ANY 연산자를 기술하여 RETURN된 목록의 각각의 값과 비교한다.

 

문제 6) EMP 테이블에서 30번 부서의 최소 급여를 받는 사원보다 많은 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여, 부서번호를 출력하여라. 단 30번은 제외

 

1.6.4) ALL 연산자

 2개 이상의 값을 RETURN 하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=, !=, <, <=, >, >-)와 SUBQUERY 사이에 ALL 연산자를 기술하여 RETURN 된 목록의 모든 값과 비교한다.

 

문제 7) EMP 테이블에서 30번 부서의 최고 급여를 받는 사원 보다 많은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를 출력하여라. 단 30번은 제외

 

1.6.5) EXISTS 연산자

 SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓이다.

 

문제 8) EMP 테이블에서 적어도 한명의 사원으로부터 보고를받을 수 있는 사원의 정보를 사원번호, 이름, 업무, 입사일자, 급여를 출력하여라, 단 사원번호 순으로 정렬하여라.

 

1.7 다중 열 SUBQUERY

 SUBQUERY의 구문을 작성할 때 WHERE 절에 비교되는 열이 하나가 아니라 여러 개의 열을 동시에 비교하는 경우가 있다. 이런 경우 다중 열 SUBQUERY 라하여 Pairewise 되었다고 한다.

 

1.7.1) Syntax

 

 SUBQUERY로 작성된 구문을 실행하면 의외의 결과가 검색되는 경우가 있을 것이다. 이런 경우는 반드시 다중 열 SUBQUERY를 사용하여 조회하여야 올바른 자료를 검색할 수 있다. 아래의 예를 보고 정확한 개념을 이해하기 바란다.

 

문제 9) EMP 테이블에서 급여와 보너스가 부서 30에 있는 어떤 사원의 보너스와 급여에 일치하는 사원의 이름, 부서번호, 급여, 보너스를 출력하여라.

 

중요

 위 결과는 30번 부서에 급여가 1500이고 보너스가 300인 사원이 없는데도 출력되었다. 이는 조건을 각각 별도로 조회할 경우에 발생되는 문제점이다. 이를 해결하기 위해서는 Pairwise SUBQUERY를 이용하여야한다.

 

문제 10) 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서번호를 출력하여라. 단 업무별로 정렬하여라.

 

중요

 위 결과를 사번에 7900인 사원은 업무가 CLERK 이다. CLERK 의 업무의 최소 급여는 800에도 불구하고 출력되었다. 이는 업무별 최소 급여만 RETURN 되지 어느 업무가 어떤 최소값을 가지는지를 알 수 없다.

 

1.7.2) Pairwise SUBQUERY

 앞의 결과를 보면 알 수 있듯이 어떤 업무의 급여가 최소인지 업무와 최소 급여를 동시에 비교하여야 한다.

 

문제 11) EMP 테이블에서 급여와 보너스가 부서 30에 있는 어떤 사원의 보너스와 급여에 일치하는 사원의 이름, 부서번호, 급여, 보너스를 출력하여라.

 

문제 12) 업무별로 최소 급여를 받는 사원의 정보를 사원번호, 이름, 업무, 부서번호를 출력하여라. 단 업무별로 정렬하여라.

 

1.8 SUBQUERY 에서의 NULL 값

 NULL 값을 비교하는 모든 조건은 NULL이다.

 

 

 위 문장의 SELECT 문장은 1명 이상으로부터 보고를 받을 수 있는 사원의 정보를 출력한 것이다. 그러면 말단 직원을 출력할 경우에는 SELECT 문장을 어떻게 기술해야 할까, 일반 사용자가 실수하기 쉬운 부분이다. 우선 IN 대신 NOT IN을 사용하면 쉽게 해결될 것이라 생각한다. 그러나 여기에는 다음과 같은 문제점이 있다. 우선 다음의 SELECT 문장을 보기 바란다.

 

 

 위의 SELECT 문장의 결과 SUBQUERY 에서 RETURN 되는 값 중에는 NULL(KING은 MGR이 NULL이다)이 있다. NULL 에 어떠한 연산을 하여도 모든 조건은 NULL 이므로 전체 값이 존재하지 않는다고 RETURN 한다. SUBQUERY의 결과 집합의 일부분으로서 NULL값은 IN 연산자를 사용할 경우는 문제가 되지 않지만 NOT IN 연산자를 사용하면 안된다.

 

Guidelines

 SUBQUERY 의 결과 집합의 일부분으로서 NULL 값은 IN(= ANY) 연산자를 사용할 수 있다. 그러나 NOT IN(!=ALL)연산자를 사용할 수 없다.

 

1.9 FROM 절에서의 SUBQUERY

 SUBQUERY는 FROM 절에서도 사용 가능하다. 하나의 테이블에서 자료의 양이 많은 경우 FROM 절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있다. 이런 경우 필요한 행과열만 선택하여 FROM 절에 기술하면 오라클 서버가 최적화 단계에서 효율적인 검색을 할 수 있다. 이처럼 FROM 절에 기술한 SUBQUERY는 마치 VIEW와 같은 역할을 한다. 이런 VIEW를 INLINE VIEW라 한다.

 

문제 13) EMP과 DEPT 테이블에서 업무가 MANAGER 인 사원의 정보를 이름, 업무, 부서명, 근무지를 출력하여라