Oracle/ 자기참조조인 & 외부조인

<자기 참조 조인(Self Join)>

 

SELECT 컬럼, ...
FROM 테이블 별명1, 테이블 별명2
WHERE 조인_조건
AND 일반_조건

 

• 동일 테이블을 자기 참조에 의해 조인한다
• 별명1과 별명2는 같은 테이블이지만, 다른 이름을 부여함으로써 서로 다른 테이블을 조인하는 효과를 부여한다.
• 테이블에 반드시 별명을 붙여야 한다. (별명을 이용한 조인이라고 부르기도 함)

 

SELECT *
FROM emp;

--각 사원을 관리하는 관리자(사수)의 이름을 검색한다
--e1은 사원 테이블
--e2는 관리자 테이블
SELECT e1.eno 사번, e1.ename 사원, e1.mgr 관리자사번,
       e2.eno 관리자사번, e2.ename 관리자
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno;

--부서간은 소속관계등에 많이 쓰임

 

 

 

 

 

 

<외부조인>

 

SELECT 테이블1.컬럼, ... , 테이블2.컬럼, ...
FROM 테이블1, 테이블2, ...
WHERE 조인_조건(+)
AND 일반_조건;

 

• 조인조건에 일치하지 않는 데이터를 출력해준다.
• 테이블 한쪽이 정보가 부실할 때 +기호를 써준다. 
  
  ex) 신규부서가 만들어져서 부서원이 없을 때, 신입사원이 아직 부서배정을 받지 않았을 때
  -> 등가조인을 하게되면 부족한 쪽의 정보가 누락된다.

 

--각부서별 사원 검색
SELECT d.dno, dname, e.eno, ename
FROM dept d, emp e
WHERE d.dno=e.dno
ORDER BY 1;

SELECT d.dno, dname, e.eno, ename
FROM dept d, emp e
WHERE d.dno=e.dno(+)
ORDER BY 1;

 

 

 

 

 

 

<Exercise>

-- 1.학생중 동명이인 검색
SELECT DISTINCT st1.sno, st1.sname
FROM student st1, student st2
WHERE st1.sname = st2.sname
AND st1.sno!=st2.sno;

--2. 전체 교수명단과 교수가 담당하는 과목의 이름을 학과순으로 검색한다.
SELECT section, p.pno, pname, cname
FROM professor p, course co
WHERE p.pno=co.pno(+)
ORDER BY section DESC;

--3. 모든 과목과 담당교수를 학점순으로 검색한다.
SELECT section, cname, p.pno, pname, st_num 
FROM professor p, course co
WHERE p.pno=co.pno(+)
ORDER BY st_num DESC;