15-1. 고급서브쿼리문(exists문, with절)

■ exists 문

(3권 246페이지)

 

문제510.

부서 테이블의 부서번호를 출력하는데

사원 테이블에 존재하는 부서번호만 출력하시오

(exists 로)

 

select deptno

from dept

where deptno in(select deptno from emp);

                   ↓

select deptno

 from dept d

 where exists ( select  'A'

                           from emp e

                            where e.deptno = d.deptno);

공백도 되고 아무거나 다 됨.

※ exists 문은 메인 쿼리부터 수행한다.

   exists 문은 메인쿼리의 데이터를 서브쿼리에서 찾을 때

   존재하면 더 이상 찾지 않고 멈춘다.

   그래서 검색속도가 빠르다.

 

 

select  'A' 

 from emp e

  where e.deptno = 10;

 

부서번호 10번인 사원수 만큼 A 로 출력된거다.

 

 

exists 는 deptno 10번 찾으라고 시키면

처음부터 훑다가 10번 발견하면 작업 완료한다.

in 과 다른 건 끝까지 다 안 본 다는 것이다.

exists 는 존재여부 확인을 위해 발견순간 작업종료.

in 은 모든 거 다 찾을 때 까지 다 검색하고 작업종료.

그래서

exists 는 속도가 엄청 빠르다.

 

 

 

문제511.

아래의 sql 을 튜닝하시오

 

튜닝후

select distinct 관리자.ename

 from emp 사원, emp 관리자

where 사원.mgr = 관리자.empno;

 

 

튜닝전

select distinct 관리자.ename

 from emp 사원, emp 관리자

where exists( select  'A'

                from emp

              where 사원.mgr = 관리자.empno);

 

 

 

문제512.

우리반 테이블에서

telecom_price테이블에서 존재하지 않는

통신사가 어떤건지 통신사 이름을 출력하시오!

(우리반 테이블에는 없고

 telecom_price 엔 있는 통신사이름을 출력하시오)

 

update emp2

set telecom = 'SK'

where telecom = 'sk';

 

select telecom_name

from telecom_price t

where not exists(select 'A'

                             from emp2 e

                             where e.telecom = t.telecom_name);

 

 

 

문제513.

telecom_price 를 ctas 로 백업을 받으세요~

 

create table telecom_price_backup

as

select * from telecom_price;

 

 

 

문제514.

telecom_price 에는 존재하는데

우리반 테이블에는 존재하지 않는

통신사를 telecom_prcie 에서 지우시오

 

노답

delete from telecom_price

where in(

select telecom_name

from telecom_price t

where not exists(select 'A'

                             from emp2 e

                             where e.telecom = t.telecom_name) );

 

 

답

       delete from telecom_price t

           where not exists(select 'A'

                             from emp2 e

                             where e.telecom = t.telecom_name) ;

필요한건 조건확인일 뿐이니까 편의상 'A'.

 

 

 

■ with 절

 (3권 255 페이지)

 

복잡한 쿼리내에 동일한 쿼리 블럭이 두 번 이상 발생하는

경우에 코드도 단순해지고 성능도 좋아지는 SQL

 

 

문제515.

직업, 직업별 토탈월급을 출력하시오.

 

select job, sum(Sal)

from emp

group by job

 

 

문제516.

직업별 토탈월급들의 평균값을 출력하시오

 

select avg(sum(Sal))

from emp

group by job;

직업이 5개니까

토탈월급을 5로 나눈것과 같게 나와야 함

 

 

문제517.

직업, 직업별 토탈월급을 출력하는데

직업별 토탈월급들의 평균값보다 더 큰것만 출력하시오

 

내오답

select ename, sal

from (select  avg(sum(sal))토탈월급평균

         from emp

         group by job) ee, emp e

where sal >토탈월급평균;

 

 

답

select job, sum(Sal)

 from emp

group by job

 having sum(sal) > (select avg(sum(sal))

                                  from emp

                                  group by job);

 

 

 

문제518.

위의 sql 을 with 절로 변경하시오

 

with job_sumsal as (select job, sum(Sal) 토탈월급

                            from emp

                          group by job)

 select job, 토탈월급

   from job_sumsal

  where 토탈월급 > (select avg(토탈월급)

                          from job_sumsal) ;

 

*with 절에서 바로 table과 같은걸 만들어 낸다

(테이블은 아니다)

필요 쿼리를 테이블 형태로 만들어내서

필요작업 수행.

517문제 답은 두개 테이블을 돌려서 작업하느라

시간이 2배 걸리는데

with 절은 하나의 임시테이블 가지고 수행하기에

훨씬 빠르다.

 

temp 를 스캔해서 작업하는 걸 볼 수 있다.

 

 

 

힌트를 쓰면 더 빨라진다.

with job_sumsal as (select /*+ inline */ job, sum(Sal) 토탈월급

                            from emp

                          group by job)

 select job, 토탈월급

   from job_sumsal

  where 토탈월급 > (select avg(토탈월급)

                          from job_sumsal) ;

