[ Oracle] 서브쿼리, 집합연산자

KIC/ORACLE

[ Oracle] 서브쿼리, 집합연산자

octopengj 2020. 10. 12. 14:19

서브쿼리( subQuery)

SQL문 내부에 또 다른 SQL구문이 존재하는 구문
서브쿼리 유형
1. single row subquery: 서브쿼리에서 메인쿼리로 하나의 row 값이 리턴되는 경우.
  
  -> 연산자: =, !=, ^=, <>, >, <, >=, <=
2. multiple row subquery: 서브쿼리에서 메인쿼리로 여러개의 row값이 리턴되는 경우.
  
  -> 연산자: in, not in, >all, <all, >any, <any

<문1> emp테이블에서 SCOTT사원이 받는 급여보다 많이 받는 사원을 구해서 그 사원의 사원번호, 이름, 업무, 급여순으로 출력하는 SQL?

1) SCOTT사원이 받는 급여 => 서브쿼리

select sal from emp where ename='SCOTT';
       SAL
----------
      3000

2) select empno, ename, job, sal from emp where sal > 3000; => 메인쿼리

SQL> select empno, ename, job, sal from emp where sal > 3000;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000

3) 1) + 2)

select empno, ename, job, sal from emp where sal > (select sal from emp where ename='SCOTT');

SQL> select empno, ename, job, sal from emp where sal > (select sal from emp where ename='SCOTT');

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7839 KING       PRESIDENT       5000

<문2> emp 테이블에서 가장 입사일이 오래된 사원의 정보를 출력?

(이름, 입사날짜 순으로 출력)

1) select min(hiredate) from emp;

SQL> select min(hiredate) from emp;

MIN(HIRE
--------
80/12/17

2) select ename, hiredate from emp where hiredate='80/12/17';

SQL> select ename, hiredate from emp where hiredate='80/12/17';

ENAME      HIREDATE
---------- --------
SMITH      80/12/17

3) 1) + 2)

SQL> select ename, hiredate from emp where hiredate=(select min(hiredate) from emp);

ENAME      HIREDATE
---------- --------
SMITH      80/12/17

<문3> emp테이블에서 사원번호가 7521인 사원과 업무가 같고(and) 사원번호가 7934인 사원보다 급여를 많이 받는 사원을 구해서 그 사원의 사원번호, 이름, 급여순으로 출력하시오.

1) 사원번호가 7521인 사원의 업무?

select job from emp where empno=7521;

SQL> select job from emp where empno=7521;

JOB
---------
SALESMAN

2) 사원번호가 7934인 사원의 급여?

select sal from emp where empno=7934;

SQL> select sal from emp where empno=7934;

       SAL
----------
      1300

3) 구해야하는 답

select empno, ename, job, sal from emp where job='SALESMAN' and sal > 1300;

SQL> select empno, ename, job, sal from emp where job='SALESMAN' and sal > 1300;

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

4) 1) + 2)

select empno, ename, job, sal from emp where job=(select job from emp where empno=7521) and sal > ( select sal from emp where empno=7934);

SQL> select empno, ename, job, sal from emp where job=(select job from emp where empno=7521) and sal > ( select sal from emp where empno=7934);

     EMPNO ENAME      JOB              SAL
---------- ---------- --------- ----------
      7499 ALLEN      SALESMAN        1600
      7844 TURNER     SALESMAN        1500

<문4> emp테이블에서 급여의 평균보다 적은 사원을 찾아서 그 사원의 이름, 업무, 급여순으로 출력하시오.

1) 급여의 평균 => select avg(sal) from emp;

SQL> select avg(sal) from emp;

  AVG(SAL)
----------
2073.21429

2) select ename, job, sal from emp where sal < 2073.21429;

SQL> select ename, job, sal from emp where sal < 2073.21429;

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
TURNER     SALESMAN        1500
ADAMS      CLERK           1100
JAMES      CLERK            950
MILLER     CLERK           1300

8 개의 행이 선택되었습니다.

3) select ename, job, sal from emp where sal < (select avg(sal) from emp);

SQL> select ename, job, sal from emp where sal < (select avg(sal) from emp);

ENAME      JOB              SAL
---------- --------- ----------
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
MARTIN     SALESMAN        1250
TURNER     SALESMAN        1500
ADAMS      CLERK           1100
JAMES      CLERK            950
MILLER     CLERK           1300

8 개의 행이 선택되었습니다.

