[Oracle SQL]12일차_23.07.05

망구씨·2023년 7월 5일

EXISTS HAVING절 서브쿼리 NOT EXISTS SELECT절 서브쿼리 from절 서브쿼리

Oracle SQL

목록 보기

12/21

오늘의 TIL

서브 쿼리 사용하기 4(EXISTS와 NOT EXISTS)

서브 쿼리 사용하기 5(HAVING절의 서브 쿼리)

서브 쿼리 사용하기 6(FROM절의 서브 쿼리)

서브 쿼리 사용하기 7(SELECT절의 서브 쿼리)

복습

서브쿼리 사용시, not in 사용하면 서브쿼리에서 메인쿼리로 null값이 리턴되지 않도록 주의해야 합니다.

서브 쿼리 사용하기 4(EXISTS와 NOT EXISTS)

짝꿍의 EMP 테이블에는 존재하는데 나의 emp 테이블에는 존재하지 않는 데이터가 무엇인지 확인하려면?
1. minus
2. exists 와 not exists

예제. 부서테이블의 부서번호를 출력하시오!

select deptno 
  from dept;

예제. minus를 이용해서 부서테이블에는 존재하는데 사원테이블에는 존재하지 않는 부서번호 출력

select deptno from dept
minus
select deptno from emp;

minus 는 위와같이 두 테이블의 데이터의 차이를 확인하고 싶을 때 사용!

          < 운영DB >                      < 테스트DB >

          주문테이블    ---------------->  주문 테이블
              ↑
          실시간 입력                        ↓ (테이블 생성하기)
             ↓                            create table emp_test
insert into emp(empno, ename              as
                 sal, job, deptno)         select * from emp@
   values(3984, 'JONES2', 4500, 'dba',80)       
   //쌩뚱맞은 데이터 넣어보기

db link 이름을 확인하고싶으면!

 select * from dba_db_links;

문제 386. 테스트db 에서 수행) 운영 db 의 테이블을 엑세스 하기 위한 db 링크를 생성하시오!

create public database link dblink_swg
 connect to C##scott
 identified by tiger
 using '192.168.19.24:1521/xe' ; // 운영db의 아이피주소:포트번호/db이름
 
 select * from tab@dblink_swg;

문제 387. 테스트db 에서 수행) 운영 db쪽에 있는 emp 테이블을 테스트 db에 다음과 같이 생성하시오!

create table emp_swg
 as 
       select * from emp@dblink_swg;
       
select * from emp_swg;

문제 388. 테스트db 에서 수행) test db의 emp 테이블과 운영 db의 emp 테이블의 차이를 확인하기
(운영쪽에는 있는데 테스트에는 없는 데이터 검색)

select * from emp@dblink_swg // 운영디비
minus
select* from emp; // 테스트디비

문제 389. 테스트db 에서 수행) 운영db의 emp table에 존재하는데 테스트db에는 존재하지 않는 데이터를운영db em table에 입력

insert into emp
select * from emp@dblink_swg
minus
select* from emp;

commit;
이렇게 하면 테스트db의 emp 에 운영디비의 데이터가 들어온다.

문제 390. 테스트db 에서 수행) 테스트 db의 emp table을 truncate 하세요!
(truncate를 수행하몀ㄴ 데이터는 다 지워지고 테이블 구조만 남는다)

truncate table emp; // 다 날라감

select* from emp;

문제 391. 운영 db의 emp table을 테스트 db의 emp 테이블에 입력하시오!

insert into emp
 select *
 from emp@dblink_swg; // 다시 운영디비 가져오고

select* from emp; // 확인해본 후 

commit; // 모두 다 잘 들어왔으면 commit!

문제 392. dept 테이블에서 부서번호를 출력하는데, emp 테이블에 존재하는 부서번호들만 출력하기

select deptno 
 from dept d
 where exists (select 'X'
                from emp e
                where e.deptno = d.deptno);
exists 작동원리

메인쿼리 테이블(select 문장)을 먼저 select 한다. ->

