🌠1-2-2. 관계와 조인의 이해

과목1. 데이터 모델링의 이해

제2장 데이터 모델과 SQL

제2절 관계와 조인의 이해

관계(Relationship)는 사전적으로 정의하면 '상호 연관성이 있는 상태'라고 1과목 4절에서 정의하였다. 이는 어떤 의미일까? 현상적으로 본다면, 관계를 맺는 것은 부모의 식별자를 자식에게 상속시키는 행위다. 이때 부모의 식별자를 자식의 식별자에 포함하면 식별관계, 부모의 식별자를 자식의 일반속성으로 상속하면 비식별관계라고 하였다. 즉 관계를 맺는다는 건 식별자를 상속하고, 상속된 속성을 매핑키로 활용하여 데이터를 결합해 볼 수 있다는 의미다. 이를 SQL에서는 조인(Join)이라 한다.

SQL을 학습자들의 첫 난관이 조인이다. 조인에 대한 개념을 상당히 낯설어 한다. 이는 관계의 개념을 정확히 이해하지 못해서 일 수 있다. 엔터티 간 관계를 맺는다는 것은 데이터를 연결해서 볼 수 있음을 의미한다. 그래서 관계라는 선으로 이어준다. 두 데이터 집합 간 연결고리, 즉 매핑키를 통해 데이터를 연결할 수 있기 때문이다.

1. 조인

[그림 I-2-13] 고객과 주문 엔터티의 관계

[그림 I-2-13] 의 모델은 고객과 주문 엔터티가 관계를 맺고 있는 모습이다. 1장에서 배운 내용을 토대로 본 모델을 읽어보자. 고객 엔터티의 입장에서는 한 명의 고객은 여러 번 주문할 수 있다', 주문 엔터티 입장에서는 각각의 주문은 반드시 한 명의 고객에 의해 발생된다' 정도로 이해할 수 있다. 관계를 맺음으로 생기는 현상은 고객 엔터티의 식별자인 고객번호를 주문 엔터티에 상속시킨 것이다. 즉 관계를 맺는다는 것은 식별자를 상속시키고 해당 식별자를 매핑키로 활용해 데이터를 결합하여 보겠다는 것이다.

[표 I-2-5] 고객과 주문 데이터

고객번호	고객명
100	정우진
101	한형식
102	황영은

[고객]

주문번호	고객번호	주문상태코드
1100001	100	주문완료
1100002	101	주문완료
1100003	101	취소요청
1100004	102	환불요청
1100005	100	교환완료

[주문]

[표 I-2-5]의 주문 데이터의 [고객] 데이터에서 고객번호를 상속시킨 것을 볼 수 있다. 주문번호가 1100001인주문의 고객명은 누구인가? 정우진'이라는 고객일 것이다. 우리는 ‘정우진' 고객명을 어떻게 알 수 있었을까? 이를풀어보면 다음과 같다.

• ① 주문 데이터에서 주문번호가 1100001인 데이터를 찾는다.

• ② 주문번호가 1100001 데이터의 행에서 고객번호가 100임을 확인한다.

• ③ 고객 데이터에서 고객번호가 100인 데이터를 찾는다.

• ④ 고객번호가 100인 데이터의 행에서 고객명인 '정우진'이라는 것을 확인한다.

아마도 위와 같은 순서로 고객명을 찾았을 것이다. 너무도 자연스럽고 당연한 일이다. 이것이 바로 관계를 활용한조인이다. 관계에 의해 상속된 고객번호라는 속성을 가지고 주문 데이터에서 매핑키로써 고객명을 찾아냈다. ②번과③번이 조인이고, 고객번호가 바로 조인키(Join Key)다. 이를 SQL로 작성하고, 앞 순서와 연결해 본다면 다음과 같을 것이다.

SELECT B.고객명                ..... ④
  FROM 주문 A, 고객 B
 WHERE A. 주문번호 = '1100001' ..... ①
   AND A. 고객번호 = B.고객번호 ..... ②, ③

주문 데이터를 통해 고객명을 찾은 방식과 동일하게 SQL도 작성된다. 관계와 조인에 대해 어느 정도 개념이잡혔다면 이제 다른 유형의 관계에 대해 알아보자.

2. 계층형 데이터 모델

일반적인 관계는 [그림 1-2-13]처럼 두 엔터티 간에 발생한다. 하지만 자기 자신에게 관계가 발생하는 경우도 있다. 생소하게 느껴질 수도 있지만, SQL을 공부했다면 익히 알고 있는 모델이다.

