PLT 5.8 SUBQUERY
이번 장에서는 알려지지 않은 기준에 의한 데이터 검색을 위한 NESTED SUBQUERY와 데이터 조작 문장에 SUBQUERY를 사용하는 방법을 배우고 SUBQUERY에 의해 검색된 데이터 정렬에 대해 다루기로 한다.
SUBQUERY의 개념
다른 SELECT 문장의 절에 내장된 SELECT 문장 입니다. SUBQUERY는 여러 절에서 사용 가능하며 SELECT 문장 안에 기술된 SELECT 문장이다. NESTED SUBQUERY는 MAIN QUERY이전에 한번만 수행되며 SUBQUERY의 결과를 MAIN QUERY에 의해 조건으로 사용된다. SUBQUERY를 사용하여 간단한 문장을 강력한 문장으로 만들 수 있고 테이블 자체의 데이터에 의존하는 조건으로 테이블의 행을 검색할 필요가 있을 때 아주 유용하다.
|
-- MAIN QUERY
SELECT select_list
FROM table
WHERE expression operator
--SUBQUERY
(SELECT select_list
FROM table
WHERE condition); |
l SUBQUERY는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장이다.
l 알려지지 않은 조건에 근거한 값들을 검색하는 SELECT 문장을 작성하는데 유용하다.
l SUBQUERY는 MAIN QUERY 이전에 한 번 실행한다.
l SUBQUERY의 결과는 MAIN OUTER QUERY에 의해 사용된다.
l SUBQUERY는 괄호로 묶어야 한다.
l 두 종류의 비교 연산자들이 SUBQUERY에 사용된다.
l 단일 행 연산자
l =,>, >=, <, <=, <>, !=
l 복수 행 연산자
l IN, NOT IN, ANY, ALL, EXISTS
l SUBQUERY는 연산자의 오른쪽에 나타나야 한다.
l SUBQUERY는 많은 SQL 명령에서 사용 가능하다.
l SUBQUERY는 ORDER BY절을 포함할 수 없다.
SUBQUERY를 사용할 수 있는 절
l WHERE, HAVING, UPDATE
l INSERT 구문의 INTO
l UPDATE 구문의 SET
l SELECT나 DELETE의 FROM절
SUBQUERY의 유형
l 단일 행 SUBQUERY : SELECT문장으로부터 오직 하나의 행만을 검색하는 질의입니다
l 다중 행 SUBQUERY : SELECT문장으로부터 하나 이상의 행을 검색하는 질의입니다
l 다중 열 SUBQUERY : SELECT문장으로부터 하나 이상의 열을 검색하는 질의입니다
단일 행 SUBQUERY
단일 행 SUBQUERY는 내부 SELECT문장으로부터 하나의 행을 검색하는 질의입니다. 이런 유형의 SUBQUERY는 단일 행 연산자를 사용합니다. 이때 WHERE절에 기술된 열의 개수와 데이터 타입은 SELECT 절에 기술된 열과 좌측부터 1대1 대응되며 데이터 타입이 일치해야 한다.
예) EMP 테이블에서 사원번호가 7521의 업무와 같고 급여가 7934보다 많은 사원의 정보를 사원번호, 이름, 담당업무, 입사일자, 급여를 출력하여라.
|
SELECT empno,ename,job,hiredate,sal
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno = 7521)
AND sal > (SELECT sal
FROM emp
WHERE empno = 7934); |
SUBQUERY에서 그룹 함수 사용
단일 행을 RETURN하는 SUBQUERY에 그룹 함수를 사용하여 MAIN QUERY로부터 데이터를 출력할 수 있다.
예) EMP 테이블에서 급여의 평균보다 적은 사원의 정보를 사원번호, 이름, 담당업무, 급여, 부서번호를 출력하여라.
|
SELECT empno,ename,job,sal,deptno
FROM emp
WHERE sal < (SELECT AVG(sal)
FROM emp); |
SUBQUERY가진 HAVING절
SUBQUERY를 WHERE 절 뿐만 아니라 HAVING절에서도 사용 가능하다. 오라클 서버는 SUBQUERY를 실행하고 MAIN QUERY의 HAVING절에 RETURN한다.
예) EMP 테이블에서 20번 부서의 최소 급여보다 많은 모든 부서를 출력하여라.
|
SELECT deptno,MIN(sal)
FROM emp
GROUP BY deptno
HAVING MIN(sal) > (SELECT MIN(sal)
FROM emp
WHERE deptno = 20); |
다중 행 SUBQUERY
하나 이상의 행을 RETURN하는 SUBQUERY를 다중 행 SUBQUERY라고 부릅니다. 다중 행 SUBQUERY는 단일 행 연산자 대신에 다중 행 연산자를 사용합니다. 다중 행 연산자는 하나 이상의 값을 요구합니다.
|
SELECT empno,ename,job,hiredate,sal,deptno
FROM emp
WHERE sal IN (SELECT MIN(sal)
FROM emp
GROUP BY deptno); |
다중 행 SUBQUERY 연산자
l IN 연산자
l ANY 연산자
l ALL 연산자
l EXISTS 연산자
IN 연산자
2개 이상의 값을 RETURN하는 SUBQUERY에 대하여 비교 연산자(=,!=,<,<=,>,>=)를 기술하면 ERROR가 발생한다. 이런 경우 SUBQUERY에서 RETURN된 목록의 각각과 비교하여 QUERY를 수행하는 연산자가 IN이다.
ANY 연산자
2개 이상의 값을 RETURN하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=,!=,<,<=,>,>=)와 SUBQUERY사이에 ANY연산자를 기술하여 RETURN된 목록의 각각의 값과 비교한다.
|
ANY 연산자는 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환 합니다. |
예) EMP 테이블에서 30번 부서의 최소 급여를 받는 사원 보다 많은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를 출력하여라. 단 30번은 제외
|
SELECT empno,ename,job,hiredate,sal,deptno
FROM emp
WHERE deptno != 30 AND sal > ANY (SELECT sal
FROM emp
WHERE deptno = 30); |
ALL 연산자
2개 이상의 값을 RETURN하는 SUBQUERY에 대하여는 그런 값들을 어떻게 사용하는가를 지정해 두어야 한다. 비교 연산자(=,!=,<,<=,>,>=)와 SUBQUERY사이에 ALL연산자를 기술하여 RETURN된 목록의 모든 값과 비교한다.
|
ALL 연산자는 서브쿼리의 결과값 중 모든 결과 값이 만족 돼야만 결과값을 반환 합니다. |
예) EMP 테이블에서 30번 부서의 최고 급여를 받는 사원 보다 많은 급여를 받는 사원의 정보를 사원번호,이름,업무,입사일자,급여,부서번호를 출력하여라. 단30번은 제외
|
SELECT empno,ename,job,hiredate,sal,deptno
FROM emp
WHERE deptno != 30 AND sal > ALL (SELECT sal
FROM emp
WHERE deptno = 30); |
EXISTS 연산자
EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값들만을 결과로 반환해 줍니다. SUBQUERY에서 적어도 1개의 행을 RETURN하면 논리식은 참이고 그렇지 않으면 거짓 입니다.
예) EMP 테이블에서 적어도 한 명의 사원으로부터 보고를 받을 수 있는 사원의 정보를 사원번호,이름,업무,입사일자,급여를 출력하여라. 단 사원번호 순으로 정렬하여라.
|
SELECT empno,ename,job,hiredate,sal,deptno
FROM emp e
WHERE EXISTS (SELECT *
FROM emp
WHERE e.empno = mgr)
ORDER BY empno; |
다중 열 SUBQUERY
SUBQUERY의 구문을 작성할 때 WHERE 절에 비교되는 열이 하나가 아니라 여러 개의 열을 동시에 비교하는 경우가 있다. 이런 경우 다중 열 SUBQUERY라하여 Pairwise(쌍비교)되었다고 한다.
|
SELECT * | column1[,column2,...]
FROM table
WHERE (column1,column2,...) IN ( SELECT column1,column2,...
FROM table
WHERE condition); |
SUBQUERY로 작성된 구문을 실행하면 의외의 결과가 검색되는 경우가 있을 것이다. 이런 경우는 반드시 다중 열 SUBQUERY를 사용하여 조회하여야 올바른 자료를 검색할 수 있다. 아래의 예를 보고 정확한 개념을 이해하기 바란다.
예) EMP 테이블에서 급여와 보너스가 부서 30에 있는 어떤 사원의 보너스와 급여에 일치하는 사원의 이름,부서번호,급여,보너스를 출력하여라.
예제를 실행하기 전에 설명을 위하여 데이터를 변경한다.
|
UPDATE emp
SET sal = 1500, comm = 300
WHERE empno = 7934; |
|
SELECT ename,deptno,sal,comm
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = 30)
AND NVL(comm,-1) IN (SELECT NVL(comm,-1)
FROM emp
WHERE deptno = 30);
|
위 결과는 30번 부서에 급여가 1500이고 보너스가 300인 사원이 없는데도 “MILLER“는 출력되었다. 이는 조건을 각각 별도로 조회할 경우에 발생되는 문제점이다. 이를 해결하기 위해서는 Pairwise SUBQUERY를 이용하여야 한다.
Pairwise SUBQUERY
|
SELECT ename,deptno,sal,comm
FROM emp
WHERE (sal,NVL(comm,-1)) IN (SELECT sal,NVL(comm,-1)
FROM emp
WHERE deptno = 30);
|
SUBQUERY에서의 NULL값
NULL값을 비교하는 모든 조건은 NULL이다.
|
SELECT e.empno,e.ename,e.job,e.sal
FROM emp e
WHERE e.empno NOT IN (SELECT m.mgr
FROM emp m); |
위의 SELECT 문장의 결과 SUBQUERY에서 RETURN되는 값 중에는 NULL(KING은 MGR이 NULL이다)이 있다. NULL에 어떠한 연산을 하여도 모든 조건은 NULL이므로 전체 값이 존재하지 않는다고 RETURN한다. SUBQUERY의 결과 집합의 일부분으로서 NULL값은 IN연산자를 사용할 경우는 문제가 되지 않지만 NOT IN연산자를 사용하면 안 된다.
|
SUBQUERY의 결과 집합의 일부분으로서 NULL값은 IN(= ANY) 연산자를 사용할 수 있다. 그러나 NOT IN(!= ALL)연산자를 사용할 수 없다. |
FROM절에서의 SUBQUERY
SUBQUERY는 FROM절에서도 사용 가능하다. 하나의 테이블에서 자료의 양이 많을 경우 FROM절에 테이블 전체를 기술하여 사용하면 효율이 떨어질 수 있다. 이런 경우 필요한 행과 열만 선택하여 FROM절에 기술하면 오라클 서버가 최적화 단계에서 효율적인 검색을 할 수 있다. 이처럼 FROM절에 기술한 SUBQUERY는 마치 VIEW와 같은 역할을 한다. 이런 VIEW를 INLINE VIEW라 한다.
예) EMP과 DEPT 테이블에서 업무가 MANAGER인 사원의 정보를 이름,업무,부서명,근무지를 출력하여라
|
SELECT e.ename,e.job,d.dname,d.loc
FROM dept d,emp e
WHERE job = 'MANAGER' AND e.deptno = d.deptno;
[출처] PLT 5.8 SUBQUERY|작성자 후루꾸 |
'DataBase > Oracle' 카테고리의 다른 글
| Chapter 6 PL SQL(Procedural Language/SQL) - 2 (0) | 2008.04.17 |
|---|---|
| Chapter 6 PL SQL(Procedural Language/SQL) - 1 (0) | 2008.04.17 |
| PLT 5.7 Join (0) | 2008.04.17 |
| PLT 5.6 그룹 함수 (0) | 2008.04.17 |
| 변환 함수 (0) | 2008.04.17 |