서브쿼리의 특징

( ) 내부에 ;을 사용하면 안된다.

서브쿼리에 그룹함수를 사용 할 수 있다.

<문5> emp테이블에서 사원의 급여가 20번부서의 최소급여보다 많이 받는 부서를 부서별로(=group bydeptno) 출력하시오(부서번호, 최소급여순으로 출력).

1) 20번 부서의 최소급여?

select min(sal) from emp where deptno=20;

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

  MIN(SAL)
----------
       800

2) select deptno, min(sal) from emp group by deptno having min(sal) > 800;

SQL> select deptno, min(sal) from emp group by deptno having min(sal) > 800;

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        10       1300

3) select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);

SQL> select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp where deptno=20);

    DEPTNO   MIN(SAL)
---------- ----------
        30        950
        10       1300

<문6> 부서별로 최소급여를 받는 사원의 이름, 업무, 급여, 부서번호순으로 출력하시오.

서브쿼리의 종류

단일행 서브쿼리 => 실행결과(행이 한개)

연산자 =, <=, >=
다중행 서브쿼리 => 실행결과(행이 한개이상)

연산자 in, any, all

1) 부서별로 최소급여?

select min(sal) from emp group by deptno;

SQL> select min(sal) from emp group by deptno;

  MIN(SAL)
----------
       950
       800
      1300

2) 메인

select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno);

SQL> select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno);
select ename, job, sal, deptno from emp where sal=(select min(sal) from emp group by deptno)
                                                   *
1행에 오류:
ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

=> 오류 원인: 서브쿼리의 결과로 행이 한개이상 나오는데 처리해주는 연산자는 단일행 연산자를 사용했기 때문에

즉, 연산자오류 때문에 발생.

다음과 같이 수정해야 한다.

SQL> select ename, job, sal, deptno from emp where sal in(select min(sal) from emp group by deptno);

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SMITH      CLERK            800         20
JAMES      CLERK            950         30
MILLER     CLERK           1300         10

= 을 in으로 수정하여 작성한다.

물리적으로 여러개의 행이 나오는 서브쿼리는 => in

<문7> any 연산자를 사용하는 경우(다중행의 논리적인 경우)

emp 테이블에서 30번부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원을 구해서 그 사원의 이름, 업무, 급여, 부서번호를 출력. (단 30번 부서는 제외한다. (=10, 20번 부서만 적용))

1) 30번 부서의 최소급여?

select min(sal) from emp where deptno=30;

SQL> select min(sal) from emp where deptno=30;

  MIN(SAL)
----------
       950

2) select ename, job, deptno from emp where deptno!=30 and sal > 950;

SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > 950;

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
JONES      MANAGER         2975         20
CLARK      MANAGER         2450         10
SCOTT      ANALYST         3000         20
KING       PRESIDENT       5000         10
ADAMS      CLERK           1100         20
FORD       ANALYST         3000         20
MILLER     CLERK           1300         10

7 개의 행이 선택되었습니다.

3) 1) + 2)

SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > (select min(sal) from emp where deptno=30);

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
JONES      MANAGER         2975         20
CLARK      MANAGER         2450         10
SCOTT      ANALYST         3000         20
KING       PRESIDENT       5000         10
ADAMS      CLERK           1100         20
FORD       ANALYST         3000         20
MILLER     CLERK           1300         10

7 개의 행이 선택되었습니다.

4) 서브쿼리 단일행 -> any 로 변경하여 사용

SQL> select ename, job, sal, deptno from emp where deptno!=30 and sal > any(select sal from emp where deptno=30);

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
KING       PRESIDENT       5000         10
SCOTT      ANALYST         3000         20
FORD       ANALYST         3000         20
JONES      MANAGER         2975         20
CLARK      MANAGER         2450         10
MILLER     CLERK           1300         10
ADAMS      CLERK           1100         20

7 개의 행이 선택되었습니다.

any(조건식)

조건식에 해당하는 가장 작은 값을 구한뒤 기존 조건에 만족하는 값을 하나씩 비교해서 큰값 순으로 내림차순 정렬해주는 기능

<문8> 30번 부서의 최대급여를 받는 사원보다 적은 급여를 받는 사원의 정보를 구하시오.

1) 30번 부서의 최대급여?

select max(sal) from emp where deptno=30;

SQL> select max(sal) from emp where deptno=30;

  MAX(SAL)
----------
      2850

