[4장] JOIN(조인)

 

 

 

 

 

- 조인 : 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서

           결과를 보여줌.

 

 

 

1.​ Cartesian Product (카티션 곱) = CROSS JOIN

​

 - 카티션 곱 : Join 쿼리 중에 Where 절에 기술하는 join 조건이 잘 못 기술되었거나 아예 없을 경우에 발생하는 경우

 이런 경우에는 Join  작업에 참조되는 테이블 행수를 모두 곱한 값의 결과가 만들어짐

 

​ex) 사원 테이블과 부서 테이블을 join 해서 사원 이름과 부서 명을 출력하고 싶을 때 사원 테이블의 데이터 행이 10행이고

부서 테이블에 데이터가 5행이면 10X5, 결과 값은 50행이 출력 됨

​

​​Oracle Join 구문​
    ​SQL > SELECT e.name , d.dname
      2       FROM  emp e , dept d ;

​
      ​ANSI Join 구문​
​​        SQL > SELECT e.name , d.dname
          2       FROM  emp e  CROSS JOIN  dept d ;

 

​

 

 

2. EQUI Join (등가 조인)

 

 - 일반적으로 많이 사용되는 조인

    WHERE 절에 기술되는 Join 조건으로 양쪽 테이블에 같은 조건이 존재할 경우의 값만을 가져오는 Join

    Equal 연산자 (=) 를 사용해서 EQUI Join 이라고 함

 

예제1) 학생 테이블 (student) 과 학과 테이블 (department) 테이블을 사용하여 학생이름, 1 전공학과번호(deptno1) ,

               1전공 학과 이름을 출력하세요.

 

 

​​Oracle Join 구문​

SCOTT> SELECT s.name "학생이름" , s.deptno1 "학과번호", d.dname "학과이름"
     2       FROM student s , department d
     3       WHERE s.deptno1 = d.deptno ;

 

​ANSI Join 구문​

SCOTT> SELECT s.name "학생이름" , s.deptno1 "학과번호", d.dname "학과이름"
     2       FROM student s JOIN department d
     3       ON (s.deptno1 = d.deptno) ;

 

 

​

예제 1) 학생 테이블 (student) 과 교수 테이블 (professor) 을 join 하여 학생의 이름과 지도교수번호,

           지도교수 이름을 출력하세요.

 

 

 

​​​Oracle Join 구문​

SCOTT> SELECT s.name "학생이름" , s.profno "교수번호" , p.name "교수이름"
     2       FROM student s , professor p

     3       WHERE s.profno = p.profno ;

 

​ANSI Join 구문​

 SCOTT> SELECT s.name "학생이름" , s.profno "교수번호" ,p.name "교수이름"

     2       FROM student s JOIN professor p
     3       ON (s.deptno1 = p.profno );

​

​ [설명] 학생 테이블에 데이터는 총 20건인데 15건만 출력됨(즉, 5명의 학생이 안 나옴)

이것은 Equi join 의 특성으로 양쪽 테이블에 모두 데이터가 존재해야 결과가 나오는데 5명의 학생은 지도 교수가 결정이 안된 상태라서 결과에서 빠지게 된 것입니다.

한쪽이 없는 데이터까지 다 보고 싶다면 Outer Join 활용

​

​

​

​예제2) 학생 테이블(student)과 학과 테이블(department) , 교수 테이블(professor) 을 Join하여 학생의 이름과

          학과이름, 지도교수 이름을 출력하세요.

​

 

 

​​Oracle Join 구문​

SCOTT> SELECT s.name "학생이름" , d.dname "학과이름" , p.name "교수이름"
     2       FROM student s , department d , professor p

     3       WHERE s.deptno1 = d.deptno

     4       AND ​s.profno = p.profno ;

 

​ANSI Join 구문​

 SCOTT> SELECT s.name "학생이름" , d.dname "학과이름" , p.name "교수이름"

     2       FROM student s JOIN department d
     3       ON s.deptno1 = d.deptno

     4       JOIN professor p
     5       ON s.profno = p.profno ;​

