서브쿼리(Subquery)란?
- 서브쿼리는 다른 하나의 SQL 문장의 절에 NESTEDED된 SELECT 문장 이다.
- SELECT, UPDATE, DELETE, INSERT와 같은 DML문과 CREATE TABLE 또는 VIEW에서 이용 될 수 있다.
- 알려지지 않은 조건에 근거한 값 들을 검색하는 SELECT 문장을 작성하는데 유용 하다.
- 서브쿼리는 괄호로 묶어야 한다.
- 단일 행 연산자(=, >, >=, <, <=, <>, !=)와 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)들이 서브쿼리에 사용 된다.
- 서브쿼리는 연산자의 오른쪽에 위치해야 한다.
- 단일 행(Sing-Row) 서브쿼리 : SELECT 문장으로 부터 오직 하나의 행 만을 검색하는 질의이다.
- 다중 행(Multiple-Row) 서브쿼리 : SELECT문장으로부터 하나 이상의 행을 검색하는 질의이다.
- 다중 열(Multiple-Column) 서브쿼리 : SELECT문장으로부터 하나 이상의 컬럼을 검색하는 질의이다.
- FROM절상의 서브쿼리(INLINE VIEW) : FROM절상에 오는 서브쿼리로 VIEW처럼 작용 한다.
- 상관관계 서브 쿼리 : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이다.
- 오직 한개의 행(값)을 반환.
- 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용.
|
SQL> SELECT ename,job
FROM emp
WHERE job = (SELECT job
FROM emp
WHERE empno = 7369); |
|
-- 위의 서브쿼리를 실행 시키면 아래의 그림처럼,
-- empno가 7369인 job을 먼저 검색하고, job이 'CLERK'인
-- 사원의 이름과 직업을 반환 한다.
- 하나 이상의 행을 반환하는 서브쿼리를 다중 행 서브쿼리라고 한다.
- 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있다.
|
-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
SQL> SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
|
|
|
select max(sal)
from emp group by deptno
|
select empno, ename, sal, deptno
from emp
where sal in (2850,3000,5000)
|
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
EMPNO ENAME SAL DEPTNO
---------- -------- --------- ---------
7698 BLAKE 2850 30
7788 SCOTT 3000 20
7902 FORD 3000 20
7839 KING 5000 10
-
ANY 연산자의 사용 예제
- ANY 연산자는 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환 한다.
|
SQL> SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ANY (SELECT sal
FROM emp
WHERE job='SALESMAN'); |
|
|
SELECT sal
FROM emp
WHERE job='SALESMAN'
|
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ANY ( 1600,1250,1250,1500);
|
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ANY (SELECT sal
FROM emp
WHERE job='SALESMAN');
ENAME SAL
---------- ----------
ALLEN 1600
BLAKE 2850
CLARK 2450
...
-
ALL 연산자의 사용 예제
- ALL 연산자는 서브쿼리의 결과값 중 모든 결과 값이 만족 되야 만 결과값을 반환 한다.
|
SQL> SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL (SELECT sal
FROM emp
WHERE job='SALESMAN');
|
|
|
SELECT sal
FROM emp
WHERE job='SALESMAN'
|
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL ( 1600,1250,1250,1500);
|
SELECT ename, sal
FROM emp
WHERE deptno != 20
AND sal > ALL (SELECT sal
FROM emp
WHERE job='SALESMAN');
ENAME SAL
---------- ----------
BLAKE 2850
CLARK 2450
KING 5000
-
EXISTS 연산자의 사용 예제
- EXISTS 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값 들만을 결과로 반환해 준다.서브쿼리에서 적어도 한 개의 행을 반환하면 논리식은 참이고 그렇지 않으면 거짓 이다.
|
-- 사원을 관리할 수 있는 사원의 정보 조회 예제 (상관관계 서브쿼리)
SQL> SELECT empno, ename, sal
FROM emp e
WHERE EXISTS (SELECT empno
FROM emp
WHERE e.empno = mgr)
|
|
EMPNO ENAME SAL
---------- -------- --------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
...
-
Pairwise(쌍비교) Subquery
- 서브쿼리가 한 번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 준다.
|
SQL> SELECT empno, sal, deptno
FROM emp
WHERE (sal, deptno) IN ( SELECT sal, deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL );
|
|
|
SELECT sal, deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL
|
SELECT empno, sal, deptno
FROM emp
WHERE (sal, deptno) IN ( (1600, 30),(1250,30),(1250,30),(1500, 30));
|
SELECT empno, sal, deptno
FROM emp
WHERE (sal, deptno) IN ( SELECT sal, deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL );
EMPNO SAL DEPTNO
---------- ---------- ----------
7521 1250 30
7654 1250 30
7844 1500 30
7499 1600 30
|
SQL> SELECT empno, sal, deptno
FROM emp
WHERE sal IN ( SELECT sal
FROM emp
WHERE deptno = 30
AND comm is NOT NULL )
AND deptno IN ( SELECT deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL );
|
|
|
SELECT sal
FROM emp
WHERE deptno = 30
AND comm is NOT NULL
|
SELECT deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL
|
SELECT empno, sal, deptno
FROM emp
WHERE sal IN ( 1600,1250,1250, 1500 )
AND deptno IN ( 30, 30, 30, 30 );
|
SELECT empno, sal, deptno
FROM emp
WHERE sal IN ( SELECT sal
FROM emp
WHERE deptno = 30
AND comm is NOT NULL )
AND deptno IN ( SELECT deptno
FROM emp
WHERE deptno = 30
AND comm is NOT NULL );
EMPNO SAL DEPTNO
---------- ---------- ----------
7521 1250 30
7654 1250 30
7844 1500 30
7499 1600 30
|
-- 급여가 부서번호 20의 평균 급여보다 크고, 사원을 관리하는 사원으로서
-- 부서번호 20에 속하지 않은 사원의 정보를 조회하는 SQL 문장이다.
SQL> SELECT b.empno,b.ename,b.job,b.sal, b.deptno
FROM (SELECT empno
FROM emp
WHERE sal >(SELECT AVG(sal)
FROM emp
WHERE deptno = 20)) a, emp b
WHERE a.empno = b.empno
AND b.mgr is NOT NULL
AND b.deptno != 20
|
|
|
SELECT AVG(sal)
FROM emp
WHERE deptno = 20
|
SELECT empno
FROM emp
WHERE sal >2175
|
SELECT b.empno,b.ename,b.job,b.sal, b.deptno
FROM (SELECT empno
FROM emp
WHERE sal >2175) a, emp b
WHERE a.empno = b.empno
AND b.mgr is NOT NULL
AND b.deptno != 20
|
SELECT b.empno,b.ename,b.job,b.sal, b.deptno
FROM (SELECT empno
FROM emp
WHERE sal >(SELECT AVG(sal)
FROM emp
WHERE deptno = 20)) a, emp b
WHERE a.empno = b.empno
AND b.mgr is NOT NULL
AND b.deptno != 20
EMPNO ENAME JOB SAL DEPTNO
---------- ---------- --------- ---------- ----------
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
-
상관관계 서브쿼리
- 상관관계 서브쿼리란 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이며, 이는 주 쿼리에서 서브쿼리를 참조하고 이 값을 다시 주 쿼리로 반환 한다는 것이다.
|
-- 사원을 관리할 수 있는 사원의 평균급여보다 급여를 많이 받는 사원의 정보를 출력
SQL> SELECT empno, ename, sal
FROM emp e
WHERE sal > (SELECT AVG(sal) sal
FROM emp
WHERE e.empno = mgr)
|
|
|
바깥쪽 쿼리
SELECT empno, ename, sal
FROM emp e
e.empno |
안쪽 쿼리
e.empno = mgr
|
SELECT empno, ename, sal
FROM emp e
WHERE sal > (SELECT AVG(sal) sal
FROM emp
WHERE e.empno = mgr
EMPNO ENAME SAL
---------- ---------- ----------
7698 BLAKE 2850
7782 CLARK 2450
7788 SCOTT 3000
7839 KING 5000
7902 FORD 3000
|
SQL> SELECT deptno FROM emp
UNION
SELECT deptno FROM dept; |
|
|
SELECT deptno FROM emp
|
UNION |
SELECT deptno FROM dept
|
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;
DEPTNO
----------
10
20
30
40
-
UNION ALL
- UNION과 같으나 두 테이블의 중복되는 값 까지 반환 한다.
|
SQL> SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept; |
|
|
SELECT deptno FROM emp
|
UNION ALL |
SELECT deptno FROM dept
|
SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;
DEPTNO
---------
20
30
30
20
30
30
10
20
10
30
. ….
-
INTERSECT
- INTERSECT는 두 행의 집합중 공통된 행을 반환 한다.
|
SQL> SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept; |
|
|
SELECT deptno FROM emp
|
INTERSECT |
SELECT deptno FROM dept
|
SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;
DEPTNO
----------
10
20
30
-
MINUS
- MINUS는 첫 번째 SELECT문에 의해 반환되는 행 중에서 두 번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 반환 한다.
|
SQL> SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
|
|
|
SELECT deptno FROM dept
|
MINUS |
SELECT deptno FROM emp
|
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
DEPTNO
----------
40
-
스칼라 서브쿼리
- 스칼라 서브쿼리란 Select-List에서 서브쿼리가 사용될 때 이를 스칼라 서브 쿼리라고 함.
-
특징
|
-- 1. 그룹함수 사용 안할 때는 리턴하는 값이 없음
SELECT 1
FROM DEPT
WHERE 1 = 2
; |
|
no rows selected
|
-- 2. 그룹함수를 사용하면 값을 리턴함
SELECT MIN(DEPTNO)
FROM DEPT
WHERE 1 = 2
; |
|
MIN(DEPTNO)
-----------
1 row selected.
|
-- 3. 스칼라 서브쿼리 테스트(일치하는 값이 없을 경우 NULL 리턴함)
-- EMP:DEPT = M:1 관계에서 M 대상을 올리다보니 GROUP BY 을 추가함
SELECT D.DEPTNO,
(SELECT MIN(EMPNO)
FROM EMP
WHERE DEPTNO = D.DEPTNO) EMPNO
FROM DEPT D
ORDER BY D.DEPTNO
; |
|
화면 캡처: 2011-12-28 오후 1:13
|
SELECT D.DEPTNO,
E.EMPNO
FROM DEPT D,
(SELECT DEPTNO,
MIN(EMPNO) EMPNO
FROM EMP
GROUP BY DEPTNO) E
WHERE D.DEPTNO = E.DEPTNO(+)
ORDER BY D.DEPTNO |
;
|
화면 캡처: 2011-12-28 오후 1:13
<참고문헌>
Oracle Database 11g : Adminstration Workshop
Oracle Database 10g Documentation Library - councepts
원본 위치 <http://www.oracleclub.com>
원본 위치 <http://www.oracleclub.com/lecture/1501>
원본 위치 <http://cong4u.tistory.com/120>
원본 위치 <http://www.gurubee.net/pages/viewpage.action?pageId=4948016>
