데이터 베이스 기초4(자기참조 조인, 외부조인)

자기 참조 조인(Self join)과 외부조인(Outer join)

조인 문을 생성하는 과정
step 1. 지문에서 검색 대상과 조건을 구분한다.
 - 이를 통해 일단 SELECT 절, WHERE 절, ORDER BY 절을 구성한다.
 - SELECT 절은 step 5에서 작성자의 의도에 따라 추가될 수 있다. 

step 2. SELECT 절과 WHERE 절의 내용에 따라 정보를 검색할 테이블을 찾는다. 

step 3. 테이블간의 관계를 확인한다.
 - 관계를 확인하는 단계에서 테이블이 추가될 수 있다. 

step 4. 조인 조건을 기술한다. 

step 5. 전체 문장을 다듬는다. 

 

자기 참조 조인 (Self Join)
SQL> SELECT 별명1.컬럼1, ... 별명2.컬럼1, ...
 2 FROM 테이블 별명1, 테이블 별명2, ...
 3 WHERE 조인_조건
 4 AND 일반_조건

 

예제 2. 각 사원을 관리하는 사수의 이름을 검색한다. 

SQL> SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
 2 FROM emp e1, emp e2

 3 WHERE e1.mgr = e2.eno;

 

ENO ENAME MGR ENO ENAME
---- ---------- ---- ---- ------
2008 윤고은 0001 0001 안영희
2007 이초록 0001 0001 안영희
0301 이승철 0001 0001 안영희
.............
............. 

 

외부 조인(Outer join)
SQL> SELECT 테이블1.컬럼, ..테이블2.컬럼, ...
 2 FROM 테이블1, 테이블2, ...
 3 WHERE 조인_조건(+)
 4 AND 일반_조건

 

예제 3 각 부서별로 사원를 검색한다. (외부 조인과 일반 조인 결과물의 비교)
SQL> SELECT d.dno 부서번호, dname 부서명, ename 사원명
 2 FROM dept d, emp e 
 3 WHERE d.dno = e.dno 
 4 ORDER BY 1;
 

부서번호 부서명 사원명
-------- ---------- ---------
01 총무 안영숙
01 총무 안영희
01 총무 손하늘
.............
............. 

 

SQL> SELECT d.dno 부서번호, dname 부서명, ename 사원명
 2 FROM dept d, emp e 
 3 WHERE d.dno = e.dno(+) 

4 ORDER BY 1;

 

부서번호 부서명 사원명
-------- ---------- --------
01 총무 안영희
01 총무 안영숙
01 총무 손하늘
.............
.............

 

ed + enter - 방금 적은 문장을 수정가능

 

실습
1. 학생 중에 동명이인을 검색한다.

    SELECT DISTINCT S1.SNO 학번, S1.SNAME 이름 

 2 FROM STUDENT S1. STUDENT S2

 3 WHERE S1.SNO != S2.SNO AND S1.SNAME = S2.SNAME

 4 ORDER BY S1.SNAME;

 

2. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색한다.

     SELECT P.PNAME 교수이름, P.SECTION 교수담당과목, C.CNAME 과목이름 

  2 FROM PROFESSOR P, COURSE C

  3 WHERE P.PNO = C.PNO(+) ORDER BY P.SECTION;

 

3. 이번 학기 등록된 모든 과목과 담당 교수를 학점 순으로 검색한다.

    SELECT C.CNAME 과목이름, P.PNAME 교수이름, C.ST_NUM 학점

 2 FROM COURSE C, PROFESSOR P

 3 WHERE AND P.PNO(+) = C.PNO 

 4 ORDER BY C.ST_NUM;

 

4. 직원 중에 자신의 관리자 보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색한다.

    SELECT E1.ENO 사원번호, E.ENAME 사원이름, E1.MGR 관리자번호,

 2 E1.SAL 급여, E2.ENO 사번, E2.ENAME 이름, E2.SAL 급여

 3 FROM EMP E1, EMP E2

 4 WHERE E1.MGR = E2.ENO AND E1.SAL < E2.SAL;

 

5. 교수의 정보와 교수가 담당하는 과목명을 검색한다.

    SELECT P.PNO 교수번호, P.PNAME 교수이름, P.SECTION 교수담당과목,

 2 P.ORDERS 직위, P.HIREDATE 부임일, C.CNAME 과목이름

 3 FROM PROFESSOR P, COURSE C

 4 WHERE P.PNO = C.PNO(+);

 

6. 직원과 사수의 명단을 검색한다. 단 직원 명단은 모든 직원 명단이 출력되어야 한다.

   SELECT E1.ENO 사원번호, E1.ENAME 사원이름, E1.MGR 관리자번호,

2 E2.ENO 사원번호, E2.ENAME 사원이름

3 FROM EMP E1, EMP E2

4 WHERE E1.MGR = E2.ENO(+);

 

7. 화학과 학생 중에 학점이 동일한 학생을 검색한다.

   SELECT S1.SNO 학번, S1.SNAME 이름, S1.MAJOR 학과, S1.AVR 평점
2 FROM STUDENT S1, STUDENT S2
3 WHERE S1.SNO != S2.SNO
4 AND S1.AVR = S2.AVR
5 AND S1.MAJOR = S2.MAJOR
6 AND S1.MAJOR = '화학';