자기 참조 조인과 외부 조인

Infra & Security Eng/Database Engineering

자기 참조 조인과 외부 조인

엔지니어 E 2026. 2. 2. 11:08

자기 참조 조인

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
- 비유: 학생 명단이나 직원 명단