관계와 조인

모델의 관계는 SQL에서 조인 조건이 됩니다. 자식의 외래키(FK)와 부모의 기본키(PK)를 같다고 이으면, 정규화로 나눠 둔 데이터를 다시 한 줄로 볼 수 있습니다.

정규화로 나눈 사원과 부서를 함께 보려면 어느 열끼리 이을지 알아야 하는데, 그 답이 모델의 관계선에 있습니다. 선 끝의 선택 기호는 외부 조인이, 자기 자신에게 돌아오는 선은 셀프 조인이 필요하다는 신호입니다.

…FROM EMP E  LEFT OUTER JOIN DEPT D  ON E.DEPTNO = D.DEPTNO…

…FROM EMP E  LEFT OUTER JOIN DEPT D  ON E.DEPTNO = D.DEPTNO…

…FROM EMP E  INNER JOIN EMP M  ON E.MGR = M.EMPNO…

…FROM EMP E  LEFT OUTER JOIN DEPT D  ON E.DEPTNO = D.DEPTNO…

…FROM EMP E  INNER JOIN DEPT D  ON E.DEPTNO = D.DEPTNO…

1관계가 조인 조건이 된다

사원의 DEPTNO는 부서에서 받아 온 FK이고, 주식별자에 들지 않으므로 두 엔터티는 비식별 관계입니다. DEPTNO 값이 같은 행끼리 짝이 되므로 '자식의 FK = 부모의 PK'인 E.DEPTNO = D.DEPTNO가 조인 조건입니다. E·D는 EMP·DEPT에 붙인 별칭(alias — 테이블에 붙이는 짧은 다른 이름, 조인 레슨에서 자세히)입니다. 짝이 있는 행만 남기는 내부 조인(INNER JOIN)으로 이으면 부서 없는 정하늘은 빠집니다.

관계선은 조인의 꼴도 알려 줍니다.

정규화로 테이블을 많이 나눌수록 조인도 늘어납니다. 다만 식별 관계로 이어진 손자 엔터티는 할아버지의 식별자를 주식별자에 물려받아, 중간 엔터티를 조인하지 않고도 그 값으로 거를 수 있습니다. 비식별 관계만 쓰면 조인이 더 필요합니다.

2선택 관계와 외부 조인

관계선의 부서 쪽 끝 ○는 사원에게 부서가 없을 수도 있다는 뜻입니다. 상대가 이렇게 선택이면 내부 조인은 짝 없는 행을 버리므로, 사원을 빠짐없이 보려면 짝 없는 행도 남기고 상대 칸을 NULL로 채우는 외부 조인(OUTER JOIN)을 씁니다. EMP를 왼쪽에 두고 왼쪽 행을 모두 남기는 LEFT OUTER JOIN으로 이으면, 정하늘이 DNAME NULL로 더해져 6행이 됩니다.

3순환 관계와 셀프 조인

사원의 MGR은 같은 EMP의 EMPNO를 가리킵니다. 이처럼 FK가 자기 테이블의 PK를 가리키는 관계가 순환 관계(recursive relationship)입니다. 김민준 → 최유나·강도윤 → 정하늘·이서연·박지호처럼 위아래가 생겨 계층형 데이터 모델이라고도 합니다. 데이터를 나무 모양으로 저장하던 예전의 '계층형 DBMS'와는 다른 말로, 한 테이블 안의 상하 관계를 뜻합니다. 맨 위 김민준은 MGR이 NULL입니다.

같은 EMP를 사원 쪽 E와 관리자 쪽 M 두 별칭으로 부르고 E.MGR = M.EMPNO로 이으면 사원마다 관리자 이름이 붙습니다. 이것이 셀프 조인(self join)이고, 별칭이 달라야 두 쪽을 가를 수 있습니다. 관리자의 관리자까지 따라가는 계층형 질의는 START WITH(시작점인 뿌리 행을 정하는 절)와 CONNECT BY(다음 단계로 이을 조건)로 씁니다(계층형 질의와 셀프 조인 레슨에서 자세히).

4상호배타적 관계

개인고객과 법인고객은 둘 다 주문의 부모가 될 수 있지만, 주문 한 건의 부모는 그 가운데 하나뿐입니다. 이처럼 한 엔터티의 인스턴스가 두 관계 가운데 하나만 맺을 수 있는 것을 상호배타적 관계(mutually exclusive relationship)라 합니다. 바커 표기에서는 두 관계선을 가로지르는 호(arc)로 그립니다. 주문은 두 고객 가운데 하나와 꼭 맺으므로 주문 쪽 반선이 실선이고, 호가 그 두 반선을 잇습니다.

예제

관계선 그대로 조건을 쓴 내부 조인입니다.

SELECT E.EMPNO,
  E.ENAME,
  D.DNAME
FROM EMP E
  INNER JOIN DEPT D
  ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

사원을 모두 보려면 EMP 기준으로 외부 조인합니다.

SELECT E.EMPNO,
  E.ENAME,
  D.DNAME
FROM EMP E
  LEFT OUTER JOIN DEPT D
  ON E.DEPTNO = D.DEPTNO
ORDER BY E.EMPNO;

셀프 조인에서 E는 사원 쪽, M은 관리자 쪽입니다.

SELECT E.EMPNO,
  E.ENAME,
  M.EMPNO AS MGR_NO,
  M.ENAME AS MGR_NAME
FROM EMP E
  INNER JOIN EMP M
  ON E.MGR = M.EMPNO
ORDER BY E.EMPNO;

김민준은 MGR이 NULL이라 빠집니다.

시험 포인트

• 조인 조건은 '자식의 FK = 부모의 PK'이고, 정규화로 테이블이 많이 나뉠수록 조인도 늘어납니다.
• 기준 엔터티 입장에서 상대가 선택이면 짝 없는 행이 내부 조인에서 빠지므로, 외부 조인을 씁니다.
• 순환 관계의 맨 위 인스턴스는 FK가 NULL입니다. 셀프 조인은 같은 테이블에 서로 다른 별칭을 붙입니다.
• 식별 관계로 이어진 손자는 중간 엔터티를 조인하지 않고 할아버지의 식별자로 거를 수 있습니다.
• 상호배타적 관계는 바커 표기에서 호(arc)로 그립니다. 자료에 따라 아크라고도 부르며, 어느 이름으로 나와도 같은 것입니다.

확인 문제

1. 다음 쿼리의 결과는 몇 행이고, MGR_NAME이 NULL인 사원은 누구일까요?

SELECT E.EMPNO,
  E.ENAME,
  M.ENAME AS MGR_NAME
FROM EMP E
  LEFT OUTER JOIN EMP M
  ON E.MGR = M.EMPNO
ORDER BY E.EMPNO;

2. 강도윤이 관리하는 사원을 찾는 쿼리입니다. 결과는 무엇일까요?

SELECT E.EMPNO,
  E.ENAME
FROM EMP E
  INNER JOIN EMP M
  ON E.MGR = M.EMPNO
WHERE M.ENAME = '강도윤'
ORDER BY E.EMPNO;

3. 40번 인사 부서처럼 사원이 없는 부서도 있습니다. 부서를 모두 보며 소속 사원 이름을 붙이려면 어떻게 조인하고, 결과는 몇 행일까요?

SELECT D.DEPTNO,
  D.DNAME,
  E.ENAME
FROM DEPT D
  LEFT OUTER JOIN EMP E
  ON E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO,
  E.EMPNO;