temp.table 훑고지나가는 내역이 사라짐

 

 

 

 

※ with 절의 유명한 힌트 2가지 ?

1.  /*+ inline */        -----> temp 테이블 안 만들겠다.

  (with 절이 아니라 그냥

  서브쿼리로 수행)

 

2. /*+ materialize */  -----> temp 테이블 만들겠다.

 

 

 

문제519.(오늘의 마지막 문제)

아래의 sql 을 with 절로 변경하시오

 

select d.loc, sum(sal)

 from emp e, dept d

 where e.deptno = d.deptno

 group by d.loc

 having sum(Sal) > (select avg(sum(sal))

                                  from emp e, dept d

                                where e.deptno = d.deptno

                                group by d.loc)

     and sum(Sal) > (select sum(sum(Sal)) / 4

                                    from emp e, dept d

                                   where e.deptno = d.deptno

                                group by d.loc);

---------------------------------------------------------------

with loc_sumsal as (select d.loc LC, sum(sal) 토탈월급

                                    from emp e, dept d

                                    where e.deptno = d.deptno

                                     group by d.loc)

select LC, 토탈월급

  from loc_sumsal

 where 토탈월급 > (select avg(토탈월급)

                                from loc_sumsal)

 and                               

        토탈월급 >(select sum(토탈월급)/4

                                    from loc_sumsal);

 

***별칭지정 하니까 나옴

 

with 테이블명 as (쿼리 *조인할 경우 별칭지정* )

select 별칭

from with의 테이블명

 where __________

 and ____________;

 

 

 

■ 어제 마지막 시간 배운 with 절 복습

*with 절을 사용했의 이점?

 1. 하나의 SQL 안에서 반복되는 쿼리문을 반복 작성하지 않고

단순하게 작성할 수 있는 장점

 

예:

select deptno, sum(sal)

 from emp

 group by deptno

 having sum(sal) > (select avg(sum(sal))

                                   from emp

                                  group by deptno);

 

                              ↓

with emp5 as (select deptno, sum(sal)토탈월급

                   from emp

                   group by deptno)

 select deptno, 토탈월급

 from emp5

 where 토탈월급 > (select avg(토탈월급)

                           from emp5);

 

 

 

create view emp5

 as

 select deptno, sum(sal)토탈월급

 from emp

 group by deptno;

 

 

select deptno, 토탈월급

 from emp5

 where 토탈월급 > (select avg(토탈월급)

                            from emp5) ;

 

 

-VIEW와 WITH 의 차이

*view 와 with 절의 차이는?

view 는 view를 쿼리할 때마다 view 의 무거운 SQL 을 수행해야 한다.

그러나 with 절은 temp table 만들 때 한번만 무거운 SQL 을

수행하면 된다.

 

2. 급하게 테이블을 만들어야 할 때 만들려고 DBA에게 요청하지 않아도 되고 with 절로 temp table 을 만들어서 쿼리할 수 있다.

 

예: 수학자 가우스가 초등학교 때 알아낸 공식을 이용해서

1 부터 10 까지 다 더한 숫자의

 

 

 

 

문제520.

가우스가 초등학교 때 알아낸 공식으로 1부터 10까지의 합이 55임을 SQL 로 구하시오!

(connect by 이용해서/with절 이용해서)

 

create table number10

 as

 select rownum as rn

 from dual

 connect by rownum < 11;

 

select sum(rn)

 from number10;

위 두과정을 with 절을 이용해서 한번에 할 수 있다

                    ↓

 with number10 as (

                        select rownum as rn

                          from dual

                          connect by rownum < 11)

select sum(rn)

from number10;

 

 create 안하고 바로 해도 됨

select sum(rownum)

from dual

connect by rownum < 11;

 

 

 

문제521.

가우스 공식으로 1부터 100까지의 합을 SQL 로 구하시오.

 

create table number100

 as

 select rownum as rn

 from dual

 connect by rownum < 101;

 

select sum(rn)

 from number100;

          ↓

with number100 as (

select rownum as rn

 from dual

 connect by rownum < 101)

select sum(rn)

 from number100;

 

 

 

select (1+100) * (100/2)

from dual;

 

 

 

 

문제522.

1부터 100,000,000(1억) 까지 다 더한 숫자의 합을 구하시오

 

select (1+100000000) * (100000000/2)

from dual;

 

 

with num1 as (select rownum as rn

                          from dual

                            connect by rownum < 100000001)

select sum(rn)

 from num1;  >>>>>이건 메모리 딸린다고 안 된다함.

 

 

 

*-with 절 힌트

1. inline         ------------>temp 테이블 사용 안 하겠다.

2. materialize----------->temp 테이블 사용하겠다.

 

select *

 from dba_temp_files;

템프파일 위치가 나오는데

찾아보니 20메가바이트 밖에 안됨.

 

WITH 절의 디폴트값은 materialize 다.