[Oracle] 조인(Join) 정리

■ 작성일 : 2005년 5월
■ 작성자 : http://blog.naver.com/thescream
■ 저작권 : 고생해서 정리한다. 퍼가더라도 링크하나 남겨주면 고맙겠다.
■ 참조링크
■ 첨부파일 

 

본좌왈...

예전에 정리해 놓은 자료이다. 7,8년이 지난 시점에 간만에 join 명령어로 쿼리를 날릴 일이 발생했는데 역시 예전 정리된 문서를 한번 훑어 봐야 할 일이 생겼다. 역시 정리된 문서는 언젠가는 써먹힐때가 있다는...

 

 

조인(JOIN)이란?

하나이상의 테이블에서 각각의 원하는 엔티티를 뽑아낼때 쓰느 방법

 

조인조건

1. 조인조건은 where 절에 기술한다.

2. 똑같은 열 이름이 하나 이상의 테이블에 존재 한다면 모호성을 없애기 위해 열 이름앞에 테이블 이름을 붙여야 한다. - EMP테이블과 DETP테이블을 조인할때 EMP 테이블의 DEPTNO와 DETP테이블의 DEPTNO는 구별되어야 한다.

3. 중복되지 않는 열 이름이라 할지라도 열 이름 앞에 테이블 이름을 붙이면 오라클 서버에게 찾고자 하는 열의 정확한 위치를 알려주므로 성능이 향상된다.

4. 2개 이상의 테이블을 조인할 수도 있다.

 

조인방법

 

1. EQUIJOIN

 

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> select * from emp ORDER BY deptno;

 EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------ ---------- --------- ---------- -------- ---------- ---------- ----------
  7782 CLARK      MANAGER         7839 81/06/09       2450                    10
  7839 KING       PRESIDENT            81/11/17       5000                    10
  7934 MILLER     CLERK           7782 82/01/23       1300                    10
  7369 SMITH      CLERK           7902 80/12/17        800                    20
  7876 ADAMS      CLERK           7788 87/05/23       1100                    20
  7902 FORD       ANALYST         7566 81/12/03       3000                    20
  7788 SCOTT      ANALYST         7566 87/04/19       3000                    20
  7566 JONES      MANAGER         7839 81/04/02       2975                    20
  7499 ALLEN      SALESMAN        7698 81/02/20       1600        300         30
  7698 BLAKE      MANAGER         7839 81/05/01       2850                    30
  7654 MARTIN     SALESMAN        7698 81/09/28       1250       1400         30

 EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
------ ---------- --------- ---------- -------- ---------- ---------- ----------
  7900 JAMES      CLERK           7698 81/12/03        950                    30
  7844 TURNER     SALESMAN        7698 81/09/08       1500          0         30
  7521 WARD       SALESMAN        7698 81/02/22       1250        500         30

 

SQL> select emp.empno, emp.ename, emp.deptno, dept.dname
  2  from emp, dept
  3  where emp.deptno = dept.deptno AND SAL > 2000;

 EMPNO ENAME          DEPTNO DNAME
------ ---------- ---------- --------------
  7566 JONES              20 RESEARCH
  7698 BLAKE              30 SALES
  7782 CLARK              10 ACCOUNTING
  7788 SCOTT              20 RESEARCH
  7839 KING               10 ACCOUNTING
  7902 FORD               20 RESEARCH

6 개의 행이 선택되었습니다.

 

상단의 조인조건 2번항목에서 보는바와 같이 열의 이름 앞에 [table name].[열이름]의 형식을 사용. where절에서 두 테이블의 동일한 열에 대한 조인을 위해 굵은글자처럼 하였다.

 

2. TABLE ALIAS 사용법

테이블이 많고 열의 이름이 길면 작업하기 엿같다. 이를 간단하게 하는 방법이 별칭방법이다.

위의 구문이 아래와 같이 간결하게 표현된다.

 

SQL>  SELECT e.empno, e.ename, e.deptno, d.dname
  2   from emp e, dept d                           // 대표적으로 나타낼 별칭을 정의한다.
  3  where e.deptno = d.deptno;

 EMPNO ENAME          DEPTNO DNAME
------ ---------- ---------- --------------
  7369 SMITH              20 RESEARCH
  7499 ALLEN              30 SALES
  7521 WARD               30 SALES
  7566 JONES              20 RESEARCH
  7654 MARTIN             30 SALES
  7698 BLAKE              30 SALES
  7782 CLARK              10 ACCOUNTING
  7788 SCOTT              20 RESEARCH
  7839 KING               10 ACCOUNTING
  7844 TURNER             30 SALES
  7876 ADAMS              20 RESEARCH

 EMPNO ENAME          DEPTNO DNAME
------ ---------- ---------- --------------
  7900 JAMES              30 SALES
  7902 FORD               20 RESEARCH
  7934 MILLER             10 ACCOUNTING

14 개의 행이 선택되었습니다.

 

3. NON EQUIJOIN

 

emp테이블과 salgrade테이블의 항목은 서로 전혀 다르다.

 

SQL> SELECT * FROM SALGRADE;

     GRADE      LOSAL      HISAL
---------- ---------- ----------
         1        700       1200
         2       1201       1400
         3       1401       2000
         4       2001       3000
         5       3001       9999

 

