Oracle 05. Join & SubQuery

Github: https://github.com/SHIN96BC/KOSMO-2021-2022-Oracle/tree/main/3_SQL3

 

(1) 조인( Join )
     1) 설명 
          <1> 하나의 테이블로는 원하는 컬럼정보를 가져올 수 없는 경우, 
                 관련된 테이블을 '논리적으로 결합'하여 원하는 컬럼정보를 가져오는 방법이다.

     2) 조건 
          <1> 논리적으로 결합되는 2개 이상의 테이블에는 반드시 '공통컬럼'이 존재해야하며 
                 이 공통컬럼은 동일한 데이터 타입과 공통 데이터를 의미해야 한다.

     3) 사원번호와 부서이름을 출력 
          ex) select EMP.EMPNO, DEPT.DNAME from EMP, DEPT where EMP.DEPTNO=DEPT.DEPTNO; --형태1
          ex) select e.EMPNO, d.DNAME from EMP e, DEPT d where e.DEPTNO=d.DEPTNO; --형태2
          ex) select e.EMPNO, d.DNAME from EMP e join DEPT d on e.DEPTNO=d.DEPTNO; --형태3
          ex) select e.EMPNO, d.DNAME from EMP e join DEPT d using(DEPTNO); --형태4
          ex) select EMPNO, DNAME from EMP join DEPT using(DEPTNO); --형태5
          ex) select EMPNO, DNAME from EMP natural join DEPT; --형태6

     4) 종류
          <1> Cross 조인 
               1> 2개 이상의 테이블이 조인될 때 'where절'에 의해 공통되는 컬럼에 의한 결합이 
                    '발생하지 않는 경우' 즉, 테이블 전체행의 전체컬럼이 조인에 사용되는
                    조인을 말한다 따라서, 모든 데이터가 검색결과가 된다.  

               ex) select e.EMPNO, e.ENAME, d.DNAME from EMP e, DEPT d; --where조건이 없는 경우
               ex) select e.EMPNO, e.ENAME, s.GRADE from EMP e, SALGRADE s;--공통컬럼이 없는 경우
               ex) select EMPNO, ENAME, GRADE from EMP, SALGRADE;--공통컬럼이 없는 경우

          <2> Natural 조인 ( Equi 조인 )

               1> 가장 일반적

               2> where 절이 사용된 '공통컬럼'들이 동등 연산자(=)에 의해 비교되는 조인 

               3> 사원번호와 부서이름을 출력 ( 단, 30번 부서만 )
                    ex) select EMP.EMPNO, DEPT.DNAME from EMP, DEPT 
                         where EMP.DEPTNO=DEPT.DEPTNO and DEPT.DEPTNO=30;  --형태1
                    ex) select e.EMPNO, d.DNAME from EMP e, DEPT d 
                         where e.DEPTNO=d.DEPTNO and e.DEPTNO=30; --형태2
                    ex) select e.EMPNO, d.DNAME from EMP e join DEPT d 
                         on e.DEPTNO=d.DEPTNO
                         where e.DEPTNO=30; --형태3
                    ex) select e.EMPNO, d.DNAME from EMP e join DEPT d 
                         using(DEPTNO)
                         where DEPTNO=30; --형태4
                    ex) select EMPNO, DNAME from EMP join DEPT 
                         using(DEPTNO)
                         where DEPTNO=30; --형태5
                    ex) select EMPNO, DNAME from EMP natural join DEPT
                         where DEPTNO=30; --형태6
          
          <3> Self 조인
               1> 가져와야 할 컬럼이 '자신의 테이블에 있는 다른 컬럼'인 경우의 조인

 

               2> SMITH의 매니져는 FORD이다

               3> 형태1
                    ex) select e.ENAME, m.ENAME from EMP e, EMP m where e.MGR=m.EMPNO; --뼈대
                    ex) select e.ENAME||'의 매니져는 ', m.ENAME||'이다' from EMP e, EMP m where e.MGR=m.EMPNO; 
                    ex) select e.ENAME||'의 매니져는 ', m.ENAME||'이다' from EMP e, EMP m 
                         where e.MGR=m.EMPNO and e.ENAME='SMITH';
              
               4> 형태2
                    ex) select e.ENAME, m.ENAME from EMP e join EMP m on e.MGR=m.EMPNO; --뼈대
                    ex) select e.ENAME, m.ENAME from EMP e join EMP m on e.MGR=m.EMPNO
                         where e.ENAME='SMITH';
     

          <4> Outer 조인
               1> 한쪽 테이블에는 해당하는 데이터가 존재하고, 
                   다른 쪽 테이블에는 데이터가 존재하지 않을 경우에 '기준 테이블'을 이용한 조인 

               2> Left 
                    ex) select T1.NO, T1.NAME, T2.NO, T2.NAME 
                         from T1 left outer join T2
                         on T1.NO = T2.NO; 

               3> Right 
                    ex) select T1.NO, T1.NAME, T2.NO, T2.NAME 
                         from T1 right outer join T2
                         on T1.NO = T2.NO; 

               4> Full 
                    ex) select T1.NO, T1.NAME, T2.NO, T2.NAME 
                         from T1 full outer join T2
                         on T1.NO = T2.NO;
     

          <5> Inner 조인
               1> 일반화 : 배치와 실행 순서
                    1. select XX   --------> 6
                    2. from XX     --------> 1 
                    3. (join XX)   --------> 2
                    4. where XX    --------> 3 
                    5. group by XX --------> 4 
                    6. having XX   --------> 5
                    7. order by XX --------> 7 

 