서브쿼리에서 10번이 emp 테이블에 존재하는지 찾아본다(where e.deptno = d.deptno 부분에서 d.deptno 자리에 dept table의 부서번호를 하나씩 넣어본다. 내가말고 오라클이 해봄 ) 뒤에 아무리 10번이 많아도 더이상 찾지 않는다. 존재하면 거기서 찾기는 끝. 10 찾으면 멈추고 20 해보고 ,,30해보고,,

특이사항은 exists 할 때 where절에 컬럼명을 쓰지 않는다. !!

풀스캔 하지 않고 찾으면(존재하면) 더이상 스캔하지않고 멈추기때문에 빠르게 데이터를 찾을 수 있다.
-> 검색성능이 좋다. (다 좋은건 아니고, main query에 데이터 건수가 몇건 되지 않을 때 좋은 검색속도를 보인다. )

테이블 별칭 사용!!!!!!!!

메인쿼리의 컬럼이 서브쿼리에 들어가고 있다. (부서번호가 10이면 그걸 넣어보고...했던 과정)

문제 393. telecom_table 테이블에서 통신사를 출력하는데, 우리반테이블(emp17)에 존재하는 통신사만 출력

1. 
select telecom 
 from emp17 e
 where exists (select 'X'
                from telecom_table t
                where e.telecom = t.telecom);
----------------------------------------------------------------------------------------
2. 
select telecom 
 from telecom_table t
 where exists (select 'X' // 'X'여기자리는 무엇을 쓰던 상관없다. 만만한것은 `*`..!
                from emp17 e
                where e.telecom = t.telecom);

첫번째 코드는 순서를 헷갈렸다. 2번이 맞음! telecom_table 테이블에서 통신사를 출력하는데 여기의 조건이 우리반테이블(emp17)에 존재 니까

문제 394. telecom_table에는 존재하는 통신사인데 emp17에는 존재하지 않는 통신사 (not exists)

select telecom 
  from telecom_table t
  where not exists (select *
                from emp17 e
                where e.telecom = t.telecom);

문제 340. 운영 db의 emp 테이블에는 존재하는 사원번호인데, 테스트db의 emp 테이블에는 존재하지 않는 사원번호가 무엇일까요?

select empno
 from emp@dblink_swg e
 where not exists (select *
                   from emp t
                   where e.empno = t.empno);
메인쿼리가 운영디비, 서브쿼리쪽이 테스트디비를 작성한다.

운영디비일때, 테스트디비일 때 다른 것 같은데 메인쪽이 운영디비를 쓰는듯.

문제 396. 운영db 에서 수행)

insert into emp(empno, ename, sal, deptno)
  values (8888, 'AAA', 3000, 30);
  
  insert into emp(empno, ename, sal, deptno)
  values (9999, 'BBB', 4000, 20);
  
commit;

문제 397. HR 계정에 departments 테이블에 있는 department_id인데, employees 테이블에는 존재하지 않는 department_id를 출력

select department_id
  from departments d
  where not exists( select *
                    from employees e
                    where d.department_id = e.department_id);

문제 398. dept 테이블에서 부서번호를 출력하는데 emp 테이블에 있는 부서번호만 출력하시오

1. exists -> 튜닝후
select deptno
  from dept  d
  where exists ( select *
                 from emp e
                 where e.deptno = d.deptno);
2. in -> 튜닝전
select deptno
  from dept 
  where deptno in (select deptno
                          from emp);
결과 출력이 같다 !

차이점은, in은 서브쿼리부터 실행되면서 emp 테이블의 부서번호를 다 찾아보고 dept 테이블에서 부서번호도 다 찾아보고 결과 출력된다. 성능 느리다!

문제 399. dept 테이블에서 deptno를 출력하는데 dept테이블에는 존재하고 emp 테이블에는 존재하지 않는 deptno 출력하시오(2가지 방법)

1. not exists
select deptno 
 from dept d
 where not exists ( select *
                    from emp e
                    where e.deptno = d.deptno);
2. not in
select deptno
  from dept 
  where deptno not in (select deptno
                          from emp
                          where deptno is not null); // not in 쓸때는 null값이 출력되지 않도록 !