서로다른 테이블을 조인할 경우 between 문장을 쓴다. equi조인처럼 '=' 문장을 쓰지 않는다.

 

SQL> select empno, ename, sal, grade from emp, salgrade
  2  where sal between losal and hisal;

 EMPNO ENAME             SAL      GRADE
------ ---------- ---------- ----------
  7369 SMITH             800          1
  7876 ADAMS            1100          1
  7900 JAMES             950          1
  7521 WARD             1250          2
  7654 MARTIN           1250          2
  7934 MILLER           1300          2
  7499 ALLEN            1600          3
  7844 TURNER           1500          3
  7566 JONES            2975          4
  7698 BLAKE            2850          4
  7782 CLARK            2450          4
  7788 SCOTT            3000          4
  7902 FORD             3000          4
  7839 KING             5000          5

14 개의 행이 선택되었습니다.

 

4. OUTER JOIN

 

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

위에서 보는 바와 같이 DEPTNO가 40번인 코드는 EMP테이블에 없다. 이럴경우 EQUI 조인이 안되는데도 불구하고(포린키가 EMP에 부족함) 어떤 결과를 출력하고 싶다면 아래와 같이 (+) 기호를 키가 부족한 테이블에 붙여 넣고 실행한다.

 

SQL> select e.empno, e.ename, d.deptno, d.dname from emp e, dept d
  2  where e.deptno(+) = d.deptno
  3  /

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7782 CLARK              10 ACCOUNTING
      7839 KING               10 ACCOUNTING
      7934 MILLER             10 ACCOUNTING
      7369 SMITH              20 RESEARCH
      7876 ADAMS              20 RESEARCH
      7902 FORD               20 RESEARCH
      7788 SCOTT              20 RESEARCH
      7566 JONES              20 RESEARCH
      7499 ALLEN              30 SALES
      7698 BLAKE              30 SALES
      7654 MARTIN             30 SALES

     EMPNO ENAME          DEPTNO DNAME
---------- ---------- ---------- --------------
      7900 JAMES              30 SALES
      7844 TURNER             30 SALES
      7521 WARD               30 SALES
                              40 OPERATIONS       // 40번 부서코드에 대한 값이 EMP테이블에 없다.

 

만일(+) 기호를 포린키가 많은 쪽으로 붙이면 EQUI 조인과 같은 결과가 출력되며

양쪽에 (+) 기호를 붙이면 에러가 난다. 절대로 붙일 수 없다.

 

5. SELF JOIN

 

SQL> SELECT * FROM emp;

 EMPNO ENAME      JOB              MGR HIREDATE        // MGR은 직속상관이다.
------ ---------- --------- ---------- -------- 
  7369 SMITH      CLERK           7902 80/12/17 
  7499 ALLEN      SALESMAN        7698 81/02/20 
  7521 WARD       SALESMAN        7698 81/02/22 
  7566 JONES      MANAGER         7839 81/04/02 
  7654 MARTIN     SALESMAN        7698 81/09/28 
  7698 BLAKE      MANAGER         7839 81/05/01 
  7782 CLARK      MANAGER         7839 81/06/09 
  7788 SCOTT      ANALYST         7566 87/04/19 
  7839 KING       PRESIDENT            81/11/17 
  7844 TURNER     SALESMAN        7698 81/09/08 
  7876 ADAMS      CLERK           7788 87/05/23 
  7900 JAMES      CLERK           7698 81/12/03 
  7902 FORD       ANALYST         7566 81/12/03 
  7934 MILLER     CLERK           7782 82/01/23

 

위의 테이블을 다음과 같이 분류할 수 있다.

EMP(WORKER) 와 EMP(MANAGER)로 구분할 수 있다.

위의 결과에서 내 상관은 누구다라고 분류를 지울수 있다.

위에처럼 자기 자신을 참조할 수 있는 모델을 리커시브 모델이라고 한다.

 

SQL> SELECT w.ename worker, m.ename manager
  2  FROM emp w, emp m
  3  WHERE w.mgr = m.empno;

WORKER     MANAGER
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

13 개의 행이 선택되었습니다.

 

아래와 같이 사장에 대한 상관은 없으므로 m.empno(+)로 아우터 조인을 시킨다.

 

SQL> SELECT w.ename worker, m.ename manager
  2  FROM emp w, emp m
  3  WHERE w.mgr = m.empno(+);

WORKER     MANAGER
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

14 개의 행이 선택되었습니다.

 

회사에서 사용된 셀프조인.

select a.cd_code, a.cd_desc, a.cd_rem, a.cd_ref1, b.cd_desc
from sycode a, sycode b
where
 (a.cd_class = 'CM001' and b.cd_class = 'CM002') and
 a.cd_ref1 = b.cd_code;

 

 

6. Cartesian Product

조인조건이 생략되거나 잘못된 경우 Cartesian Product가 발생된다.

조인되는 모든 테이블의 모든 행이 조인되는 경우 Cartesian Product가 발생한다.

Cartesian Product가 발생하면 잘못된 결과를 얻을 수 있으므로 where 절에 정확한 조인조건을 써야한다.

Cartesian Product를 의도적으로도 사용할 수 있다.

 

 

 [출처] https://m.blog.naver.com/thescream/169759220