오라클 서브쿼리, 집합연산

오라클 서브쿼리, 집합연산  

서브쿼리(SubQuery)

- 쿼리문 내에 또 다른 쿼리문이 있는 형태 

- 서브쿼리는 메인쿼리에 포함되는 관계 

- ()를 사용해 감싸는 형태 

- Order by 를 사용하지 못한다 

- 사용가능한 위치 ( SELECT/ FROM /WHERE/HAVING/ORDER BY / VALUES(INSERT)/SET(UPDATE)....)

서브쿼리의 종류 

1) 단일행(Single Row) 서브쿼리

- 결과가 레코드 하나인 서브쿼리

- 일반 연산자(=,>,<) 사용 

예제) 부서명이 'ACCOUNTING'인 부서의 직원수를 표시하시오 

select count(*)

from   emp 

where  deptno = (select deptno 

                 from   dept

         where  dname = 'ACCOUNTING');

예제) 부서명이 'RESEARCH'인 부서의 sal 평균보다 많은 sal을 받는 직원들의 이름과 연봉을 표시하시오 

select  ename, sal

from    emp 

where   sal > (select avg(sal) 

               from   emp 

               where  deptno = (SELECT deptno 

                                from   dept 

                                where  dname = 'RESEARCH'));

2) 다중행(Multi ROw) 서브쿼리

- 결과가 레코드 여러개인 서브쿼리

- 다중행 연산자 (IN, ALL,ANY,EXISTS) 사용

- ALL  : 여러개의 레코드의 AND 효과(가장 큰 값보다 큰) 

- ANY : 여러개의 레코드의 OR효과(가장 작은값보다 큰) 

- IN : 결과값 중에 있는 것 중에서의 의미, 일종의 서브셋 

예제) 부서번호가 20인 부서의 모든(ALL)직원보다 연봉이 더 많은 직원의 Ename , Sal을 표시하시오 

select ename, sal 

from   emp

where  SAL > ALL(select sal from emp where deptno = 20); 

예제) 부서번호가 20인 부서의 어떤(ANY)직원보다 연봉이 더 많은 직원의 Ename, sal을 표시하시오 

select ename, sal

from  emp

where  sal > ANY(select sal from emp where deptno = 20);

3) 다중컬럼(Multi Column) 서브쿼리 

- 결과가 컬럼 여러 개인 서브쿼리 

예제) 부서명이 'ACCOUNTING','RESEARCH'인 부서의 부서번호 별 직원수를 표시하시오 

select deptno, count(deptno)

from   emp

where deptno in(select dept

                from   dept 

                where  dname in ('ACCOUNTING','RESEARCH'))

group by deptno; 

예제) Sal이 3000이 넘어가는 직원명과 job, 부서번호를 표시하시오 (IN) 

select ename, job, deptno 

from   emp 

where  (ename, job,deptno) IN (Select ename, job,deptno from  emp where sal > 3000);

집합연산 

1) UNION 

- 두 쿼리의 결과값을 합쳐서 리턴함. 

- select 쿼리 UNION  select 쿼리2 UNION

- 두 쿼리의 결과 형식이 동일해야함 (기본적으로 DISTINCT적용) 

- 다른 테이블이라도 결과값의 형식만 일치하면 됨 

2) UNION ALL (중복을 허용하는 UNION) 

- SELECT 쿼리1 UNION ALL SELECT 쿼리2 UNION ALL ... 

예제) emp테이블에서 JOB이 'CLERK'과 'SALESMAN'인 직원의 부서번호를 표시하시오 (UNION사용) 

select deptno

from   emp

where  job = 'CLERK' 

UNION

select deptno

from   emp

where  job='SALESMAN';

예제) emp테이블에서 JOB이 'CLERK'과 'SALESMAN'인 직원의 부서번호를 표시하시오 (UNION ALL사용)

select deptno

from   emp

where  job = 'CLERK' 

UNION ALL

select deptno

from   emp

where  job='SALESMAN'; 

UNION ALL을 사용하면 중복을 허용하기 때문에 똑같은 내용이 여러번 나올 수 있다

3) INTERFSECT 

- 두 쿼리의 결과값 중 공통값을 찾아서 리턴

- SELECT 쿼리1 INTERSECT SELECT 쿼리2 

- 두 쿼리의 결과 형식이 동일해야함(기본적으로 DISTINCT적용) 

- 다른 테이블이라도 결과값의 형식만 일치하면 됨 

예제) EMP테이블에서 JOB이 'CLERK'인 직원들의 부서번호 'SALESMAN'인 직원들의 부서번호 중 동일한 것을 표시하시오. 

select deptno

from   emp 

where  job= 'CLERK' 

INTERSECT 

select deptno 

from   emp

where  job= 'SALESMAN';

4) MINUS / EXCEPT 

- 쿼리1결과값에서 쿼리2 결과값을 빼서 리턴함 

- SELECT 쿼리1 MINUS SELECT 쿼리2 

예제) EMP테이블에서 JOB이 CLERK인 직원들의 부서번호에서 SALESMAN인 직원들의 부서번호를 빼서 표시하시오.  

select deptno 

from   emp

where  job= 'CLERK'

MINUS

select deptno

from   emp

where  job = 'SALESMAN';