​

​ [설명] Oracle Join 과 ANSI Join 의 문법이 많이 차이가 나는데 ANSI Join 의 경우는 쿼리의 2번과 3번행의 조건으로

     먼저 Join 을 수행 후 나온 결과 값을 가지고 4번과 5번 행의 조건으로 Join 을 수행하는 것입니다.

     Join 테이블이 더 늘어날 경우 계속 JOIN~ON 조건을 추가 하시면 됩니다.

 

 

 

 

 

 

3. Non-Equi Join (비등가 Join)

 

 - 같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 Join을 수행할 때 사용하는 것

 

 

예제1) Gogak 테이블과 gift 테이블을 Join하여 고객의 마일리지 포인트별로 받을 수 있는 상품을 조회 하여

          고객의 이름과 상품 명을 출력하세요.

 

​​Oracle Join 구문​

SCOTT> SELECT go.gname "고객명", go.point "POINT" , gi.gname "상품명"

     2       FROM gogak go , gift gi

     3       WHERE go.point BETWEEN gi.g_start AND gi.g_end ;

 

​ANSI Join 구문​

 SCOTT> SELECT go.gname "고객명", go.point "POINT" , gi.gname "상품명"

     2       FROM gogak go JOIN gift gi
     3       ON go.point BETWEEN gi.g_start AND gi.g_end ;

 

​

 

 

예제2) 위 예제 1번에서 조회한 상품의 이름과 필요 수량이 몇 개 인지 조회하세요.

 

 

​​Oracle Join 구문​

SCOTT> SELECT gi.gname "상품명" , COUNT(*) "필요수량"

     2       FROM gogak go , gift gi

     3       WHERE go.point BETWEEN gi.g_start AND g_end ;

     4       GROUP BY gi.gname ;

 

​ANSI Join 구문​

 SCOTT> SELECT gi.gname "상품명" , COUNT(*) "필요수량"

     2       FROM gogak go JOIN gift gi
     3       ON go.point BETWEEN gi.g_start AND gi.g_end ;

     4       GROUP BY gi.gname ;​

 

 

 

예제3)  Emp 2 테이블과 p_grade 테이블을 조회하여 사원들의 이름과 나이, 현재 직급 , 예상 직급 을 출력하세요.

   예상 직급은 나이로 계산하며 해당 나이가 받아야 하는 직급을 의미합니다.
   나이는 오늘(sysdate) 를 기준으로 하되 trunc 로 소수점 이하는 절삭해서 계산하세요.

 

 

​​Oracle Join 구문​

SCOTT> SELECT e.name "이름",

     2                   trunc((sysdate-e.birthday)/365,0) "현재나이" ,

     3                   e.position "현재직급" ,​

​     4                   g.position "예상직급"

​     5       FROM emp2 e , p_grade g
     6       WHERE trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ;

​

​ANSI Join 구문​

 SCOTT> SELECT e.name "이름",
      2                   trunc((sysdate-e.birthday)/365,0) "현재나이" ,
      3                   e.position "현재직급" ,
      4                   g.position "예상직급"
      5       FROM emp2 e JOIN p_grade g
      6       ON trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ;

 

 

 

4. OUTER Join (아우터 조인)

 

- INNER JOIN(이너 조인) : 위에서 살펴본 equi join , non-equi join 은 Join에 참여하는 모든 테이블에 데이터가 존재하는

                                     경우에만 결과 값을 출력했습니다.

 

- Outer Join :  Inner Join과는 반대로 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽

                     테이블의 내용을 전부 출력하게 하는 방법입니다.

 

 ※ DB 성능에 아주 나쁜 영향을 줄 수 있다는 것을 명심하고 주의

 

 

 

예제1) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력하세요.
          단 지도교수가 결정되지 않은 학생의 명단도 함께 출력하세요.

 

​​Oracle Join 구문​

SCOTT> SELECT s.name "학생이름", p.name "교수이름"

