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);
'SQL > Oracle' 카테고리의 다른 글
Oracle 07. TCL ( Transaction Control Language ) (0) | 2022.03.31 |
---|---|
Oracle 06. DML ( Data Manipulation Language ) (0) | 2022.03.31 |
Oracle 04. DQL - 함수 ( Function ) (0) | 2022.03.25 |
Oracle 03. DQL - Base (0) | 2022.03.20 |
Oracle 02. 개요 (0) | 2022.03.20 |