(DBA관점에서 SQL을 사용할 때) 두 테이블 사이에 차이가 나는 데이터가 무엇인지 확인하고싶을 때 사용하는 SQL ?????
1. exists / not exists
2. minus

문제 400. emp 테이블을 emp_backup 으로 백업하기

create table emp_backup
as
 select *
 from emp;
 
select * from emp_backup;

문제 401. emp 테이블의 데이터중 부서번호가 20번인 사원들을 지우고 커밋하기!

delete from emp
 where deptno = 20;

commit;

위 경우 실수로 지웠다고 가정했을 때 먼저 아래처럼 하자!

문제 402. emp 테이블과 emp_backup의 데이터 차이를 확인하시오

먼저 어떤것을 지웠는지, 차이를 확인해보기!
1. minus
2. not exists

웬만하면 minus 쓰는데 너무 속도 느리면 not exists
select * from emp_backup
 minus
 select * from emp;               // 이렇게도 해보고
--------------------------------------------------------------------- 
select * from emp
 minus
 select * from emp_backup;        // 반대로도 해보고!
복구하기!!
insert into emp
 select * from  emp_backup
 minus
 select * from emp;

commit;

점심시간문제

1. non pairwise

select ename, age, telecom 
  from emp17
  where age in ( select age
                 from emp17
                 where address like '%남양주%') 
    and telecom in ( select telecom
                     from emp17
                     where address like '%남양주%');

2. pairwise

select ename, age, telecom 
from emp17
where (age, telecom) in (select age, telecom
                         from emp17
                         where address like '%남양주%');

둘다 두개 출력된다.

서브 쿼리 사용하기 5(HAVING절의 서브 쿼리)

select 문의 6가지절에서 서브쿼리를 쓸 수 있는 절은?
select (사용가능) (scalar subquery)
from (사용가능) (in line view)
where (사용가능)
group by (X)
having (사용가능) (scalar subquery)
order by (사용가능) (scalar subquery)

scalar subquery : 다른절에도 확장해서 쓸 수 있다.

문제 403. 직업, 직업별 토탈월급을 출력하시오 !

select job, sum(sal)
 from emp
 group by job;

문제 404. 직업, 직업별 토탈월급을 출력하는데 직업이 SALESMAN인 사원들의 토탈월급보다 더 큰 사원들만 출력

select job, sum(sal)
 from emp
 group by job
 having sum(sal) > (select sum(sal)
                      from emp
                      where job = 'SALESMAN');
WHERE 절에는 그룹함수로 검색조건을 줄 수 없고 HAVING절에 주어야 한다!

문제 405. 통신사, 통신사별 인원수를 출력하는데 통신사별 인원수가 LG의 인원수보다 큰 것만 출력

select telecom, count(*)
 from emp17
 group by telecom
 having count(*) > (select count(*)
                    from emp17
                    where telecom = 'lg');

문제 406. (복습)직업, 직업별 최대월급, 직업별 최소월급, 직업별 토탈월급, 직업별 인원수를 출력하시오

서브 쿼리 사용하기 6(FROM절의 서브 쿼리)

select 문의 6가지절에서 서브쿼리를 쓸 수 있는 절은?
select (사용가능) (scalar subquery)
from (사용가능) (in line view)
where (사용가능)
group by (X)
having (사용가능) (scalar subquery)
order by (사용가능) (scalar subquery)

scalar subquery : 다른절에도 확장해서 쓸 수 있다.

문제 407. (복습) 이름, 월급, 월급에대한 순위를 출력

select ename, sal, dense_rank() over(order by sal desc) as 순위
 from emp;

문제 408. 위 결과에서 순위가 3, 5등 출력

 select ename, sal, 순위
 from (
        select ename, sal, dense_rank() over(order by sal desc) as 순위
        from emp
 ) 
  where 순위 in (3,5);  
위와같이 from절 안에 넣어서 먼저 실행 시키고, 순위를 밖쪾에 쓸 수 있도록
select ename, sal, dense_rank() over(order by sal desc) as 순위
 from emp
 where 순위 in(3,5);
