sql 자연조인 : 속성 값이 같은 것들끼리만 결합한다.

 SQL의 자연 조인에 대해 알아봅시다. natural join은 기본적으로 동등 조인입니다. 그런데, 흔히 알고 있는 join A using B, 혹은 ON 조건절을 거는 것과 다른 점은, 조인 조건이 없다는 것입니다. 테이블 2개가 있을 때, 2개에 공통적으로 나타나는 속성들이 있을 거에요.  이 속성들에 대해서 같은 쌍만을 고려합니다.

 

 

---

 예제를 들어보도록 하겠습니다. 어제 보았던 sakila를 보도록 합시다. rental과 customer를 natural join 한다고 해 봅시다.

 

 

 먼저 table에 있는 속성들을 모두 봅시다. 고객 id, 인벤토리 id, 최근 업데이트 날짜, ... 이렇게 들어 있네요.

 

 

 그리고 고객 테이블을 보면, customer_id가 있고, last_update가 눈에 보입니다. 고객이 물건을 빌려갔으니까, 고객 ID는 두 테이블에 공통적으로 나타나는 속성이 맞긴 합니다. 그런데 굳이 last_update라는 것까지? 두 테이블에서 공통적인 속성은, customer_ID와 last_update입니다.

 

 

 그러면 rental과 customer를 자연 조인 하면 어떤 일이 나타날까요? 일정 기간동안 물건을 빌린 고객 수라던지, 고객 이름을 뽑고 싶을 때, 두 테이블을 join을 해야 할 겁니다. 그런데.. 예상과는 다른 결과가 나옵니다.

 

 

 빈 결과만 나왔다는 것을 알 수 있는데요. Table customer에 있는 last_update와, Table rental에 있는 last_update가 다르기 때문에 그렇습니다.

 

---

 다른 예제를 봅시다. 이번에는 world 데이터베이스를 고려해 보겠습니다. 저는 특정 language를 쓰는 country의 이름들을 모두 출력하려고 합니다. 그러면, country와 countrylanguage를 JOIN 해야 한다는 것을 Table 내용을 보셨다면 아실 수 있을 거에요.

 

 

 먼저 country 테이블의 속성들을 봅시다. 너무 많네요. 그러면 상대적으로 더 적은, language Table을 보겠습니다.

 

 

 CountryCode, Isofficial, Language, Percentage. 이렇게 4개가 있어요. 이 4개 중에 어느 하나라도 나라 테이블에 나타나는 속성이 있나요? 없습니다. 이러한 경우에는 어떻게 되는지 잘 모르겠어요. 그래서 직접 쿼리를 입력해 보았습니다.

 

 

 서브 쿼리에, country와 language를 자연 조인한 결과를 t table이라고 하고 있어요. 이 테이블로부터 행의 갯수만을 뽑는 쿼리인데요. 결과를 보겠습니다.

 

 

 235176. 상당히 큰 숫자가 나온다는 것을 알 수 있어요. 어떻게 이런 결과가 나왔는지 추론해 봅시다.

 

 

 두 테이블의 레코드 갯수를 출력해 봅시다. 이는 select count(*) from ~ r구문으로 가능합니다.

 

 

 이 둘의 행 갯수를 보았더니 각각 239, 984개가 나왔습니다. 이 둘을 곱하면 놀랍게도 235176이 나오는데요. 제 환경에서는, 겹치는 속성이 없었기 때문에 그냥 카티션 곱을 한 결과를 리턴한 셈이 되었습니다. 한 가지 확실한 건 의도와는 다른 결과가 나왔다는 것입니다.

 

 

 원래 의도대로 하려면, 쿼리를 이렇게 짜야 합니다. country와 cl의 카티션 곱에서 1차 필터링 조건으로, 나라의 3자리 코드가 같은지를 검사하고, 2번째 필터 조건으로 특정 Language에 대한 걸 걸어버리면 될 거에요. 위 예제는, Language가 English인 걸 필터링 한 것입니다.

 

 

---

 데이터 베이스 교과서 예제에도 나왔고, 스택 오버 플로우 질문에도 올라온 내용을 보도록 하겠습니다.

 

  라는 문제가 있다고 생각해 봅시다. teaches 테이블을 보았을 때, 우리는 필요한 테이블 2개를 유추할 수 있어요.

 

 

 바로 instructor와, course입니다. 그러면 우리는 간단하게 요렇게 natural join을 시도할 수도 있어요. instructor와 teaches는 교수의 ID가 동일할 거고, teaches와 course는 course_id가 동일할 거니까, 그냥 세 개를 자연 조인으로 연결해도 되지 않을까?

 

 

 그러면 이렇게 작성할 수 있을 겁니다. 결과 값은 84개가 나옵니다.

 

 

 그런데 subQuery를 이용해서 작성한 이 쿼리의 결과 레코드 갯수는 100개입니다. 물론, 교과서에 나온 아래 쿼리도, 100개의 rows가 리턴됩니다.

 

 

 이 쿼리와, 자연 조인 2번을 한 Query를 비교해 보겠습니다. 분명한 것은 그냥 natural join 2번 한 것보다 결과가 더 많이 나왔다는 것인데요. 어떤 레코드들이 결과값에 포함되지 않았는지 보겠습니다. 이것은 저번에 배운 outer join을 이용해서 할 수 있는데요. 조인 조건이 다소 까다롭습니다.

 

 

 강의가 유일하다는 건, course_id랑 년도와 학기까지 모두 같아야 할 겁니다. 그래서 on 조건이 다소 길어졌습니다. 그리고 B는, 결과 값이 올바르게 나오는 결과 Table을, A에는 자연 조인을 2번 쓴, 의도와는 다른 결과가 나오는 join을 넣었습니다. B와 A를 left join을 하면, B에는 있지만, A에는 없는 결과가 나타나는데요.

 

 

 이 중 하나인 ID가 95709인 교수가 강의하는, course_id가 270인 Music of the 90s는 왜 안 나타났는지 보도록 하겠습니다.

 

 

 먼저, instructor와 teaches를 자연 조인을 한 결과는 위와 같습니다. 이 중 ID가 95709이고, course_id가 270인 것은 커서가 가리키고 있는 row입니다. 이제 course를 봅시다.

 

 

 어떤가요? course_id가 270인 것은, 커서가 가리키는 레코드 밖에 없습니다. 그리고 이 수업은 수학과의 수업인지 뭔지 모르겠어요. 그런데, 이 dept_name하고 위에 join이 된 table의 dept_name하고도 값이 같아야 하는데, 그런가요? 그렇지 않아요. course_id의 값은 같지만, dept_name은 다르기 때문에 누락이 되어 버린 것입니다.

 

 교수가 소속된 학과는 English인데, 다른 학과인 Math에서 열리는 수업 중 하나가 270이였던 셈입니다. 글이 다소 길어졌습니다. 자연 조인은, 의도치 않은 결과가 나올 수도 있다는 것만 간파하셨다면 크게 어렵지 않으셨으리라 생각이 듭니다. 그러니 그냥, on 조건을 이용하자. 정도 정리하셔도 좋을 듯 싶습니다.