SQL - 조인

1. equi join - 두 테이블 사이에 서로 공통된 컬럼이 있을때 하는 조인 
 where 절에 증거를 줘야한다.
 
먼저, emp 테이블 말고 dept 테이블이 있는데 이를 확인하면..

select * from dept;

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

여기있는 deptno 가 emp 테이블과의 연결고리다.
emp 테이블에도 deptno가 있는 것을 확인해보라.

그럼 여기서,, 각 사원들의 근무지를 보려면

select ename, loc
 from emp, dept
 where emp.deptno=dept.deptno;

ENAME      LOC         
---------- -------------
SMITH      DALLAS      
ALLEN      CHICAGO     
WARD       CHICAGO     
JONES      DALLAS    
.......

select 절에는 보고싶은 것을 나열하고,
from 절에는 가져올 테이블들을 나열하고,
where 절에는 연결고리조건(증거)을 주면 된다.
증거는 최소한 테이블갯수-1 만큼 준다.

위의 쿼리를 보면 .연산자를 사용한 것을 볼 수 있는데
쓰는 방법은 테이블명.컬럼명 이다.

근데 위의 것을 퍼포먼스 차원(?) 에서 다시 써보면

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

from 절에 있는 테이블명 뒤에 앨리어스를 주고,
select 절에는 해당 앨리어스.컬럼명 으로 가져오도록 한다.
저렇게 정확히 명시를 해주면 좋아한다.
위의 쿼리처럼 ename 컬럼은 emp 테이블에 있고, loc는 dept 테이블에 있어서 테이블 명 또는
앨리어스를 안써도 됐지만 deptno 의 경우에는 양쪽 컬럼에 모두 있기 때문에..

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

쿼리를 실행을 시키면 '열의 정의가 애매합니다' 라는 메시지를 볼 수 있다.
d.deptno 라고 select 절에서 입력해주면 된다.

그럼 여기서 근무지가 DALLAS 은 사원들만 보여주려면..

select e.ename, d.loc
 from emp e, dept d
 where e.deptno=d.deptno and d.loc='DALLAS'

ENAME      LOC         
---------- -------------
SMITH      DALLAS      
JONES      DALLAS      
SCOTT      DALLAS      
.......

이렇게 and 를 써서 추가해주면 된다.
where 절에 있는 것을 구분할 수 있는데
처음에 있는 연결고리는 즉, 증거라고 볼수 있고, and 뒤에 있는 것은 조건이라고 볼 수 있다.

2. non equi join - 두테이블 사이에 서로 공통된 컬럼이 없을때
 where 절에 조건만 준다.
새로운 테이블을 하나 더 보면..

 select * from salgrade;
GRADE LOSAL HISAL
------------------------
1          700      1200
2         1201      1400
3         1401      2000
4         2001      3000
5         3001      9999

emp 테이블에서 sal 에 따라서 등급을 줘서 같이 출력하고 싶다.
사원 이름과 월급과 등급을 출력하려면..
emp와 salgrade 테이블에는 공통된 컬럼이 없다.

select e.ename, e.sal, s.grade
  from emp e, salgrade s
  where e.sal between s.losal and s.hisal;

이렇게 조건을 주게되면, 원하는 결과를 볼 수 있다.

ENAME    SAL    GRADE
--------------------------
SMITH       800         1
JAMES      950         1
ADAMS     1100        1
WARD       1250        2
MARTIN    1250        2
MILLER     1300       2
TURNER    1500       3
ALLEN      1600       3
CLARK      2450       4
BLAKE      2850       4
JONES      2975       4
SCOTT      3000       4
FORD        3000       4
KING         5000       5

3. outer join - 모자란 쪽에 (+)를 붙여준다.
뭐.. 예와 함께살펴보자.

부서테이블(dept) 에는 사원테이블(emp) 없는 부서가 있다.

 DEPTNO         DNAME           LOC         
---------- -------------- -------------
      40           OPERATIONS      BOSTON      

deptno 가 40번인 데이터다.

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

이렇게 해서 쿼리를 날리면 loc 가 BOSTON인 데이터를 볼 수 없다. emp 테이블에는 deptno가 40번은
사원이 없기 때문이다.
그래서, BOSTON을 출력하고 싶으면 outer join 을 쓰면 되는데, 모자란 쪽에 (+)를 붙인다.

select e.ename, d.loc
 from emp e, dept d
 where e.deptno(+)=d.deptno;

ENAME       LOC         
-----------------------------
SMITH        DALLAS      
ALLEN        CHICAGO     
WARD         CHICAGO     
JONES       DALLAS      
MARTIN      CHICAGO     
BLAKE       CHICAGO     
CLARK       NEW YORK    
SCOTT       DALLAS      
KING          NEW YORK    
TURNER      CHICAGO     
ADAMS       DALLAS      
JAMES       CHICAGO     
FORD         DALLAS      
MILLER      NEW YORK    
                 BOSTON      

(+) 는 오라클에서만 사용된다. 표준으로 사용하면(ANSI-JOIN)

select e.ename, d.loc
 from emp e right outer join dept d
 on e.deptno = d.deptno;

