4) 외부(outer)조인

한쪽 테이블에는 해당하는 데이터가 존재하고 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 모든 
데이터를 조회하는 조인

- 조회 조건에서 (+)기호를 사용하는 조인.

- 데이터가 존재하지 않는 테이블이 조인 조건에 (+) 붙임

 **(주의) 테이블에 (+)를 붙이지 않음

- (+)가 붙은 컬럼과는 in 연산자를 함께 사용할 수 없음

- (+)가 붙은 컬럼과는 서브쿼리를 같이 사용할 수 없음

* 학생테이블과 교수테이블을 조인하여 학생이름과 지도교수 이름을 출력

(단, 지도교수가 배정되지 않은 학생의 명단도 함께 출력)

select s.name, p.name from student s, professor p

where s.profno =p.profno;

 

내부조인 , 일반조인=>   서진수    조인형 서재수    양선희 이미경    나한열 김재수    심슨 박동호    박원범 김신영    박승곤 신은경    김영조 오나라    박원범 구유미    허은 임세현    심슨 일지매    박승곤 김진욱    양선희 안광훈    최슬기 김문호    박원범 노정호    허은

 

select s.name, p.name from student s, professor p

where s.profno =p.profno(+);

서진수    조인형 일지매    박승곤 김신영    박승곤 김진욱    양선희 서재수    양선희 신은경    김영조 이미경    나한열 임세현    심슨 김재수    심슨 안광훈    최슬기 김문호    박원범 오나라    박원범 박동호    박원범 노정호    허은 구유미    허은 허우    null 김주현    null 인영민    null 안은수    null 이윤나    null

 

=>지도교수가 배정되지 않은 학생들도 출력

****  +  라 하는 곳이  null 값이 출력된다. 

-- outer join(외부조인) + selef join
select a.ename 사원 , b.ename 관리자
from emp a, emp b 
where a.mgr =b.empno(+);

 <==>

select a.ename 사원, b.ename 관리자

from emp a left outer JOIN emp b
  On  a.mgr =b.empno;

*** null 값도 출력될 수 있도록 오른쪽에 +  하여 right 외부조인이라 생각 할 수 있으나 

*** 이것이 left 조인이다.  출력할 null값이 왼쪽에 붙은다 해서 left 조인 이다.

 

=> 출력 결과

송기성    임채호 박민성    박지성 성명준    박지성 나대호    박지성 박종수    박지성 이찬수    박지성 최철호    구자철 황인태    송기성 구자철    김철호 박지성    김철호 임채호    김철호 김철수    박진성 김철호    null

 

 

--  레코드가 없는 테이블의 조인조건에 (+)를 붙임

select d.dname, count(*), sum(sal), avg(sal) from  emp e, DEPT d

where e.deptno(+)=d.deptno group by d.dname order by dname;

 

사원테이블에는 전산팀이 없으나 부서테이블에는 전산팀이 존재한다.

따라서 전산팀을 출력하고 싶을때 외부조인을 해야 한다. 이때, 없는 테이블에

+ 를 하면 된다. 위 조인은 right 조인으로 오른쪽에 부터테이블로 조인하므로서

전산팀을 볼 수 있다.

=> 출력

경리팀    3    1475    491.666666666666666666666666666666666667
연구팀    5    2007    401.4
전산팀    1        
총무팀    6    2040    340

-- 

-- 지도교수가 배정되지 않은 5명도  출력됨 (외부조인, outer join)

select s.name, p.name
from student s, professor p 
where s.PROFNO =p.PROFNO(+);

 

출력 =>

서진수    조인형
일지매    박승곤
김신영    박승곤
김진욱    양선희
서재수    양선희
신은경    김영조
이미경    나한열
임세현    심슨
김재수    심슨
안광훈    최슬기
김문호    박원범
오나라    박원범
박동호    박원범
노정호    허은
구유미    허은
허우    null
김주현    null
인영민    null
안은수    null

-- 내부조인(innser join), 5명이 제외됨

select s.name, p.name from STUDENT s
 join PROFESSOR p on s.PROFNO = p.PROFNO;

