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

< 서브쿼리, 집합연산 >

* 서브쿼리는 쿼리문 안에 또 다른 쿼리문이 들어있는 형태를 말한다.

  서브쿼리문은 보통 ( )로 감싼다.

* MySQL과는 달리 오라클은 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS) 연산을 지원한다.

 

---

* 서브쿼리

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

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

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

- ORDER BY를 사용하지 못한다.

- 사용 가능한 위치

 -> SELECT / FROM / WHERE / HAVING / ORDER BY

     VALUES(INSERT) / SET(UPDATE)...

 

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

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

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

 

ex) 1. 부서명이 'ACCOUNGTING'인 부서의 직원 수를 표시하시오.

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

-> 1. select count(*) from emp where deptno = (select deptno from dept where dname = 'ACCOUNGTING');

-> 2. select ename, Sal from emp where Sal > (select avg(Sal) from emp where deptno = (select deptno from dept where dname = 'RESEARCH'));

 

 

- 다중행(Multi Row) 서브쿼리

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

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

 

  ♧ ALL

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

  ex) Sal > ALL (select Sal...)

 

  ♧ ANY

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

  ex) Sal > ANY (select Sal...)

 

  ♧ IN

  - 결괏값들을 비교하여 값이 같으면 해당 결과 출력

 

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

     2. 부서 번호가 20인 부서의 어떤(ANY) 직원보다 연봉이 더 많은 직원의 Ename과 Sal을 표시하시오.

     3. 부서명이 'ACCOUNGTING', 'RESEARCH'인 부서의 부서 번호 별 직원 수를 표시하고, Sal이 3000이 넘어가는 직원명과 JOB, 부서 번호를 표시하시오.(IN)

-> 1. select ename, Sal from emp where Sal > ALL (select Sal from emp where deptno = 20);

      (제일 많은 사람이 출력됨)

-> 2. select ename, Sal from emp where Sal > ANY (select Sal from emp where deptno = 20);

      (제일 작은 사람보다 많은 사람들이 출력됨)

-> 3. select deptno, count(deptno) from emp where deptno IN (select deptno from dept where dname in ('ACCOUNGTING', 'RESEARCH')) group by deptno;

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

 

 

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

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

---

* 집합연산

- 각종 집합 연산 지원

- 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS)...

 

♧ UNION

  : 두 쿼리의 결괏값을 합쳐서 리턴함

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

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

SELECT 쿼리 1 UNION SELECT 쿼리 2 UNION...

 

♧ UNION ALL

  : 중복을 허용하는 UNION

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

 

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

     2. UNION / UNION ALL을 사용해 차이를 확인하시오.

-> select deptno from emp where job = 'CLERK' UNION select deptno from emp where job = 'SALESMAN';

-> select deptno from emp where job = 'CLERK' UNION ALL select deptno from emp where job = 'SALESMAN';

-> 차이: union all의 경우 중복값 표시 됨

 

 

♧ INTERSECT

  : 두 쿼리의 결괏값 중 공통값을 찾아서 리턴함

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

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

  SELECT 쿼리 1 INTERSECT SELECT 쿼리 2

 

♧ MINUS / EXCEPT

  : 쿼리 1 결괏값에 쿼리 2 결괏값을 빼서 리턴함

  SELECT 쿼리 1 MINUS SELECT 쿼리 2

 

ex) 1. emp 테이블에서 JOB이 'CLERK"인 직원들의 부서 번호와 'SALESMAN"인 직원들의 부서 번호 중 동일한 것을 표시하시오.

    2. emp 테이블에서 JOB이 'CLERK'인 직원들의 부서 번호에서 'SALESMAN'인 직원들의 부서 번호를 빼서 표시하시오.

-> select deptno from emp where job = 'CLERK' INTERSECT select deptno from emp where job = 'SALESMAN';

-> select deptno from emp where job = 'CLERK' MINUS select deptno from emp where job = 'SALESMAN';

 

---

< Point > 

☆ 서브 쿼리의 형태는 크게 하나의 컬럼으로 하나의 행을 리턴하는 단일행 서브 쿼리, 하나의 컬럼으로 여러 행을 리턴하는 다중행 서브 쿼리, 여러 컬럼으로 레코드를 리턴하는 다중 컬럼 서브 쿼리가 있다.

☆ 다중행 연산자에는 ALL, ANY, IN/EXISTS가 있다.

☆ UNION 연산의 결과에서 중복되는 레코드는 제외한다.

    중복되는 레코드는 포함하려면 UNION ALL을 사용하면 된다.

---

< 요약 >

* 서브쿼리

- 쿼리문 내에 다른 쿼리가 내장된 형태

- 단일행 ( =, >, <, ...)

- 다중행 (IN, ANY, ALL, EXISTS)

- 다중컬럼

* 집합연산

- 두 개 이상의 쿼리 결과에 대한 합집합/교집합/차집합 연산