#6 6일차_ JOIN : equi join, non equi join, 세 개 이상 테이블의 조인

6일차에 배운 내용을 정리하였다.

 목차는 다음과 같다. :

 

01. join

 -01 조인의 종류

 -02 equi join

 -03 non equi join

 

---

01. join

 -01 조인의 종류

 RDB의 필수 연산 중 하나이다.

 RDB는 각 테이블들이 최소 하나 이상의 관계를 가지는데, 관계를 맺고 있는 테이블끼리는 서로 공유(혹은 비교)할 수 있는 정보(조인키=매핑키)를 가진다.

 조인키가 되는 열의 이름은 가급적 테이블간 통일되는 것이 좋다.

 

 또한 조인 시 조건을 생략하면 테이블의 모든 행이 한 번씩 결합하며 곱집합이 발생한다. (카테시안 곱 : Cartesian Product) 

 

  오라클의 경우 테이블 위치가 크게 상관으나, 해석이 편한 테이블을 먼저 배치하는 것이 편리하다.

  또한 오라클에서는 조인키를 where절에서 지정하며, select에서 무엇을 조회할 것인지 결정한다.

  select절에서 열을 지정할 때 한 테이블에서만 가지고 있는 열의 경우 컬럼 구분자를 지정하지 않아도 오류 없이 정상적으로 작동하나, 기본적으로 항상 열의 출처를 밝혀주는 것이 중요하다. (컬럼구분자.열의 이름)

 

  조인은 조건의 형태에 따라 다음과 같이 나누어진다.

  1) equi join

  2) non equi join

---

1) equi join 

   가장 일반적인 형태의 조인이며, 연결 조건이 '='인 경우이다.

 

  emp, dept 테이블을 사용하여 직원의 사번, 이름, 급여, 소속부서명, 부처위치를 조회하겠다.

 

select e.empno, e.ename, e.sal, d.dname, d.loc                 
  from emp e, dept d
where e.deptno = d.deptno;

 

  emp, dept의 테이블을 연결해야 하므로 from 절에서 두 테이블을 연결한다.

 테이블을 조인하여 emp테이블은 e, dept 테이블은 d라는 컬럼구분자로 지정한다.

 

  emp, dept 테이블은 'deptno'라는 열을 동일하게 공유하며 where 절에서 deptno 열을 기준으로 조인을 진행한다.

 

  사번, 이름, 급여는 emp 테이블, 소속부서명, 부처위치는 dept 테이블에 존재하므로

구하고자 하는 데이터가 어느 테이블에 있는지 확인 후 출처.열의 이름 형태로 조회를 진행한다.

 

 

 원하는 결과가 조회되었음을 확인할 수 있다.

 

 이번에는 student, exam_01 테이블을 이용하여 각 학년별 시험 성적의 평균을 구한다.

 group by와 avg를 사용해야 하는데, 먼저 테이블 조인 후 어떤 데이터가 어느 테이블에 속해 있는지, 무엇을 해야하는지를 생각해가며 쿼리를 작성하였다.

 

select s.grade, avg(e.total) as 시험성적평균
  from student s, exam_01 e
 where s.studno = e.studno
 group by s.grade
 order by s.grade;

 학년별 시험성적평균을 조회할 수 있다.

---

 

 -03 non equi join

   연결 조건이 '='이 아닌 경우이다. (>, <, between, in, like 등)

 

 gogak, gift 테이블을 조인하여 각 고객의 이름과 포인트로 받을 수 있는 상품을 하나 출력하도록 한다.

 

 gogak 테이블은 20행으로 이루어져 있으나, 다음과 같이 where절에서 조인 조건을 지정하지 않고 쿼리를 작성하게 되면 다음과 같은 문제가 발생한다.

select *
  from gogak g1, gift g2;

 

 

 또한 고객의 포인트가 예를 들어 50000이라면, 50000 이하의 범위에 해당되는 상품 X 고객의 조합이 발생한다.

위 쿼리의 경우 gift 테이블은 총 10개의 행으로 구성되어 있기 때문에

 

gogak  테이블 20행 X gift 테이블 10행 = 총 200행

 

 으로 곱집합이 발생하며 수많은 행이 생성되었다.

 따라서 gogak 테이블의 포인트(POINT) 열이 gift 테이블의 포인트 범위 내(G_START ~ G_END)에 포함된다는 조건을 지정해야 한다.

 gift 테이블을 살펴보면 다음과 같이 각 상품별로 포인트의 범위가 지정되어 있다.

 

 

 따라서 where절에서 gogak 테이블의 POINT열이 조인키가 되며, 이 조인키가 gift 테이블의 G_START부터 G_END까지 포함된다는 조건 하에 고객이 수령할 수 있는 상품명이 조인된다.

 

select g1.gname as 고객명, g2.gname as 상품
  from gogak g1, gift g2
 where g1.point between g2.g_start and g2.g_end;

 

 

  조건을 지정함으로써 20행 모두 포인트에 맞는 상품을 수령하게 되었다.

---

  * 세 테이블 이상 조인하기

 

 세 테이블 이상 조인하게 되면 where 절에서 각 조건을 and를 통해 연결해주어야 한다.

a테이블에 5행, b테이블에 5행, c테이블에 2행이 있을 때, a와 b 테이블은 특정 조인키를 통해 연결되었지만 c 테이블이 조인되지 않는다면 5*2 = 10 행의 곱집합이 발생하게 된다.

 따라서 조건은 n개의 테이블을 사용한다고 가정할 시, 최소한 n-1 개 이상의 조건이 존재하여야 한다.

 

 student, exam_01, hakjum 테이블을 이용하여 각 학생의 학번, 이름, 학년, 성적, 학점을 조회하도록 하겠다.

 

 student 테이블은 학번, 이름, ID, 학년, 주민등록번호, 생년월일, 연락처, 키, 체중, 제1전공, 제2전공, 지도교수에 대한 정보로 구성되어 있다.

 

 exam_01 테이블은 학번, 성적 에대한 정보로 구성되어 있다.

 

 hakjum 테이블은 학점, 각 학점에 요구되는 최소 성적, 각 학점에 요구되는 최대 성적에 대한 정보로 구성되어 있다.

 

 따라서 student 테이블의 studno 열과 exam_01 테이블의 studno 열은 같은 구성으로 조인키를 쉽게 찾을 수 있으나, hakjum 테이블의 경우 exam_01 테이블의 성적을 hakjum 테이블에서 조건 하에 조인해야 한다.

  where 절에서 student 테이블과 exam_01 테이블의 조인키를 찾아 명시하였으며 ( s.studno = e.studno )

and 로 연결 후 exam_01 테이블의 성적이 hakjum 테이블 내 최소 성적 ~ 최대 성적 범주 내에 포함되는 학점을 조회하도록 하였다.

 

select s.studno as 학번, 
          s.name as 이름, 
          s.grade as 학년, 
          e.total as 성적, 
          h.grade as 학점
  from student s, exam_01 e, hakjum h
 where s.studno = e.studno 
           and e.total between h.min_point and h.max_point;

 

 

 세 개의 테이블이 하나로 조인되었음을 확인할 수 있다.