2) select ename, job, sal, deptno from emp where deptno!=30 and sal < (select max(sal) from emp where deptno=30);

SQL>  select ename, job, sal, deptno from emp where deptno!=30 and sal < (select max(sal) from emp where deptno=30);

ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SMITH      CLERK            800         20
CLARK      MANAGER         2450         10
ADAMS      CLERK           1100         20
MILLER     CLERK           1300         10

3) any 사용

SQL>select ename, job, sal, deptno from emp where deptno!=30 and sal < any(select sal from emp where deptno=30)


ENAME      JOB              SAL     DEPTNO
---------- --------- ---------- ----------
SMITH      CLERK            800         20
ADAMS      CLERK           1100         20
MILLER     CLERK           1300         10
CLARK      MANAGER         2450         10

물리적으로 여러개의 행이 나오는 서브쿼리는 => in

any 비교해서 큰값 => 큰값보다 작은순 => 오름차순

작은값 => 작은값을 기준 => 내림차순

`> any(작은값을 구하는 SQL)` => 내림차순

`< any(큰값을 구하는 SQL)` => 오름차순

<문9> 업무가 MANAGER인 사원의 이름, 업무, 부서명, 근무지를 출력하시오.

서브쿼리 -> where 조건식 이외의 패턴

인라인뷰 -> SQL구문에서 from 뒤에 서브쿼리가 나오는 구문

select e.ename, e.job, d.dname, d.loc from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';

SQL> select e.ename, e.job, d.dname, d.loc from emp e,dept d where e.deptno=d.deptno and e.job='MANAGER';

ENAME      JOB       DNAME          LOC
---------- --------- -------------- -------------
JONES      MANAGER   RESEARCH       DALLAS
BLAKE      MANAGER   SALES          CHICAGO
CLARK      MANAGER   ACCOUNTING     NEW YORK

필드갯수 줄이고, 조건식에 대한 데이터만 메모리에 올리도록 수정

SQL> select e.ename, e.job, d.dname, d.loc from(select ename, job, deptno from emp where job='MANAGER') e,dept d where e.deptno=d.deptno;

ENAME      JOB       DNAME          LOC
---------- --------- -------------- -------------
JONES      MANAGER   RESEARCH       DALLAS
BLAKE      MANAGER   SALES          CHICAGO
CLARK      MANAGER   ACCOUNTING     NEW YORK

<문10> 서브쿼리 -> select와 from 사이에도 서브쿼리가 나올 수 있다.

dept테이블에서 부서위치가 NEW YORK(중앙), NEW YORK을 제외한나머지 도시근무(변두리)

SQL> select deptno, dname, (case when deptno in(select deptno from dept where loc='NEW YORK') then '중앙' else '변두리주' end) "부서위치" from dept;

    DEPTNO DNAME          부서위치
---------- -------------- --------
        10 ACCOUNTING     중앙
        20 RESEARCH       변두리주
        30 SALES          변두리주
        40 OPERATIONS     변두리주

<문11> emp테이블에서 부서별로 부서번호, 인원수, 평균급여, 급여합계, 최소급여, 최대급여를 구해서 emp_deptno 백업테이블로 저장하시오.

서브쿼리에서의 사용시 주의할 점 => 테이블을 생성할 수 있다.

create table 테이블명

백업 table => create table 백업t명 as SQL구문

=> SQL구문에 해당하는 조건을 찾아서 그 데이터를 출력

=> 테이블에 만들어서 출력(테이블에 저장)

아래의 오류 주의

SQL> create table emp_deptno as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno;

create table emp_deptno as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno
                                          *
1행에 오류:
ORA-00998: 이 식은 열의 별명과 함께 지정해야 합니다

=> 가상필드는 필드로 복사가 안되기 때문에 발생하는 에러메세지

=> 실제존재X 계산식의 가상필드에 어울리는 이름을 부여 -> 별명, 별칭

SQL> create table emp_deptno(deptno, e_count, e_avg, e_sum, e_min, e_max) as select deptno, count(*), avg(sal), sum(sal), min(sal), max(sal) from emp group by deptno;

테이블이 생성되었습니다.

SQL> select * from emp_deptno

    DEPTNO    E_COUNT      E_AVG      E_SUM      E_MIN      E_MAX
---------- ---------- ---------- ---------- ---------- ----------
        30          6 1566.66667       9400        950       2850
        20          5       2175      10875        800       3000
        10          3 2916.66667       8750       1300       5000