​     2       FROM student s, professor p
     3       WHERE s.profno = p.profno(+) ;

 

​ANSI Join 구문​

 SCOTT> SELECT s.name "학생이름", p.name "교수이름"

      2       FROM FROM student s LEFT OUTER JOIN professor p
      3       ON s.profno = p.profno ;

​

[설명] Oracle Outer Join 구문은 Where 조건절에서 데이터가 없는 쪽에 (+) 표시를 추가해 주면 됩니다.

    위 SQL 문장에서 3번 줄을 보면 조건 중에 교수테이블의 교수 번호 쪽에 (+) 기호가 있습니다.
    즉, 학생은 존재하지만 교수가 없는 내용을 출력해야 하기에 교수 조건 쪽에 (+) 기호를 붙인 것 입니다.

 

 

 

 

예제2) Student 테이블과 Professor 테이블을 Join하여 학생이름과 지도교수 이름을 출력하세요.
          단 지도학생이 결정 안 된 교수 명단과 지도 교수가 결정 안된 학생 명단을 한꺼번에 출력하세요.

 

 

 

​​Oracle Join 구문​

SCOTT> SELECT s.name "학생이름", p.name "교수이름"
​     2       FROM student s , professor p
     3       WHERE s.profno(+) = p.profno
     4       UNION
     5       SELECT s.name "학생이름", p.name "교수이름"
     6       FROM student s , professor p
     7       WHERE s.profno = p.profno(+) ;

 

​ANSI Join 구문​

 SCOTT> SELECT s.name "학생이름", p.name "교수이름"

      2       FROM FROM student s FULL OUTER JOIN professor P

      3       ON s.profno = p.profno ;

 

 

 

 

5. SELF Join

 

 - SELF Join : 원하는 데이터가 하나의 테이블에 다 있을 경우에 사용하는 Join

 

 

예제1) 여러 테이블을 Join 해야 하는데 만약 원하는 데이터가 하나의 테이블에 다 들어있는 경우에는?!

          하나의 테이블을 메모리상에서 별명을 두 개로 사용해서 가상으로 2개의 테이블로 만든 후 Join 작업을 수행

 

 

​​Oracle Join 구문​

SCOTT> SELECT a.dname “부서명” , b.dname “상위부서명”
     2       FROM dept2 a , dept2 b
     3       WHERE a.pdept = b.dcode ;

 

ANSI Join 구문​

 SCOTT> SELECT a.dname “부서명” , b.dname “상위부서명”
      2       FROM dept2 a JOIN dept2 b
      3       ON a.pdept = b.dcode ;

 

[설명] 위 쿼리에서 2번 행의 FROM 절 처럼 dept2 테이블의 별명을 a 와 b 로 두 번 사용 하게 되면

          메모리에서 오라클이 이 테이블을 별명을 다르게 해서 위 그림처럼 복사를 하게 됩니다.

 

 

 

 

예제2) professor 테이블에서 교수의 번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 출력하세요.

         단 자신보다 입사일이 빠른 사람수를 오름차순으로 출력하세요.

 

 

Oracle Join 구문​

SCOTT> SELECT a.profno "교수번호" ,
     2                   a.name "교수명" ,
     3                   a.hiredate "입사일" ,
     4                   COUNT(b.hiredate) "빠른사람"  
     5      FROM professor a , professor b
     6      WHERE b.hiredate(+) < a.hiredate
     7      GROUP BY a.profno, a.name, a.hiredate
     8      ORDER BY 4 ;
 

 

 

 

6. JOIN 원리

 

 (1) Nested Loop Join (가장 기본적인 Join 기법)

 

SQL> SELECT e.ename , d.dname

  2     FROM emp e , dept d

  3     WHERE e.deptno = d.deptno ;

 

 

 

1. 사원 테이블과 부서 테이블을 메모리(데이터베이스 버퍼 캐시)로 복사해옴

2. 그 후에 사원 테이블에서 사원 이름을 꺼내서 임시 작업 공간으로 가져 감,

    인덱스 상황이나 다른 요소에 따라 작업 순서가 변경될 수도 있음.

