1. Self Join

개념

• 한 테이블 내 각 행끼리 관계를 갖는 경우 사용하는 조인 기법
• 한 테이블을 참조할 때마다 명시해야 함
• 테이블명이 중복되므로 반드시 테이블 별칭 사용

특징

• 같은 테이블을 여러 번 조인
• 테이블 별칭으로 구분하여 사용
• 주로 조직도, 직원-매니저 관계 등에서 활용

문법

SELECT A.컬럼1, B.컬럼2
FROM 테이블명 A, 테이블명 B
WHERE A.연결컬럼 = B.연결컬럼;

📊 예제 테이블 (EMP)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800	NULL	20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975	NULL	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	NULL	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	NULL	10
7788	SCOTT	ANALYST	7566	1987-04-19	3000	NULL	20
7839	KING	PRESIDENT	NULL	1981-11-17	5000	NULL	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100	NULL	20
7900	JAMES	CLERK	7698	1981-12-03	950	NULL	30
7902	FORD	ANALYST	7566	1981-12-03	3000	NULL	20
7934	MILLER	CLERK	7782	1982-01-23	1300	NULL	10

💡 예제 1: 직원과 매니저 이름 함께 출력

SELECT E1.ENAME AS 직원명, E2.ENAME AS 매니저명
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

결과

직원명	매니저명
SMITH	FORD
ALLEN	BLAKE
WARD	BLAKE
JONES	KING
MARTIN	BLAKE
BLAKE	KING
CLARK	KING
SCOTT	JONES
TURNER	BLAKE
ADAMS	SCOTT
JAMES	BLAKE
FORD	JONES
MILLER	CLARK

설명: KING은 MGR이 NULL이므로 결과에 포함되지 않음 (INNER JOIN 기본)

💡 예제 2: 매니저보다 급여가 많은 직원

SELECT E1.ENAME AS 직원명, E1.SAL AS 직원급여, 
       E2.ENAME AS 매니저명, E2.SAL AS 매니저급여
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO
AND E1.SAL > E2.SAL;

결과

직원명	직원급여	매니저명	매니저급여
SCOTT	3000	JONES	2975
FORD	3000	JONES	2975

설명: SCOTT과 FORD만 자신의 매니저보다 급여가 높음

2. Hierarchical Query

개념

• 하나의 테이블 내 각 행끼리 관계를 가질 때, 연결고리를 통해 행과 행 사이의 계층(depth)을 표현하는 기법
• 조직도, 부서 상하관계 등을 표현할 때 사용

문법

SELECT 컬럼명
FROM 테이블명
START WITH 시작조건
CONNECT BY [NOCYCLE] PRIOR 연결조건
[WHERE 조건]
[ORDER SIBLINGS BY 정렬컬럼];

구성 요소

• START WITH: 데이터를 출력할 시작 지점 지정
• CONNECT BY PRIOR: 행을 이어나갈 조건
• NOCYCLE: 순환 참조 방지
• PRIOR 위치: 부모→자식 또는 자식→부모 방향 결정

PRIOR 위치에 따른 방향

-- 부모 → 자식 방향 (Top-Down)
CONNECT BY PRIOR 자식키 = 부모키

-- 자식 → 부모 방향 (Bottom-Up)  
CONNECT BY PRIOR 부모키 = 자식키

📊 예제 테이블 (DEPT2)

DCODE	DNAME	PDEPT	AREA
1000	사장실	NULL	서울
1100	경영지원실	1000	서울
1200	영업부	1000	서울
1210	영업1팀	1200	서울
1220	영업2팀	1200	경기
1300	연구개발부	1000	대전
1310	연구팀	1300	대전
1320	개발팀	1300	대전

💡 예제 1: 최상위 부서부터 하위 부서까지 계층 구조

SELECT LEVEL, LPAD(' ', (LEVEL-1)*2) || DNAME AS 부서구조, DCODE, PDEPT
FROM DEPT2
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT
ORDER SIBLINGS BY DNAME;

결과

LEVEL	부서구조	DCODE	PDEPT
1	사장실	1000	NULL
2	경영지원실	1100	1000
2	연구개발부	1300	1000
3	개발팀	1320	1300
3	연구팀	1310	1300
2	영업부	1200	1000
3	영업1팀	1210	1200
3	영업2팀	1220	1200

💡 예제 2: 특정 부서의 하위 부서만 조회

SELECT LEVEL, LPAD(' ', (LEVEL-1)*2) || DNAME AS 부서구조, DCODE
FROM DEPT2
START WITH DCODE = 1200  -- 영업부부터 시작
CONNECT BY PRIOR DCODE = PDEPT;

