[SQL] 관계에 따른 조인의 이해

Posted Sep 6, 2022

11 min read

[SQL] 관계에 따른 조인의 이해

관계란 부모의 식별자를 자식에게 상속시키는 행위이다. 관계를 맺는 다는 것은 식별자를 상속하고, 상속된 속성을 매핑키로 활용하여 데이터를 결합해 볼 수 있다는 의미이다. 이를 SQL에서는 조인이라고 하며, SQL 입문자들은 대게 조인을 어려워한다. 이유는 어쩌면 관계에 대하여 정확히 이해하지 못했기 때문일지 모른다. 그럼 아래 몇가지 예제를 통해 관계와 조인을 좀더 깊이 이해해보자.

일반적인 관계

위 모델에서 고객과 주문 엔터티가 관계를 맺고 있다. 고객 엔터티의 식별자인 고객번호를 주문 엔터티에 상속시킨 것이다. 아래 데이터를 보자.

고객과 주문 샘플 데이터

주문 엔터티에 고객 엔터티의 고객번호 데이터를 상속시켰다. 주문번호가 1100001인 주문의 고객명은 누구인가? ‘정우진’ 고객이다. 우리는 ‘정우진’ 고객 명을 어떻게 알 수 있었을까? 이를 풀어보면 다음과 같다.

주문 데이터에서 주문번호가 1100001인 데이터를 찾는다.
주문번호가 1100001 데이터의 행에서 고객번호가 100임을 확인한다.
고객 데이터에서 고객번호가 100인 데이터를 찾는다.
고객번호가 100인 데이터의 행에서 고객명인 ‘정우진’이라는 것을 확인한다.

이렇게 우리는 위와 같은 흐름으로 고객명을 찾았을 것이다. 이것이 바로 일반적인 관계를 활용한 조인이다. 이것을 쿼리로 작성하면 다음과 같다.

  
SELECT B.고객명
  FROM 주문 A, 고객 B
 WHERE A.주문번호 = '1100001'
   AND A.고객번호 = B.고객번호;

계층형 모델 관계

계층형 모델이 생소한 독자들도 있을 것이다. 계층형 모델이란 말 그대로 계층 구조를 가진 데이터 모델을 지칭한다. 바로 예시 모델을 보자.

계층형 모델 관계 표현 예시

위 모델은 SQL 입문자들이 가장 많이 접하는 샘플 스키마 EMP 모델이다. 이 모델은 많은 사람들이 계층형 데이터 모델이라는 사실을 모른다. 이 모델의 샘플 데이터를 살펴보자.

EMP 샘플 데이터

EMP 데이터에서 주목해야할 속성은 MGR이다. MGR 속성은 각 사원 관리자의 사원번호를 의미한다. 즉 ‘SMITH’의 관리자는 사원본호가 7902인 ‘FORD’가 된다. 그럼 ‘FORD’의 관리자는 누구일까? 사원번호가 7566인 ‘JONES’다. 관리자를 찾아가는 과정이 낯익지 않은가? 이 또한 위에서 언급한 조인이다. 이를 풀어보면 다음과 같다.

EMP A에서 ENAME이 ‘SMITH’인 데이터를 찾는다.
EMP A에서 ENAME이 ‘SMITH’ 데이터의 행에서 MGR이 7902라는 것을 확인한다.
EMP B에서 EMPNO가 7902인 데이터를 찾는다.
EMP B에서 EMPNO가 7902인 데이터의 행에서 ENAME가 ‘FORD’라는 것을 확인한다.

이러한 흐름을 쿼리로 작성하면 다음과 같다.

  
SELECT B.ENAME
  FROM EMP A, EMP B
 WHERE A.ENAME = 'SMITH'
   AND A.MGR = B.EMPNO;

앞전에 일반적인 관계에서의 쿼리와 다른점은 자기자신을 조인했다는 것이다. 이를 우리는 셀프조인이라고 한다. 셀프조인이 가능한 이유는 무엇일까? 위 쿼리는 자기 자신에서 관계를 맺은 것으로 볼 수 있다. 관계를 맺으면식별자를 상속하다고 언급하였는데, 본 모델에서는 상속된 식별자가 자기 자신의 MGR 속성이기 때문이다.

상호배타적 관계

상호배타적 관계는 업무에 따라 얼마든지 만날 수 있으므로 개념 정도는 숙지하고 있어야 한다.

상호배타적 관계 예시

위 모델은 개인, 법인고객이 존재하는 모델에서 주문과의 상호배타적 관계를 표현하는 예시이다. 본 모델에서는 관계의 배타적 관계로서 주문 엔터티에는 개인 또는 법인번호 둘 중 하나만 상속될 수 있음을 의미한다. 즉 주문은 개인고객이거나 법인고객 둘 중 하나의 고객만이 가능하다. 이를 데이터로 보면 더 명확하게 이해할 수 있다.

주문 샘플 데이터

개인/법인번호는 개인고객 또는 법인 고객의 식별자가 상속된 값이다. 이때 고객구분코드의 값을 통해 개인고객의 식별자가 상속되었는지, 법인고객의 식별자가 상속되었는지를 나타낸다.

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

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

개인번호와 법인번호는 중복되지 않기에 아우터 조인으로 연결하였다. 둘 중하나는 조인에 성공하기 때문에 위와 같인 쿼리를 작성해도 무방하다. 단 SELECT 절에서는 조인되는 테이블의 고객명을 가져와야 하기에 COALESCE함수를 사용하였다.

두 방식의 쿼리 차이점을 생각해보면, UNION ALL을 사용한 첫 번째 쿼리는 만일 조인되는 결과가 없다면 공집합을 출력할 것이다. 아우터 조인을 활용한 두번째 쿼리는 조인되는 결과가 없다면 NULL값을 가진 한건의 ROWS(NULL ROWS)를 출력하게 된다. 모델 상으로는 반드시 둘 중 하나의 값을 가져야 하지만, 데이터 품질문제로 이와 같은 결과가 출력될 수도 있으니 이역시 쿼리 작성 시 고려해야 한다. 이처럼 쿼리는 모델을 정확히 이해하고 작성하는 습관을 들이는 것이 중요하다.

읽어주셔서 감사합니다. 😊

Reference
SQL 전문가 가이드 - Kdata 한국데이터산업진흥원

Language, SQL

sql 관계 조인 관계에 따른 조인의 이해 relationship join Understanding joins by relationships

This post is licensed under CC BY 4.0 by the author.

일반적인 관계

계층형 모델 관계

상호배타적 관계

Trending Tags