[그림 I-2-14] 계층형 데이터 모델 관계 표현

[그림 I-2-14]은 Sample Schema로 제공되는 EMP(사원) 모델이다. SQL을 공부할 때 자주 등장하는 모델이다. 주로 EMP와 DEPT 모델을 활용하여 여러 실습을 진행했을 것이다. 하지만 EMP 모델이 계층형 데이터 모델(Hierarchical Data Model)이라는 사실을 대부분 간과한다. 계층형 데이터 모델에 대한 이해 없이 SQL을 공부하다 계층형 쿼리(Connect By절)를 만나면 또 한번 난관에 봉착하게 된다.

계층형 데이터 모델이란 무엇일까? 말 그대로 계층 구조를 가진 데이터를 지칭하는 것이다. 비교적 익숙한 EMP 모델을 기반으로 알아보자.

[표 I-2-6] EMP(사원) 데이터

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800	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	20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850	30
7782	CLARK	MANAGER	7839	1981-06-09	2450	10
7788	SCOTT	ANALYST	7566	1987-0419	3000	20
7839	KING	PRESIDENT	1981-11-17	5000	10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100	20
7900	JAMES	CLERK	7698	1981-12-03	950	30
7902	FORD	ANALYST	7566	1981-12-03	3000	20
7934	MILLER	CLERK	7782	1982-01-23	1300	10

[표 I-2-]에서 주목해야 할 속성은 MGR이다. MGR 속성은 각 사원 관리자의 사원번호를 의미한다. 즉 'SMITH'의 관리자는 사원번호가 7902인 'FORD'가 된다. 그럼 FORD'의 관리자는 누구일까? 사원번호가 7566인 'JONES'다. 관리자를 찾아가는 과정이 낯익지 않은가? 이 또한 위에서 언급한 조인이다. 이를 SQL로 표현하면 다음과 같다.

SELECT B.ENAME          ... ④
  FROM EMP A, EMP B
 WHERE A. ENAME = SMITH ... ①
   AND A.MGR = B. EMPNO ... ②, ③

• ① EMP A에서 ENAME가 'SMITH'인 데이터를 찾는다.

• ② EMP A에서 ENAME가 'SMITH' 데이터의 행에서 MGR이 7902라는 것을 확인한다.

• ③ EMP B에서 EMPNO가 7902인 데이터를 찾는다.

• ④ EMP B에서 EMPNO가 7902인 데이터의 행에서 ENAME가 'FORD'라는 것을 확인한다.

이전과 다른 점은 자기 자신을 조인했다는 것이다. 이를 우리는 셀프조인(Self-Join)이라고 한다. 이처럼 조인이 가능한 이유는 무엇일까? [그림 I-2-15] 모델을 보면, 자기 자신에서 관계를 맺은 것을 볼 수 있다. 관계를 맺으면 식별자를 상속한다고 했다. 본 모델에서는 상속된 식별자가 바로 MGR 속성이다. 속성명만 다를 뿐 MGR 속성에는 EMPNO가 들어간다. 이를 매핑키로 활용하여 조인할 수 있는 것이다.

즉 계층형 데이터 모델은 데이터 간의 계층이 존재할 때 발생하는 모델이라 할 수 있다. EMP 데이터를 더 직관적으로 표현하면 다음과 같다.

[그림 I-2-15] EMP 모델 계층구조

애초에 EMP 데이터는 계층이 존재하는 데이터이기에 일반적인 모델과는 다른, [그림 I-2-15] 유형의 관계가 표현된다. 이러한 데이터가 생소하지만 실제 업무에 꽤나 녹아 있기에 이러한 모델이 존재한다. 다른 예로 쇼핑몰에는 카테고리 정보가 대표적이다.

[그림 I-2-16] 카테고리 모델 계층구조

[그림 I-2-16]의 카테고리 모델은 전체가 아닌 일부 데이터만 표현하였지만, 익숙하게 경험해본 데이터일 것이다. 계층형 데이터 모델은 특수한 경우에만 발생하는 희귀한 모델은 아니다. 즉 업무에 따라 충분히 만날 수 있는 모델이기에 개념을 명확하게 알고 있어야 한다. 그래야 계층형 쿼리(Connect By절)를 만났을 때 당황하지 않고 학습할 수 있다. 계층형 쿼리가 어려웠다면, 아마도 계층형 구조에 대한 개념을 모르고 접했기 때문일 것이다.

3. 상호배타적 관계

