Oracle SQL Sub-query(기초)

서브쿼리(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) 서브쿼리

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

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

   

SQL> SELECT ename,job FROM emp WHERE job = (SELECT job FROM emp WHERE empno = 7369);

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

   

   

• 

     

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

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

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

   

• IN 연산자의 사용 예제

-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제 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
...

   

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

   

• 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

   

   

   

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

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

   

   

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

   

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

           

-- 급여가 부서번호 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

   

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

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

         

-- 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

   

   

   

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

       

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>