어디서건 표준을 사용하면 좋겠지만.. 일단 오라클이니까.. 훗!!
양쪽에 모자란 데이터가 있어서 둘다 출력하고 싶으면 (+)를 양쪽에 쓰면 되겠지 하고 생각하지만
(나만 그랬나 ㅡㅡ;),
양쪽에 (+)를 쓰게되면 에러가 출력된다. 'outer-join된 테이블은 1개만 지정할 수 있습니다' 이런 메시지..
그럼, 어떻게 하면 되는가.
표준으로 가자.. full outer join

select e.ename, d.loc
 from emp e full outer join dept d
 on e.deptno = d.deptno;
이렇게 하면 양쪽테이블에 없는 값들을 모두 출력할 수 있다.

4. self join - 자기 자신 테이블과 조인을 한다.
emp 테이블을 보면 mgr 이라는 컬럼이 있다. 이것은 해당 사원의 관리자번호이다.
그렇다면, 사원과 그 사원의 관리자를 같이 출력하고 싶다면..

select e.ename 사원 , m.ename 관리자
   from emp e, emp m
   where e.mgr = m.empno;

사원           관리자
-------------------
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

쿼리를 살펴보면, emp 테이블을 보는 관점을 사원테이블과 관리자 테이블로 나누면
증거는 이렇다. 사원테이블의 관리자 번호가 관리자 테이블의 사원번호와 같다 라고 해주면 된다.
조금만 생각해 보면 알 수 있다. ㅡ,.ㅡ;
KING 이 아무래도 대빵 같다. 그래서 관리자가 없다. KING 까지 같이 출력하고 싶으면..
모자란 쪽에 (+)..  outer join...
생각해라.. 관리자가 없다..

select e.ename 사원 , m.ename 관리자
   from emp e, emp m
   where e.mgr = m.empno(+);

사원              관리자
---------------------
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

KING 이 출력되는 것을 확인 할 수 있다.

5. cross join - 해당 경우를 다 조인한다.
수학시간에 열심히(?) 공부한 (으아~~) 경우의 수 구하는 거랑 같다.
주사위와 동전으로 나올 수 있는 경우의 수는?
주사위(6) * 동전(2) = 12 개.. ㅡ,.ㅡ;;
이처럼 cross join 도 같은 결과를 준다.

select e.ename, d.loc
    from emp e, dept d;

or

select e.ename, d.loc
  from emp e cross join dept d;

출력해 보면 마지막에 56 rows selected. 라는 결과를 볼 수 있다.
emp(14 rows) * dept(4 rows) = 56 rows 라는 결과.. ^^;

그 밖에 natural join 은 공통된 컬럼을 알아서 찾아서 join 해 준다.

select e.ename, d.loc
   from emp e natural join dept d;
이렇게 하면 emp테이블과 dept테이블에서 공통된 컬럼(deptno)로 알아서 조인해 준다.

공통된 컬럼이 여러개면 using( 컬럼명 ) 을 사용한다.

select e.ename, d.loc
  from emp e join dept d
  using (deptno);

주의사항 : 컬럼명에 테이블 앨리어스는 주지않는다. ex) e.deptno
select e.ename, d.loc
  from emp e join dept d
  using (d.deptno);
이렇게 앨리어스를 주게되면 '열명 그 자체만 사용할 수 있습니다' 라는 메시지가 출력된다.

조인은 뭐. 이렇게 대~충 살펴본 듯 한데.. 여러개를 조인 하고 싶으면

사원이름, 월급, 부서위치, 월급의 등급을 출력하고 싶다..
3개의 테이블을 조인해야겠지. emp, dept, salgrade  - 해보자.

select e.ename, e.sal, d.loc, s.grade
  from emp e, dept d, salgrade s
  where e.deptno=d.deptno and e.sal between s.losal and s.hisal;

ENAME       SAL         LOC                     GRADE
------------------------------------------------
SMITH         800          DALLAS                   1
JAMES        950          CHICAGO                 1
ADAMS      1100          DALLAS                   1
WARD        1250          CHICAGO                 2
MARTIN      1250         CHICAGO                 2
MILLER      1300          NEW YORK               2
TURNER     1500          CHICAGO                 3
ALLEN       1600          CHICAGO                 3
CLARK       2450          NEW YORK               4
BLAKE       2850          CHICAGO                 4
JONES       2975          DALLAS                   4
SCOTT       3000          DALLAS                   4
FORD         3000          DALLAS                   4
KING          5000          NEW YORK               5

위 결과를 ANSI 로 바꾸면 (이게 1999 SQL 문법인가? 잘 모르겠어 ㅡ,.ㅡ;;)

select e.ename, d.loc, s.grade
     from emp e join dept d
     on e.deptno = d.deptno
     join salgrade s
     on e.sal between s.losal and s.hisal;

이렇게 하면 동일한 결과를 얻을 수 있다.
이거저거 다 해보고 편한걸로 하면 좋지~ --;
너무 길었다.. 이궁~  조인 은근히 많군.. ㅡ,.ㅡ;

'SQL > 공부' 카테고리의 다른 글

문자형 데이터에서 엔터값 찾기  (0) 2008.04.15
메인쿼리-서브쿼리(Main Query - Sub Query)  (0) 2007.12.07
SQL 함수3  (2) 2007.11.29
SQL 함수2  (0) 2007.11.29
SQL - 함수  (0) 2007.11.26