위가 안되는 이유는, 실행 순서가 from -> where -> select라서 순위라는 별칭자체를 데려올 수 없다.
-> 이와같은 이유로 from 절의 서브쿼리를 써야 결과를 볼 수 있다.

문제 409. 직업, 이름, 월급, 순위를 출력하는데 순위가 직업별로 각각 월급이 높은 순서대로 순위

select ename, job, sal,  dense_rank() over( partition by job
                                            order by sal desc) as 순위
 from emp;

문제 410. 위 결과를 다시 출력하는데, 순위가 1등인 사원들만 출력

 select *
   from  (
            select ename, job, sal,  
            dense_rank() over( partition by job
            order by sal desc) as 순위
            from emp
 )
  where 순위 = 1;

문제 411. 사원 테이블 평균월급 출력 (복습)

select avg(sal)
  from emp;

문제 412. 사원테이블 이름, 월급, 평균월급 출력

select ename, sal, avg(sal) over () 평균월급
  from emp
  group by(ename, sal);

문제 413. 위 결과를 다시 출력하는데 본인 월급이 사원 테이블의 평균 월급보다 더 큰사람

 select  ename, sal, 평균월급
from   (
         select ename, sal, avg(sal) over () 평균월급
         from emp
)
where sal > 평균월급;

문제 414. 부서번호, 부서번호별 평균월급 출력!

select deptno, avg(sal)
 from emp
 group by deptno;

문제 415. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균 월급 출력

select ename, sal, avg(sal) over(partition by deptno) 부서평균
 from emp;

문제 416. 위 결과 다시 출력하는데, 자기의 월급이 자기의 부서번호 평균 월급보다 더 큰사원들만 출력

select ename, sal, 부서평균
     from ( 
            select ename, sal, avg(sal) over(partition by deptno) 부서평균
            from emp)
            where sal > 부서평균;

문제 417. 위 결과 보기 위해서 다른 튜닝전 SQL 출력

 select e.deptno, e.ename, e.sal, v.부서평균
     from emp e, ( select  deptno,  avg(sal)  부서평균
                                     from   emp
                                     group   by  deptno  ) v
    where e.deptno = v.deptno and e.sal > v.부서평균 ;

서브 쿼리 사용하기 7(SELECT절의 서브 쿼리)

문제 418. 토탈월급 출력

select sum(sal)
 from emp;

문제 419. 이름, 월급, 토탈월급

select ename, sal, sum(sal)
 from emp
이렇게만 쓰면 에러가 난다.
over() 를 써야하는데, over는 확장하는 것. ()만 쓰면 그냥 토탈월급만 출력
select ename, sal, sum(sal) over() as 토탈값
 from emp;

문제 420. 이름, 월급, 토탈월급을 출력하는데 select 절의 서브쿼리인 스칼라 서브쿼리를 이용해서 출력

select ename, sal, (select sum(sal) from emp) as 토탈값
  from emp;

※ select 절의 서브쿼리인 스칼라 서브쿼리는 emp 테이블의 건수만큼 반복되며 수행된다.
만약 where 절을 넣으면 그에 맞는 조건만큼 반복한다.

아래 2개의 sql의 버퍼 갯수를 각각 확인하기

1. 튜닝 전
select /*+ gather_plan_statistics */ename, sal, (select sum(sal) from emp) as 토탈값
  from emp;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')); 
2. 튜닝 후
select /*+ gather_plan_statistics */ ename, sal, sum(sal) over() as 토탈값
 from emp;

SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));  
1번이 악성 SQL이다. 스칼라 서브쿼리 안 SELECT절을 갯수만큼 반복하니까.
그럼 느린데 왜 쓸까? 쓸 수 밖에 없는 경우들이 있다.

문제 421. 직업이 salesman인 사원들의 토탈월급 출력

 select sum(sal)
  from emp
  where job = 'SALESMAN';

문제 이름, 월급, 직업이 SALESMAN인 사원들의 토탈월급 출력

select ename, sal, (select sum(sal)
                    from emp
                    where job = 'SALESMAN' ) as "SALES토탈"
 from emp;
