'서브쿼리'에 해당되는 글 1건

서브쿼리(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처럼 작용 한다.

- 상관관계 서브 쿼리 : 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이다.

   

• 단일 행(Single-Row) 서브쿼리

- 오직 한개의 행(값)을 반환.

- 단일 행 연산자(=,>, >=, <, <=, <>, !=) 만 사용.

   

-- 위의 서브쿼리를 실행 시키면 아래의 그림처럼,
-- empno가 7369인 job을 먼저 검색하고, job이 'CLERK'인
-- 사원의 이름과 직업을 반환 한다.

   

   

• 

     

• 다중 행(Multiple-Row) 서브쿼리

- 하나 이상의 행을 반환하는 서브쿼리를 다중 행 서브쿼리라고 한다.

- 복수 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)를 사용 할 수 있다.

   

• IN 연산자의 사용 예제

   

   

   

   

   

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 연산자는 서브쿼리의 결과값 중 어느 하나의 값이라도 만족이 되면 결과값을 반환 한다.

 

 

   

   

   

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 연산자는 서브쿼리의 결과값 중 모든 결과 값이 만족 되야 만 결과값을 반환 한다.

 

 

   

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 연산자를 사용하면 서브쿼리의 데이터가 존재하는가의 여부를 먼저 따져 존재하는 값 들만을 결과로 반환해 준다.서브쿼리에서 적어도 한 개의 행을 반환하면 논리식은 참이고 그렇지 않으면 거짓 이다.

   

   

EMPNO ENAME SAL
---------- -------- --------
7566 JONES 2975
7698 BLAKE 2850
7782 CLARK 2450
...

   

• 다중 열(Multiple-Column) 서브쿼리
  • 다중 열 서브쿼리란 서브쿼리의 결과 값이 두 개 이상의 컬럼을 반환하는 서브쿼리 이다.

   

• Pairwise(쌍비교) Subquery
  • 서브쿼리가 한 번 실행되면서 모든 조건을 검색해서 주 쿼리로 넘겨 준다.

 

 

   

   

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

   

   

   

• Nonpairwise(비쌍비교) Subquery
  • 서브쿼리가 여러 조건별로 사용 되어서 결과 값을 주 쿼리로 넘겨 준다.

   

   

 

   

   

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

   

   

• Null Values in a Subquery
  • 서브쿼리에서 NULL 값이 반환 되면 주 쿼리 에서는 어떠한 행도 반환되지 않는다.

   

• FROM절상의 서브쿼리(INLINE VIEW)
  • INLINE VIEW란 FROM절 상에 오는 서브쿼리로 VIEW처럼 작용 한다.
    • View
      • 실제테이블을 참고로 가상적인 테이블을 제공, 복수의 테이블에서 데이터를 검색
        • 데이터 접근의 권한
        • 복잡한 쿼리의 간소화
        • 데이터의 독립성 확보
        • 동일 데이터를 서로 다른 뷰에게 제공

           

   

 

   

   

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

   

   

• 상관관계 서브쿼리
  • 상관관계 서브쿼리란 바깥쪽 쿼리의 컬럼 중의 하나가 안쪽 서브쿼리의 조건에 이용되는 처리 방식 이며, 이는 주 쿼리에서 서브쿼리를 참조하고 이 값을 다시 주 쿼리로 반환 한다는 것이다.

 

   

   

   

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

   

• 집합 쿼리(UNION, INTERSECT, MINUS)
  • 집합 연산자를 사용시 집합을 구성할 컬럼의 데이터 타입이 동일해야 한다.
    •  UNION : 합집합
    •  UNION ALL : 중복 데이터를 다 포함하는 합집합
    •  INTERSECT : 교집합
    •  MINUS : 차집합
• UNION
  • UNION은 두 테이블의 결합을 나타내며, 결합시키는 두 테이블의 중복되지 않은 값들을 반환 한다.

 

   

   

   

SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

   

DEPTNO
----------
10
20
30
40

• UNION ALL
  • UNION과 같으나 두 테이블의 중복되는 값 까지 반환 한다.

 

 

   

   

   

SELECT deptno FROM emp
UNION ALL
SELECT deptno FROM dept;

   

DEPTNO
---------
20
30
30
20
30
30
10
20
10

30

. ….

   

• INTERSECT
  • INTERSECT는 두 행의 집합중 공통된 행을 반환 한다.

 

 

   

   

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

DEPTNO
----------
10
20
30

 

• MINUS
  • MINUS는 첫 번째 SELECT문에 의해 반환되는 행 중에서 두 번째 SELECT문에 의해 반환되는 행에 존재하지 않는 행들을 반환 한다.

 

 

   

   

   

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

   

DEPTNO
----------
40

   

   

   

• 스칼라 서브쿼리
  • 스칼라 서브쿼리란 Select-List에서 서브쿼리가 사용될 때 이를 스칼라 서브 쿼리라고 함.
  • 특징
    • 하나의 레코드만 리턴이 가능하며, 두 개 이상의 레코드는 리턴할 수 없다.
    • 일치하는 데이터가 없더라도 NULL값을 리턴할 수 있다. 이는 원래 그룹함수의 특징 중에 하나인데 스칼라 서브쿼리 또한 이 특징을 가지고 있다.

         

no rows selected

   

   

   

MIN(DEPTNO)
-----------

1 row selected.

   

   

   

   

   

화면 캡처: 2011-12-28 오후 1:13

   

   

   

• 스칼라 서브쿼리와 Outer 조인 관계
  • 위와 같이 스칼라 서브쿼리는 일치하지 않더라도 NULL를 리턴하므로 Outer 조인과 같은 형태이며,
    이는 스칼라 서브쿼리와 Outer 조인은 서로 변형이 가능하다는 것을 알 수 있다.

       

   

   

   

화면 캡처: 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>