sql>desc emp_deptno; //실제로 필드가 만들어져서 복사된 상태에서 저장

SQL> desc emp_deptno;
 이름                                      널?      유형
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                             NUMBER(2)
 E_COUNT                                            NUMBER
 E_AVG                                              NUMBER
 E_SUM                                              NUMBER
 E_MIN                                              NUMBER
 E_MAX                                              NUMBER

형식) create table 백업t명(가상필드에 대응되는 필드명,필드명2,,,,)

as sql구문

테이블의 구조(=Schema(스키마))만 복사하기=>테이블만 구조만 복사하고 싶다.

데이터는 필요X

폴더(파일 X)

create table 백업t명 as select * from 원본t명 //내용 O, 필드 O

**** 제약조건은요? =>복사X ****

create table 백업t명 as select * from 원본t명 where 조건식=>조건식에 해당

create table 백업t명 as select * from 원본t명 where 1=0 //조건식이 거짓

SQL> create table b_emp2 as select empno,ename,job from emp where 1=0;

테이블이 생성되었습니다.

SQL> select count(*) from b_emp2;

  COUNT(*)
----------
         0

<문12> 부서번호가 40부서에 근무할 직원의 데이터를 입력하라(서브쿼리 이용)

서브쿼리=>DML을 이용해서 서브쿼리 사용이 가능하다.

DML의 종류: insert, update, delete

SQL> create table b_emp3 as select * from emp;

테이블이 생성되었습니다.

SQL> insert into (select empno, ename, sal, hiredate, job, deptno from b_emp3 where deptno=40) values(7777, 'JANG', 4000, to_date('20-07-06','rr-mm-dd'), 'MANAGER',40);

1 개의 행이 만들어졌습니다.

SQL> select * from b_emp3 where deptno=40;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7777 JANG       MANAGER              20/07/06       4000
        40

insert into + select 구문을 통해서 필드를 나열하기 위해서 select구문 사용

<문13> b_emp3 테이블에서 SCOTT의 업무와 급여가 일치하도록 JONES의 업무와 급여를 수정하는 SQL을 작성하시오.

update+subQuery 적용

형식) update 수정t

set (필드명1, 필드명2, 필드명3...) =(서브쿼리를 이용) 수정할 값

where 조건식

1) JONES의 업무와 급여

select job, sal from emp

where ename=upper('&ename');

SQL> select job, sal from emp where ename=upper('&ename');
ename의 값을 입력하십시오: jones
구   1: select job, sal from emp where ename=upper('&ename')
신   1: select job, sal from emp where ename=upper('jones')

JOB              SAL
--------- ----------
MANAGER         2975

SCOTT

SQL> /
ename의 값을 입력하십시오: scott
구   1: select job, sal from emp where ename=upper('&ename')
신   1: select job, sal from emp where ename=upper('scott')

JOB              SAL
--------- ----------
ANALYST         3000

일치하도록 작성

SQL> update b_emp3 set(job, sal)=(select job, sal from b_emp3 where ename='SCOTT') where ename='JONES';

1 행이 갱신되었습니다.

SQL> select job, sal from b_emp3 where ename='JONES';

JOB              SAL
--------- ----------
ANALYST         3000

<문14> b_emp3의 자료중에서 부서명이 'SALES'인 사원의 정보를 삭제하시오.

delete구문 + subQuery 사용이 가능하다.

형식) delete from 테이블명 // 모든 데이터 삭제

delete from 테이블명 where 조건식(=서브쿼리)

1) 부서명이 SALES인 부서번호?

select deptno from dept where dname='SALES';

SQL> select deptno from dept where dname='SALES';

    DEPTNO
----------
        30

2) delete from b_emp3 where deptno = (select deptno from dept where dname='SALES');

(30번 부서를 알고있다면 서브쿼리 대신 30(부서번호) 사용이 가능하나 모를 때 서브쿼리 사용하여 삭제 )

SQL>  delete from b_emp3 where deptno = (select deptno from dept where dname='SALES');

6 행이 삭제되었습니다.

inser, update, delete 복구 기능

rollback // 되돌리기

3) rollback

SQL> rollback
  2  ;

롤백이 완료되었습니다.

SQL> select count(*) from b_emp3;

  COUNT(*)
----------
        14

<문15> exit 연산자 => 서브쿼리의 결과가 참인지 아닌지를 체크해주는 연산자