이 코드는 튜닝을 할 수 없다. WHERE절이 있어서 그런가?
아래 코드는 튜닝후 코드인데 여기 아래코드에서 where 절을 쓸 자리가 없다.
select ename, sal, sum(sal) over() as 토탈값
 from emp;
-----------------------------------------
 select ename, sal, sum(sal) over() as 토탈값
 from emp
 where job = 'SALESMAN'; // 이거 안됨

문제 423. 이름, 월급, 직업이 salesman인 사원들의 토탈월급, 최대월급, 최소월급을 출력

select ename, sal, job,(select sum(sal)
                           from emp
                           where job = 'SALESMAN' ) as "SALES토탈",
                    (select max(sal)
                           from emp
                           where job = 'SALESMAN' ) as "SALES최대",
                    (select min(sal)
                           from emp
                           where job = 'SALESMAN' ) as "SALES최소"         
from emp;

위의 문제점은 emp테이블의 건수만큼 스칼라 서브쿼리의 select 문장이 반복된다는 것!
그런데 emp 테이블을 각각 3번을 엑세스 하고있다는 것이 더 문제!

 select sum(sal) 토탈 , max(sal) 최대, min(sal) 최소
  from emp
  where job = 'SALESMAN';

위 코드로 토탈, 최대, 최소를 뽑고 저 코드 그대로

select ename, sal, ( select sum(sal) , max(sal), min(sal) 
                       from emp
                      where job = 'SALESMAN')  
from emp;

 select ename, sal, ( select sum(sal) || max(sal) || min(sal) 
                       from emp
                      where job = 'SALESMAN')  
from emp;

위처럼 이어주면 출력된ㄷㅏ !!

이렇게 넣으면 에러가 난다. 왜!?
★스칼라 서브쿼리의 특징임. 스칼라 서브쿼리는 하나의 값만 출력할 수 있다 !!!!★

문제 424. 이름, 성별, 나이, 남자나이 평균값, 여자나이 평균값을 출력

select ename, gender, age , (select avg(age) from emp17 where gender = '남') 남자평균,
                             (select avg(age) from emp17 where gender = '여') 여자평균
from emp17;

문제 425. 이름, 주소, 나이, 서울에서 사는 학생들의 평균나이, 서울이 아닌 곳에서 사는 학생들의 평균 나이 출력

select ename, address, age,
       ( select avg(age) from emp17 where address like '%서울%') "서울평균",
       ( select avg(age) from emp17 where address not like '%서울%') "서울 외 평균"
from emp17;

문제 426. (스칼라 서브쿼리 가장 많이 쓰는 경우-약간 어려움) 이름, 월급, 부서위치를 출력

join 하지 않고 아래처럼 출력가능
select ename, sal, ( select loc
                     from dept d
                     where e.deptno = d.deptno) 부서위치
from emp e;
스캇 월급 3000 뽑고 웨어절에서 스캇의 부서번호 뽑고 그 부서번호에 해당하는 부서위치를 출력한다.
아까는 그냥 독단적인데 위 경우는 메인테이블의 컬럼이 (emp table이 where절에 e.deptno 에 들어가서 데이터 검색하고 출력한다.)

이경우 굳이 이렇게 스칼라 서브쿼리로 안하고 join이 낫다 !

문제 427. 부서번호, 이름, 월급, 자기가 속한 부서번호의 평균월급을 출력(스칼라 서브쿼리 사용하기)

select deptno, ename, sal, ( select avg(sal)
                              from emp s
                              where e.deptno = s.deptno) 부서평균
from emp e;

실행순서가 가장 밖에 있는 from절이 먼저

문제 428. 통신사, 이름, 나이, 자기가 속한 통신사의 평균 나이 출력

튜닝 전(악성)

select telecom, ename, age, ( select avg(age)
                              from emp17 s
                              where s.telecom = e.telecom) 통신사평균나이
from emp17 e;

문제 429. 위 SQL을 스칼라 서브쿼리 이용하지 말고 데이터 분석 함수로 수행(튜닝) over()

select telecom, ename, age, avg(age) over()
  from emp17;
위 코드는 우리반 전체의 평균나이가 나온다. 심지어 over ( ) 안써주면 에러남.

