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 절에 조건만 준다. 새로운 테이블을 하나 더 보면..
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); 이렇게 앨리어스를 주게되면 '열명 그 자체만 사용할 수 있습니다' 라는 메시지가 출력된다.
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 - 조인
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 > 공부' 카테고리의 다른 글