3. 그 후에 부서 테이블에서 해당 부서명을 찾으러 가는데 그 때 위의 SQL의 3행에 있는 조건을 보고 해당 조건에 맞는

   데이터를 찾아서 부서명을 가져옴

4. 한 행의 작업이 끝나면 다시 처음 테이블로 돌아가서 두 번째 행의 이름을 다시 PGA로 가져옴

5. 다시 부서 테이블에 가서 두 번째 행의 부서번호와 동일한 부서번호를 가진 부서명을 꺼내 옴

 

 - 위와 같은 과정을 계속 반복해서 먼저 읽었던 사원 테이블의 데이터가 끝이 날때까지 작업이 반복(Loop)됨,

    그래서 이 Join을 Nested Loop Join이라고 하며, 모든 조인의 기본이 되는 Join.

 

 

- Join은 몇번 수행되는가?  : 먼저 읽는 테이블의 행 수만큼 Join이 수행된다.

 

- Driving Table(선행 테이블) : 먼저 읽는 테이블

- Driven Table(후행 테이블) : 나중에 읽는 테이블

 

- Join의 성능은 어떤 테이블을 선행 테이블로 설정하는가가 중요함

   이것을 결정해 주는 오라클 내부 구성 요소가 옵티마이저이다.

 

 

 

 (2) Sort-Merge Join

 

SQL> SELECT e.ename , d.dname

  2     FROM DEPT d , EMP e

  3     WHERE e.deptno = d.deptno ;

 

- 위와 같은 SQL 을 수행하게 되면 오라클은 emp 테이블의 Smith 를 가져오고 Smith의 Dname 을 찾으러 dept 테이블로

   가게 됩니다. 그런데 dept 테이블에 데이터가 많아서 어떤 dname 을 가져와야 할지 모르기 때문에 SQL 문장의

   Where 절에 있는 조건을 보고 그 조건에 맞는 dname 을 가져오는 것입니다.

- 즉 SQL 문장에서 Where 절에 잘못된 조건을 줄 경우나 조건을 안 줄 에는 올바른 데이터를 가져 오지 못한다는 뜻이 됩니다.

  (이런 경우 모든 데이터를 다 가져오게 되며 카티션 곱이라고 합니다)

 

- 오라클 입장에서는 빨리 dept 테이블을 조회해서 사용자가 원하는 데이터를 찾아서 돌려주길 원할 것입니다.

   그런데 만약 dept 테이블에 데이터가 1억 건이 있다라고 가정하면 오라클은 Smith의 dname 을 찾기 위해 1억 건을 읽어

   본 후 적당한 데이터를 가져와야 합니다. (→ 성능 저하)

 

- Join과 더불어 필수적으로 언급되는 것이 바로 인덱스!

 

- 인덱스 : '어떤 데이터가 어디에 있다'라는 주소록 같은 개념

 

 

 

 (3) Hash Join (CBO에서만 가능)

 

 - Hash Join : 양쪽 테이블 모두 Join 컬럼에 인덱스가 없을 경우에 과거의 Sort-Merge Join이 시간이 너무 오래 걸린다는

   단점을 보완해서 만들어진 Join 방법

 

▶ 두 개의 테이블에 인덱스가 없는 상황에서 Join을 수행 한다면 아래와 같은 순서로 진행

1. 두 테이블 중에서 범위가 좁은 테이블을 메모리(PGA 내부의 Hash Area)로 가져옴

2. Join 조건 컬럼의 데이터를 Hash 함수에 넣어서 나온 Hash Value 값으로 Hash Table을 생성

3. 후행 테이블의 Join 조건을 Hash 함수에 넣어서 Hash Value를 생성하고 이 값을 선행 테이블의 Hash Table의 값과

   비교하여 같은 값이 있으면 해당 컬럼의 값을 매칭.

 

 

 

 

서진수,『오라클 SQL과 PL/SQL』, 생능출판사, 2012, pp.148-176