셀프 조인

상위 문서: SQL

Self Join
한 테이블을 서로 다른 별칭으로 두 번 이상 참조해 자기 자신과 조인하는 방법

셀프 조인은 같은 테이블을 두 개의 테이블처럼 다루어 조인한다. 사원 테이블의 관리자번호가 같은 테이블의 사원번호를 가리키는 것처럼, 한 테이블 안의 칼럼이 같은 테이블의 다른 행을 참조할 때 쓴다. 별도의 조인 종류가 있는 것은 아니며, 일반 내부 조인이나 외부 조인을 같은 테이블에 적용한 것이다. 사원과 관리자, 상위 분류와 하위 분류, 이전 이력과 다음 이력처럼 순환 관계를 가진 데이터에서 자주 쓴다.

예제 테이블

B_EMP의 관리자번호는 같은 테이블의 사원번호를 참조한다. 김대표는 관리자가 없는 최상위 사원이다.

사원번호	이름	부서번호	관리자번호	급여
1001	김대표	10	NULL	9000
1002	이부장	20	1001	6000
1003	박과장	20	1002	4500
1004	최대리	30	1001	4000
1005	정사원	30	1004	3000
1006	한신입	NULL	1002	2500

기본 형태

한쪽은 사원(E), 다른 쪽은 관리자(M) 역할로 별칭을 붙이고, 사원의 관리자번호와 관리자의 사원번호를 연결한다.

SELECT E.사원번호, E.이름, M.사원번호 AS 관리자번호, M.이름 AS 관리자
FROM B_EMP E
JOIN B_EMP M ON E.관리자번호 = M.사원번호
ORDER BY E.사원번호;

사원번호	이름	관리자번호	관리자
1002	이부장	1001	김대표
1003	박과장	1002	이부장
1004	최대리	1001	김대표
1005	정사원	1004	최대리
1006	한신입	1002	이부장

Oracle 전통 문법으로는 FROM B_EMP E, B_EMP M WHERE E.관리자번호 = M.사원번호로 쓴다.
조인 방향이 중요하다. E.관리자번호 = M.사원번호이면 M이 E의 관리자이고, E.사원번호 = M.관리자번호로 바꾸면 M은 E의 부하 직원이 된다.

별칭은 필수

같은 테이블이 FROM 절에 두 번 나오므로 별칭이 없으면 어느 쪽 칼럼인지 구분할 수 없다.

SELECT 이름 FROM B_EMP, B_EMP WHERE 관리자번호 = 사원번호;
-- ORA-00918: column ambiguously specified (19c 이전 메시지는 column ambiguously defined)

모든 칼럼에 별칭 접두사를 붙여 어느 역할의 칼럼인지 밝혀야 한다.

INNER JOIN과 LEFT OUTER JOIN

내부 조인으로 셀프 조인하면 관리자가 없는(관리자번호가 NULL인) 최상위 사원이 결과에서 빠진다. 모든 사원을 보려면 외부 조인을 쓴다.

SELECT E.이름, M.이름 AS 관리자
FROM B_EMP E
LEFT OUTER JOIN B_EMP M ON E.관리자번호 = M.사원번호
ORDER BY E.사원번호;
-- Oracle 문법: WHERE E.관리자번호 = M.사원번호(+)

이름	관리자(INNER JOIN)	관리자(LEFT OUTER JOIN)
김대표	(행 없음)	NULL
이부장	김대표	김대표
박과장	이부장	이부장
최대리	김대표	김대표
정사원	최대리	최대리
한신입	이부장	이부장
행 수	5	6

2단계 셀프 조인

관리자의 관리자(차상위 관리자)까지 보려면 같은 테이블을 세 번 참조한다.

SELECT E.이름, M.이름 AS 관리자, MM.이름 AS 차상위관리자
FROM B_EMP E
JOIN B_EMP M ON E.관리자번호 = M.사원번호
LEFT OUTER JOIN B_EMP MM ON M.관리자번호 = MM.사원번호
ORDER BY E.사원번호;

이름	관리자	차상위관리자
이부장	김대표	NULL
박과장	이부장	김대표
최대리	김대표	NULL
정사원	최대리	김대표
한신입	이부장	김대표

첫 조인이 내부 조인이므로 김대표는 빠진다. 두 번째 조인을 내부 조인으로 바꾸면 관리자가 김대표인 이부장, 최대리도 빠져 3행이 된다.
단계가 늘 때마다 조인을 하나씩 더해야 하므로 깊이가 정해지지 않은 계층에는 맞지 않는다.

셀프 조인과 집계

관리자별 부하 직원 수처럼 조인 결과를 그룹으로 묶을 수 있다.

SELECT M.이름 AS 관리자, COUNT(*) AS 부하수
FROM B_EMP E JOIN B_EMP M ON E.관리자번호 = M.사원번호
GROUP BY M.이름
ORDER BY 2 DESC, 1;
-- 김대표 2, 이부장 2, 최대리 1

셀프 조인과 계층형 질의

같은 순환 관계를 Oracle의 계층형 질의(START WITH ... CONNECT BY)로도 다룰 수 있다(SQL 계층형 질의).

SELECT LEVEL, LPAD(' ', 2*(LEVEL-1)) || 이름 AS 조직도
FROM B_EMP
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호
ORDER SIBLINGS BY 사원번호;
-- 1 김대표 / 2 이부장 / 3 박과장 / 3 한신입 / 2 최대리 / 3 정사원

구분	셀프 조인	계층형 질의
결과 형태	한 행에 사원과 관리자를 나란히(가로) 보여 준다.	한 행에 한 사원, 위에서 아래로 트리 순서(세로)로 보여 준다.
탐색 깊이	조인한 횟수만큼 고정	데이터 깊이만큼 자동으로 내려간다.
깊이 정보	직접 계산해야 한다.	LEVEL 의사 칼럼을 제공한다.
표준 여부	표준 조인 문법	CONNECT BY는 Oracle 문법. 표준과 SQL Server는 재귀 WITH(CTE)를 쓴다.

시험 포인트

셀프 조인에서는 별칭이 반드시 필요하다. 별칭 없이 쓰면 칼럼이 모호하다는 오류(ORA-00918)가 난다.
조인 조건의 방향(E.관리자번호 = M.사원번호)에 따라 M이 관리자인지 부하인지 달라진다.
내부 조인으로 셀프 조인하면 관리자번호가 NULL인 최상위 사원이 빠진다. 외부 조인을 쓰면 포함된다. 결과 행 수를 묻는 문제가 나온다.
고정된 단계(관리자, 차상위 관리자)는 셀프 조인으로, 깊이가 정해지지 않은 트리는 계층형 질의로 처리한다.

같이 보기

참고 문헌