-- left outer join (왼쪽 테이블이 모두 출력 )
 

select s.name, p.name
from student s left outer join PROFESSOR p
  on s.PROFNO =p.PROFNO;

 

=> 출력 서진수    조인형 일지매    박승곤 김신영    박승곤 김진욱    양선희 서재수    양선희 신은경    김영조 이미경    나한열 임세현    심슨 김재수    심슨 안광훈    최슬기 김문호    박원범 오나라    박원범 박동호    박원범 노정호    허은 구유미    허은 허우    null 김주현    null 인영민    null

 

-- right outer join (오른쪽 테이블이 모두 출력 ) 

​​​​​​​
select s.name, p.name
from student s right outer join PROFESSOR p
  on s.PROFNO =p.PROFNO;
  

=>출력

서진수    조인형
서재수    양선희
이미경    나한열
김재수    심슨
박동호    박원범
김신영    박승곤
신은경    김영조
오나라    박원범
구유미    허은
임세현    심슨
일지매    박승곤
김진욱    양선희
안광훈    최슬기
김문호    박원범
노정호    허은
null    김도형
null    전민
null    주승재
null    바비
null    김현정
null    송도권

-- full outer join (양쪽 테이블이 모두 출력 )  

select s.name, p.name
from student s full outer join PROFESSOR p
  on s.PROFNO =p.PROFNO;  
  

=> full  (양쪽에 + 를 붙이면 에러 union으로 합집합 사용)

select s.name, p.name
from student s, professor p 
where s.PROFNO(+) =p.PROFNO
union
select s.name, p.name
from student s, professor p 
where s.PROFNO =p.PROFNO(+);

 

 

5) ANSI 조인 : 새로운 국제 표준에 따른 조인, oracle 9i부터 지원

가. 내부조인 : innser join 사용

 - where 절 대신 on 사용

 select e. empno, d.dname from emp

    e innser join dept d

    on e.deptno = d.deptno;

 

- where절 대신 using 사용(참조하는 컬럼명이 동일한 경우에만 사용가능)

 

select e.empno, d.dname

 from emp e inner join dept d using(deptno);

 

나. 외부조인 : [left|right|full] outer join 사용

- 데이터가 있는 테이블편을 기준으로 left 또는 right를 붙임

 

 select s.name, s.deptno 1, p.name

 from student s full outer join professor p

  on s.profno =p.profno

 

<<실습문제>>

-- 1. emp 와 dep 테이블을 조인하여 부서번호, 부서명, 이름, 급여를 출력하시오.
-- 오라클 방식

 

select e.deptno, dname, ename, sal from emp e, dept d

where e.DEPTNO =d.DEPTNO;

-- ANSI 방식

select e.deptno, d.DNAME, e.ENAME, sal
 from emp e  inner  join dept d
 on e.deptno=d.DEPTNO;

 

-- 2. 직업이 '사원' 인 사원의 이름, 부서번호, 부서이름을 출력하시오.

select ename , e.deptno, dname from emp e, dept d where e.deptno = d.deptno and job='사원';
 
select ename , e.deptno, dname from emp e inner join  dept d on e.deptno = d.deptno where  job='사원';

 

-- 3. 이름이 '황인태' 인 사원의 부서명을 출력하시오

--오라클

select ename, dname from emp e, dept d where e.deptno= d.DEPTNO and ename ='황인태';

 

-- Ansi // and 대신에 원래 where  넣어야 하지만 and 된다.

 

select ename, dname from emp e join  dept d on e.deptno= d.DEPTNO and ename ='황인태';

 

 

 

-- 4. emp 테이블에 있는 empno, mgr을 이용하여 서로의 관계를 다음과 같이 출력하시오.

-- "박지선의 매니저는 임채호이다"

select 사원.ename || '의 매니저는 ' || 관리자.ename || '이다' from emp 사원, emp 관리자

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

select 사원.ename || '의 매니저는 ' || 관리자.ename || '이다' from emp 사원 inner join emp 관리자

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