SQL-16) exist , with , 계층형 질의 : SQl, SQLD 자격증, ADsP, 함수

안녕하세요 이승혁입니다.

오늘 포스팅은 SQL의 exists , with , 계층형 질의문에 대해 실습을 진행해 보도록 하겠습니다.

 

ㆍ exist 문

 

   A 테이블에 존재하는 데이터가 B 테이블에도 존재하는지 확인할 때 사용하는 문법입니다.

 

 

실습 1. dept 테이블에서 부서번호와 부서위치를 출력하는데 emp 테이블에 존재하는 부서번호에 대한 것만 출력해보기

select deptno, loc
from dept d
where exists( select * 
               from emp e 
               where e.deptno = d.deptno);

※ exist 문 특징

 

1. 서브쿼리문과는 다르게 테이블 별칭이 사용되고 있다.

2. 메인쿼리의 exists 앞에 컬럼명이 없다.

3. 메인쿼리의 컬럼이 서브쿼리문 안에 들어가게 되면 반드시 메인쿼리부터 실행하게 된다.

   -> 자신의 값을 먼저 확인한 후 , 그 값을 기준으로  서브쿼리 내에서 비교하기 때문입니다.

           [ 메인쿼리 데이터 양 < 서브쿼리 데이터 양 ] 속도적 측면에서 효율적 !

           서브쿼리의 조건을 서브쿼리 테이블의 위에서 부터 확인해 하나라도 존재한다면 메인쿼리로

           넘어가기 때문입니다.

4. exist 문은 메인쿼리의 데이터를 하나씩 서브쿼리문의 테이블에서 하나씩 검색하는데 존재하면 바로 스캔을 멈추고

    바로 출력을 해준다.

    서브쿼리문의 테이블을 매번 전체를 다 스캔하지 않아도 되는 장점이 있어서 검색 속도를 높일 수 있다.

    단, 메인쿼리의 테이블의 크기가 서브쿼리보다 작아야 효과를 볼 수 있다.

 

실습 2 not exists. 관리자가 아닌 사원들의 이름을 출력해보기

select m.ename
from emp m
where not exists( select *
                  from emp e 
                  where e.mgr = m.empno); -- e 테이블 매니저번호 = m 테이블 사원번호
                                          -- 즉, 매니저인 사람들을 모두 출력하는 서브쿼리

---

ㆍ with 절

 

     복잡한 쿼리내에 동일 쿼리 블럭이 두 번 이상 발생하는 경우에 사용하면 좋은 SQL

     테스트 테이블과 같이 임시로 사용하는 데이터를 가지고 테스트할 때 유용한 SQL

 

실습 2. with 절 사용해보기, 10이하 짝수 출력해보기

with test_table as ( select level as num 
		      from dual
		      connect by level<=10)
select num
 from test_table
 where mod(num,2)=0;

※설명 : with절의 임시 테이블의 데이터는 with절이 끝나면 사라진다.

          with절의 임시 테이블의 데이터는 오라클의 temporary tablespace라는 임시

          저장소에 저장이 된다.

 

실습 3. with 절 사용해보기, 구구단 2단 출력해보기

with test_table as ( select level num
		      from dual
		      connect by level<=9)
              
select '2 x ' || num ||'='|| 2*num as "2단" 
 from test_table;

---

ㆍ 계층형 질의

    

   순위와 서열을 출력하는 SQL문

 

    계층형 쿼리절은 where 절 다음에기술하며 from절이 수행된 후 수행된다.

    < start with > , < connect by > 절로 구성되며

    start with 절이 수행된 후 connect by 절이 수행된다.

    start with 절은 생략이 가능하다.

select level, empno, ename, mgr
 from emp
 start with ename = 'KING'
 connect by prior empno = mgr;

 

emp 테이블의 조직도, 서열을 확인할 수 있다.

서열 결정 : empno , mgr

start with ename ='KING' 부터 시작

connect by 절 : 좌변을 기준으로 , 우변의 내용이 좌변에 속하면 하위 서열

 

level 1	level 2	level 3	level 4
KING	JONES BLAKE CLARK	SCOTT FORD ALLEN WARD MARTIN TURNER JAMES MILLER	ADAMS SMITH

 

※ 코드를 사용해서 시각화 해보기

select level,rpad(' ',level*2)||ename as employee, sal
 from emp
 start with ename = 'KING'
 connect by prior empno = mgr;

rpad 함수와 level 을 사용해 시각적으로 계급,소속을 확인할 수 있습니다