(2) SubQuery
     1) 사원번호가 7900인 사원의 부서이름 출력( 구조 )
          ex) Sub쿼리: select DEPTNO from EMP where EMPNO=7900;
          ex) Main쿼리: select DNAME from DEPT where DEPTNO=30;
          ex) 결합: select DNAME from DEPT where DEPTNO=(select DEPTNO from EMP where EMPNO=7900);

     2) 설명 
          <1> 하나의 select 문장에 포함된 또 다른 select 문장 
                ( 두번 이상 질의를 해야 얻을 수 있는 결과를 한번의 질의로 통합한 쿼리 )

     2) 용어 
          <1> Main-Query or Outer-Query 
          <2> Sub-Query or Inner-Query 
  
     3) 특징 
          <1> 괄호로 묶어야 함 
          <2> 실행순서는 '대부분' Sub-Query 가 먼저 수행되고, Main-Query가 수행됨 
          <3> SUB-QUERY는 MAIN-QUERY의 다음 부분에 위치된다.
               1> select/delete/update 문장의 from 절 / where 절 
               2> insert 문장의 into 절 
               3> update 문장의 set 절 
          <4> SUB-QUERY는 order by 절을 사용할 수 없다.
               (예외 : select/delete/update 문장의 from 절 )

 

     4) 종류
          <1> 단일행 Sub-Query
               1> 실행결과가 '하나의 데이터'만 리턴해주는 쿼리

               2> 사원번호가 7900인 사원의 부서이름 출력
                    ex) Sub쿼리: select DEPTNO from EMP where EMPNO=7900;
                    ex) Main쿼리: select DNAME from DEPT where DEPTNO=30;
                    ex) 결합: select DNAME from DEPT where DEPTNO=(select DEPTNO from EMP where EMPNO=7900);

               3> 부서번호가 10번인 사원급여와 급여가 같은 사원의 이름과 커미션을 출력( 복수행:X )
                    ex) Sub쿼리: select SAL from EMP where DEPTNO=10;
                    ex) Main쿼리: select ENAME, COMM from EMP where SAL=?
                    ex) Err: select ENAME, COMM from EMP where SAL=(select SAL from EMP where DEPTNO=10);

               4> 사번이 7369인 사원의 급여와 커미션이 같은.. 사원의 이름과 커미션을 출력( 복수컬럼: X ) 
                    ex) Sub쿼리: select SAL, COMM from EMP where EMPNO=7369;
                    ex) Main쿼리: select ENAME, COMM from EMP where SAL=?
                    ex) Err: select ENAME, COMM from EMP

                              where SAL=(select SAL, COMM from EMP where EMPNO=7369);

               5> 평균급여보다 많은 받는 사원의 이름과 급여 출력!

                    ex) select ENAME, SAL from EMP where SAL>(select avg(SAL) from EMP);
                    ex) Err: select ENAME, SAL from EMP where SAL>avg(SAL);
                   

          <2> 복수행 Sub-Query
               1> 실행결과가 '둘 이상의 데이터행'을 리턴해주는 쿼리

               2> in
                    1. 부서번호가 10번인 사원급여와 급여가 같은 사원의 이름과 급여를 출력
                         ex) Sub쿼리: select SAL from EMP where DEPTNO=10;
                         ex) Main쿼리: select ENAME, SAL from EMP where SAL=?
                         ex) Err: select ENAME, SAL from EMP where SAL=(select SAL from EMP where DEPTNO=10);

                         ex) select ENAME, SAL from EMP where SAL in (select SAL from EMP where DEPTNO=10);
                         ex) select ENAME, SAL from EMP where SAL=any(select SAL from EMP where DEPTNO=10);
                         ex) 효과: select ENAME, SAL from EMP where SAL=2450 or SAL=5000 or SAL=1300;

               2> all 
                    1. (급여가 가장 많은) 'MANAGER'보다 급여가 같거나 많은 사원의 JOB,SAL을 출력!!
                         ex) Sub쿼리: select SAL from EMP where JOB='MANAGER';
                         ex) Main쿼리: select JOB,SAL from EMP where SAL>=?
                         ex) Err> select JOB, SAL from EMP where SAL>=(select SAL from EMP where JOB='MANAGER');
                         ex) 효과> select JOB, SAL from EMP where SAL>=2975 and SAL>=2850 and SAL>=2450;
                         ex) select JOB, SAL from EMP where SAL>=all(select SAL from EMP where JOB='MANAGER');

               3> all 과 any 차이
                    1. (급여가 가장 큰) 'SALESMAN' 보다 급여가 작은 사원의 JOB, SAL을 출력!
                         ex) Sub쿼리: select SAL from EMP where JOB='SALESMAN';
                         ex) Main쿼리: select JOB, SAL from EMP where SAL<?;
                         ex) select JOB, SAL from EMP where SAL<all(select SAL from EMP where JOB='SALESMAN');
                         ex) select JOB, SAL from EMP where SAL<any(select SAL from EMP where JOB='SALESMAN');
               
               4> exists
                    1. '부서번호'가 10인 사원이 존재하면 모든 부서의 이름을 출력! 
                         ex) Sub쿼리: select * from EMP where DEPTNO=10;
                         ex) Main쿼리: select DNAME from DEPT;
                         ex) select DNAME from DEPT where exists(select * from EMP where DEPTNO=10);

 

          <3> 복수컬럼 Sub-Query
               1> 실행결과가 '둘 이상의 컬럼' 데이터를 리턴주는 쿼리 

               2> '부서번호'가 30인 사원의 (SAL과 COMM)이 같은 사원들의 '이름'과 '부서번호' 출력!
                    ex) Sub쿼리: select SAL, COMM from EMP where DEPTNO=30;
                    ex) Main쿼리: select ENAME, DEPTNO from EMP where (SAL, COMM) in ? 
                    ex) select ENAME, DEPTNO from EMP where (SAL, COMM) 
                         in (select SAL, COMM from EMP where DEPTNO=30); -- 4개 
                    ex) 비교: select ENAME, DEPTNO from EMP where (SAL, nvl(COMM,0)) 
                                 in (select SAL, nvl(COMM,0) from EMP where DEPTNO=30); -- 6개 

          <4> 상호관련 Sub-Query
               1> Main-Query 절에 사용된 테이블이 Sub-Query절에 다시 재사용되는 쿼리
                    ex) Sub쿼리: select avg(e1.SAL) from EMP e1, EMP e2
                                     where e1.DEPTNO=e2.DEPTNO;
                    ex) Sub쿼리 Err: select avg(SAL) from EMP e2
                                          where e1.DEPTNO=e2.DEPTNO;
                    ex) Main쿼리: select e1.EMPNO, e1.SAL from EMP e1
                                       where SAL>(select avg(SAL) from EMP e2 where e1.DEPTNO=e2.DEPTNO);