자기 참조 조인과 외부 조인

자기 참조 조인

SELECT 별명1.컬럼1, ... 별명2.컬럼1, ... FROM 테이블 별명1, 테이블 별명2, ... WHERE 조인_조건 AND 일반_조건; 나 자신과의 대화라고 생각하면 쉽다. 보통은 A테이블과 B테이블을 합히지만, 자기 참조 조인은 하나의 테이블 안에서 서로 다른 행끼리 관계를 맺어주는 것 실습 하기  테이블은 하나지만, 사원 데이터를 뽑을 장부(e1)와 상사 데이터를 찾을 장부(e2)를 각각 책상에 펼쳐놓기 위해 이름을 다르게 붙인 것 각 사원과 이를 관리하는 사원의 정보를 검색 SQL> SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename FROM emp e1, emp e2 3 WHERE e1.mgr = e2.eno ---> e1 상사번호와 e2의 사원번호가 같은 사람을 짝지어라  * e1: 사원 테이블, e2: 관리자 테이블

 

외부(Outer) 조인

SELECT 컬럼, 컬럼, ... FROM 테이블1, 테이블2, ... WHERE 조인_조건(+) AND 일반_조건 - 조인 조건에 맞지 않아 일치하지 않는 데이터를 출력 - 목적: 짝이 없는 데이터(예: 상사가 없는 사원, 사원이 없는 부서)를 버리지 않고 보고 싶을 때 사용 - 특징: "+" 기호는 데이터가 부족한 쪽에 넣음 실습 하기  각 부서별로 사원의 명단을 검색 SQL> SELECT d.dno 부서번호, dname 부서명, ename 사원명 2 FROM dept d, emp e 3 WHERE d.dno = e.dno 4 ORDER BY 1; SQL> SELECT d.dno 부서번호, dname 부서명, ename 사원명 2 FROM dept d, emp e 3 WHERE d.dno = e.dno(+)     --->  사원(e) 테이블에 해당 부서번호(dno)를 가진 사원이 없더라도, 부서(d) 정보는 무조건 출력해! 대신 사원 이름 칸은 **빈칸(NULL)**으로 채워줘 4 ORDER BY 1;

 

문제풀이 

1. 학생 중에 동명이인을 검색한다. SELECT DISTINCT s1.sno, s1.sname FROM student s1, student s2 where s1.sno ! = s2. sno AND s1.sname = s2.sname  ORDER BY sname; 2. 모든 직원의 명단과 이를 관리하는 직원의 명단을 검색한다. SELECT e1.eno, e1.ename, e1.emr, e2.eno, e2.ename  FROM emp e1, emp e2 WHERE e1.mgr = e2.eno(+); 3. 직원 중에 자신의 관리자 보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색 한다. SELECT w.eno, w.ename, w.sal, m.eno, m.ename, m.sal FROM emp w, emp m WHERE w.mgr=m.eno AND w.sal > m.sal; 4. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색한다. SELECT section, p.pno, pname, cname FROM professor p, course c WHERE p.pno=c.cno(+) ORDER BY section; 5. 모든 과목 목록과 각 과목별 담당 교수를 검색한다. SELECT c.cno, cname, p.pno, pname FROM course c, professor p WHERE p.pno(+)=c.pno;

 

참고사항 부모(DEPT) - 자식(EMP) 관계 정리  부모 테이블(DEPT): 기준 정보  - 역할: 참조 당하는 테이블(먼저 존재해야 함) - PK(Primary Key): dno - 비유: 학과 명단이나 부서 목록  자식 테이블(EMP): 참조하는 테이블 (나중에 데이터를 넣음) - FK(Foreign Key): dno - 비유: 학생 명단이나 직원 명단