상호 배타적(Exclusive-OR) 관계는 업무에 따라 얼마든지 만날 수 있으므로 개념 정도는 숙지하고 있어야 한다. 내용을 알고 나면 사실 별로 어렵지 않다.

[그림 I-2-17] 상호배타적 관계

[그림 I-2-17] 모델은 개인, 법인고객이 존재하는 모델에서 주문과의 상호배타적 관계를 표현하고 있다. IE표기법에서는 상호배타적 관계 표기를 지원하지 않아 괄호와 유사한 선을 직접 그려준다. 본 모델에서는 관계의 배타적관계로서 주문 엔터티에는 개인 또는 법인번호 둘 중 하나만 상속될 수 있음을 의미한다. 즉 주문은 개인고객이거나 법인고객 둘 중 하나의 고객만이 가능하다. 이를 데이터로 보면 더 명확하게 이해할 수 있다.

[표 I-2-6] 주문 데이터

주문번호	고객구분코드	개인/법인번호
1100001	01	1234
1100002	02	1122334455
1100003	01	1356
1100004	01	2556
1100005	02	2233445566

[주문]

[표 I-2-6]의 주문 데이터를 보면, 개인/법인번호는 개인고객 또는 법인고객의 식별자가 상속된 값이다. 이때 고객구분코드의 값을 통해 개인고객의 식별자가 상속되었는지, 법인고객의 식별자가 상속되었는지를 나타낸다(고객구분코드 01: 개인고객, 02: 법인고객).

그렇다면 [표 I-2-6]에서 주문번호가 1100001인 주문의 주문자명을 보여주고 싶을 때는 SQL을 다음과 같이 작성해야 한다.

SELECT B.개인고객명
  FROM 주문 A, 개인고객 B
 WHERE A.주문번호 = 1100001
   AND A.고객구분코드 = '01'
   AND A.개인/법인번호 = B.개인번호
 UNION ALL
SELECT B. 법인명
  FROM 주문 A, 법인고객 B
 WHERE A.주문번호 = 1100001
   AND A.고객구분코드 = 02
   AND A.개인/법인번호 = B.법인번호

고객구분코드를 알 수 있다면 명확하게 개인고객 또는 법인고객 테이블을 선택하여 하나의 테이블만 조인하여 SQL을 작성할 수 있다. 그렇지 않은 경우라면 위와 같은 SQL이 최선일 것이다. 이때 주의할 점은 개인번호와 법인번호에 중복이 발생되어서는 안 된다는 것이다. 법인번호는 10자리이므로 개인번호가 십억 명을 초과하지 않는다면 문제가 되지 않겠지만, 만일 그 이상이 된다면 2건의 데이터가 출력될 수 있다. 이에 해당하는 경우라면 반드시 고객구분코드 값을 변수로 받아야 할 것이다. 또한 어차피 개인번호와 법인번호가 중복되지 않는다는 전제가 있다면 다음과 같은 SQL도 가능하다.

SELECT COALESCE(B. 개인고객명, C.법인명) 고객명
  FROM 주문 A LEFT OUTER JOIN 개인고객 B
    ON (A. 개인/법인번호 = B. 개인번호) LEFT OUTER JOIN 법인고객 C
	ON (A. 개인/법인번호 = C. 법인번호)
 WHERE A. 주문번호 = 1100001;

개인번호와 법인번호는 중복되지 않기에 아우터 조인으로 연결하였다. 둘 중 하나는 조인에 성공하기 때문에 위와 같이 SQL을 작성해도 무방하다. 단 SELECT 절에서는 조인되는 테이블의 고객명을 가져와야 하기에 COALESCE 함수를 사용하였다. 두 방식의 SQL 차이점을 생각해보면, UNION ALL을 사용한 첫 번째 SQL은 만일 조인되는 결과가 없다면 공집합(NO ROWS)을 출력할 것이다. 아우터 조인을 활용한 두 번째 SQL은 조인되는 결과가 없다면 NULL값을 가진 한 건의 ROWS(NULL ROWS)를 출력하게 된다. 모델 상으로는 반드시 둘 중 하나의 값을 가져야 하지만, 데이터 품질문제로 이와 같은 결과가 출력될 수도 있으니 이 역시 SQL 작성 시 고려해야 할 사안이다. 이처럼 모델을 정확히 이해하고 SQL을 작성하는 습관을 길러야 한다.

출처 : 데이터온에어 – 한국데이터산업진흥원(https://dataonair.or.kr)