결과

LEVEL	부서구조	DCODE
1	영업부	1200
2	영업1팀	1210
2	영업2팀	1220

가상 컬럼 및 함수

가상 컬럼

• LEVEL: 각 계층의 깊이 (시작점부터 1)
• CONNECT_BY_ISLEAF: 최하위 노드 여부 (1: 리프노드, 0: 비리프노드)
• CONNECT_BY_ISCYCLE: 순환 참조 발생 여부

가상 함수

• CONNECT_BY_ROOT 컬럼명: 루트 노드의 해당 컬럼값
• SYS_CONNECT_BY_PATH(컬럼, 구분자): 루트부터 현재 노드까지의 경로
• ORDER SIBLINGS BY: 같은 레벨에서의 정렬

💡 예제 3: 가상 컬럼 및 함수 활용

SELECT LEVEL,
       DNAME,
       CONNECT_BY_ISLEAF AS "리프노드여부",
       CONNECT_BY_ROOT DNAME AS "루트부서",
       SYS_CONNECT_BY_PATH(DNAME, ' > ') AS "부서경로"
FROM DEPT2
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT
ORDER SIBLINGS BY DNAME;

결과

LEVEL	DNAME	리프노드여부	루트부서	부서경로
1	사장실	0	사장실	> 사장실
2	경영지원실	1	사장실	> 사장실 > 경영지원실
2	연구개발부	0	사장실	> 사장실 > 연구개발부
3	개발팀	1	사장실	> 사장실 > 연구개발부 > 개발팀
3	연구팀	1	사장실	> 사장실 > 연구개발부 > 연구팀
2	영업부	0	사장실	> 사장실 > 영업부
3	영업1팀	1	사장실	> 사장실 > 영업부 > 영업1팀
3	영업2팀	1	사장실	> 사장실 > 영업부 > 영업2팀

CONNECT BY 절 조건

• 계층 구조 설정 조건
• START WITH 데이터는 항상 출력됨
• 조건에 맞지 않으면 하위 연결 중단

WHERE 절 조건

• 전체 결과 필터링
• START WITH 데이터도 조건에 맞지 않으면 생략

💡 예제 4: CONNECT BY 절 조건 vs WHERE 절 조건

CONNECT BY 절 조건:

SELECT LEVEL, DNAME, DCODE
FROM DEPT2
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT AND DNAME != '연구개발부';

WHERE 절 조건:

SELECT LEVEL, DNAME, DCODE
FROM DEPT2
START WITH PDEPT IS NULL
CONNECT BY PRIOR DCODE = PDEPT
WHERE DNAME != '연구개발부';

결과

CONNECT BY 절 조건 결과:
| LEVEL | DNAME | DCODE |
|-------|------------|-------|
| 1 | 사장실 | 1000 |
| 2 | 경영지원실 | 1100 |
| 2 | 영업부 | 1200 |
| 3 | 영업1팀 | 1210 |
| 3 | 영업2팀 | 1220 |

WHERE 절 조건 결과:
| LEVEL | DNAME | DCODE |
|-------|------------|-------|
| 1 | 사장실 | 1000 |
| 2 | 경영지원실 | 1100 |
| 2 | 영업부 | 1200 |
| 3 | 영업1팀 | 1210 |
| 3 | 영업2팀 | 1220 |
| 3 | 개발팀 | 1320 |
| 3 | 연구팀 | 1310 |

차이점: CONNECT BY 조건은 연구개발부 하위까지 연결이 중단되지만, WHERE 조건은 하위부서는 출력됨

순환 참조 처리

📊 순환 참조 예제 테이블 (EMPLOYEES)

EMPLOYEE_ID	MANAGER_ID	FIRST_NAME
1000	2000	John
2000	1000	Jane
3000	1000	Bob

💡 예제 5: NOCYCLE 옵션으로 순환 참조 처리

SELECT LEVEL, EMPLOYEE_ID, MANAGER_ID, FIRST_NAME, CONNECT_BY_ISCYCLE
FROM EMPLOYEES
START WITH EMPLOYEE_ID = 1000
CONNECT BY NOCYCLE PRIOR EMPLOYEE_ID = MANAGER_ID;

결과

LEVEL	EMPLOYEE_ID	MANAGER_ID	FIRST_NAME	CONNECT_BY_ISCYCLE
1	1000	2000	John	0
2	3000	1000	Bob	0

설명: NOCYCLE 옵션이 없으면 1000 → 2000 → 1000으로 무한 루프 발생