서브쿼리에서 적어도 1개의 행을 리턴 => 참

리턴해주는 행이 없음을 의미 => 거짓

select * from emp where 1=2; 거짓

SQL> select * from emp where 1=2;

선택된 레코드가 없습니다.

select * from emp where 2=2; 참

SQL> select * from emp where 2=2;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7369 SMITH      CLERK           7902 80/12/17        800
        20

      7499 ALLEN      SALESMAN        7698 81/02/20       1600        300
        30

      7521 WARD       SALESMAN        7698 81/02/22       1250        500
        30


     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7566 JONES      MANAGER         7839 81/04/02       2975
        20

      7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400
        30

      7698 BLAKE      MANAGER         7839 81/05/01       2850
        30


     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7782 CLARK      MANAGER         7839 81/06/09       2450
        10

      7788 SCOTT      ANALYST         7566 87/04/19       3000
        20

      7839 KING       PRESIDENT            81/11/17       5000
        10


     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7844 TURNER     SALESMAN        7698 81/09/08       1500          0
        30

      7876 ADAMS      CLERK           7788 87/05/23       1100
        20

      7900 JAMES      CLERK           7698 81/12/03        950
        30


     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM
---------- ---------- --------- ---------- -------- ---------- ----------
    DEPTNO
----------
      7902 FORD       ANALYST         7566 81/12/03       3000
        20

      7934 MILLER     CLERK           7782 82/01/23       1300
        10


14 개의 행이 선택되었습니다.

부서테이블의 부서번호가 10인 데이터를 보여주시오(emp).

select empno, ename, sal from emp where exists(select * from dept where deptno=10);

SQL> select empno, ename, sal from emp where exists(select * from dept where deptno=10);

     EMPNO ENAME             SAL
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600
      7521 WARD             1250
      7566 JONES            2975
      7654 MARTIN           1250
      7698 BLAKE            2850
      7782 CLARK            2450
      7788 SCOTT            3000
      7839 KING             5000
      7844 TURNER           1500
      7876 ADAMS            1100

     EMPNO ENAME             SAL
---------- ---------- ----------
      7900 JAMES             950
      7902 FORD             3000
      7934 MILLER           1300

14 개의 행이 선택되었습니다.

집합연산자

union, union all
서로 다른 두개의 SQL문장의 실행결과를 하나로 합쳐서 결과를 보여주는 연산자.

-- emp 테이블에서 업무가 MANAGER인 사원의 사번, 이름을 구하시오.

select empno, ename from emp where job='MANAGER';

SQL> select empno, ename from emp where job='MANAGER';

     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK

-- 이름이 A로 시작하는 사원의 사번, 이름을 구하시오.

select empno, ename from emp where ename like 'A%';

SQL> select empno, ename from emp where ename like 'A%';

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7876 ADAMS

-- 두 문장을 union으로 합치기

select empno, ename from emp where job='MANAGER' union select empno, ename from emp where ename like 'A%';

SQL> select empno, ename from emp where job='MANAGER' union select empno, ename from emp where ename like 'A%';

     EMPNO ENAME
---------- ----------
      7499 ALLEN
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7876 ADAMS

-- union all

SQL> select empno, ename from emp where job='MANAGER' union all select empno, ename from emp where ename like 'A%';

     EMPNO ENAME
---------- ----------
      7566 JONES
      7698 BLAKE
      7782 CLARK
      7499 ALLEN
      7876 ADAMS

앞문장 결과 + 뒷문장 결과 (정렬안함)

집합연산자의 주의할 점

서로 다른 필드를 사용해도 상관없다.
합쳐지는 필드의 자료형은 반드시 같아야 된다.
합쳐지는 필드의 갯수는 서로 같아야 된다.

'KIC > ORACLE' 카테고리의 다른 글

[Oracle] 인덱스, 시퀀스 (0)	2020.10.12
[Oracle] 뷰 (0)	2020.10.12
[Oracle] 조인개요 및 작성법 (0)	2020.10.12
[Oracle] 그룹함수, DDL, 테이블 제약조건 (0)	2020.10.12
[Oracle] 함수활용 (0)	2020.10.12

현재글[ Oracle] 서브쿼리, 집합연산자

기록하는 노트

패스트캠퍼스 #자바스크립트 #벨로퍼트, 패스트캠퍼스,

Today :
Yesterday :

기록하는 노트