우리는 데이터를 이걸 원한게 아니라 통신사별로 출력하고 싶던 거! -> partition by
★ 튜닝 후 답 !!!!!!! ★
select telecom, ename, age, avg(age) over(partition by telecom)
  from emp17;
쉽게 생각하자면.. over () 를 썼다면 그룹바이를 쓸 생각을 하지말자!

set autot on // 얘를 먼저 프롬프트에 입력하고 
select telecom, ename, age, ( select avg(age)
                              from emp17 s
                              where s.telecom = e.telecom) 통신사평균나이
from emp17 e; // 궁금한 코드를 넣어보면 아래 이미지가 나옴

select telecom, ename, age, avg(age) over()
  from emp17; // 이 코드의 consistent gets는 아래 이미지임.

위 consistent gets는 12, 아래는 9

다음에 배움.. 아무튼 숫자 낮은게 좋다.

문제 430.(오늘 마지막 문제) 우리반 테이블에서 이름, 나이, 통신사, 성별, 자기가 속한 통신사의 평균나이, 자기의 성별의 평균나이 출력

select ename, age, telecom, gender,
        ( select  round(avg(age)) from emp17 s where s.telecom = e.telecom) 통신사평균나이,
        ( select round(avg(age)) from emp17 s where s.gender = e.gender) 성별평균나이
   from emp17 e;

궁금한 것 / 해결한 것

select ename, sal, sum(sal)
 from emp
 group by ename, sal; // 이건 이름, 월급별로 총 월급 (row느낌)
------------------------------------------------------------------------ 
select ename, sal, sum(sal) over() as 토탈값
 from emp; // 이건 각 컬럼들이 연관없음 sum(sal) over() 이거는 그냥 모든 사원의 월급 합계임

두개 다른점

 select ename, sal, sum(sal)
 from emp
 where job = 'SALESMAN'
 group by ename, sal; // 얘도 직업이 salesman인 사원들 중 이름, 월급별 총 월급 (row느낌)
------------------------------------------------------------------------ 
select ename, sal, (select sum(sal)
                    from emp
                    where job = 'SALESMAN' ) as "SALES토탈" 
                    // 모든 사원들중 직업이 salesman인 사원들의 월급 총합계!
 from emp;

두개 다른점

아래 sql을 from 절 서브쿼리에 넣어서 쓸 수 없는 이유

아래 첫 코드 튜닝 시도해보다가 궁금했던 것.

select sum(sal) 토탈 , max(sal) 최대, min(sal) 최소
  from emp
  where job = 'SALESMAN'
내가쓴 것
select ename, 토탈, 최대, 최소
  from (
          select ename ,  sum(sal) as 토탈, max(sal) as 최대, min(sal) as 최소
          from emp
          where job = 'SALESMAN'
          group by ename
);
from절 안에 코드가 먼저 실행이 되는데 제일 밖에 컬럼에 ename을 뽑고있다 .
이렇게는 애초에 에러가 나니까 , from절 안과 밖 컬럼을 맞추어주자. ename 출력하고 싶으면 가장 먼저 실행이 되는 from절 서브쿼리 안에 ename 컬럼을 적어주어야하는데, 이렇게 하고싶으면 from절 안에 group by를 써줘야한다. 이렇게 해서 코딩하면 출력되는 결과 자체가 다르기때문에 우리가 원하는 코드가 아니다.

위 데이터 아니고 아래처럼 뽑아야함. 결과 데이터값 자체가 다르다

select ename, sal, ( select '토탈: ' || sum(sal) || ', ' ||
'최대: ' || max(sal) || ', ' ||
'최소: ' || min(sal)
from emp
where job = 'SALESMAN') as "총, 최대, 최소월급"
from emp;

![](https://velog.velcdn.com/images/seonghae/post/42fd5fff-4e41-4df1-ad94-468f0bd16ae3/image.png)
* 스칼라 서브쿼리는 데이터를 하나만 출력할 수 있으니, `||`  연결연산자로 이어서 출력함

망구씨

Slow and steady wins the race.

이전 포스트