Oracle DB6_ 고유키,외래키,Join

Goals

• 고유 키(PK : Primary Key), 외래 키(FK : Foreign Key)
• Cross Join, Join (Equi Join), Non - Equi JOIN
• Oracle vs ANSI 

Notes

고유 키(PK : Primary Key) : 

숫자 하나를 알면 오직 하나의 record를 검색할 수 있다.

어느 테이블에서 고유 키가, 항상 다른 테이블에서도 고유 키인 것은 아니다.

외래 키(FK : Foreign Key) :

다른 테이블의 고유 키(PK)가 되는 칼럼이 외래 키이다.

크로스 조인 (테이블의 곱셈) :

두 테이블 칼럼 값의 조합으로 모든 경우의 수를 보여주는 것.

>> Oracle 방식의 Cross Join ( Cartesian Product )

select *

from emp, dept ;

>> 표준 방식 (ANSI) Cross Join

select *

from emp cross join dept; ( 표준에서는 Cross 라는 조인 조건을 명시하여야 함)

Join (Equi Join)

Oracle JOIN: 

>> SELECT 테이블1.컬럼1, 테이블2.컬럼2 FROM 테이블1, 테이블2 WHERE 조건; 

  ■ SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno; 

select *

from emp e, dept d

where e.deptno = d.deptno

order by e.emptno;

ANSI JOIN: 

>>SELECT 테이블1.컬럼1, 테이블2.컬럼2 FROM 테이블1 JOIN 테이블2 ON 조건;       ON 뒤에 JOIN에 대한 조건을 삽입.

 ■ SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

select *

from emp e join dept d

on e.deptno = d.deptno;

order by e.empno;

Non-Equi Join ( '='가 아닌 방식으로 두 테이블을 비교 )

-- EMP 테이블과 SALGRADE 테이블에서

-- 사번 이름 급여 급여등급 출력

Oracle

select e.empno, e.ename, e.sal, s.grade

from emp e , salgrade s

where e.sal between s.losal and s.hisal

order by e.sal;

ANSI

select  e.empno, e.ename, e.sal, s.grade

from emp e join salgrade s

on e.sal >= s.losal and e.sal <= s.hisal

order by e.sal;

-- 3개 이상의 테이블 조인시

Oracle : From 절에 테이블을 나열, Where 절에 (n-1)개의 조건을 삽입

select e.empno, e.ename, d.deptno, d.dname, e.sal, s.grade

from emp e , salgrade s, dept d

where e.sal >= s.losal and e.sal <= s.hisal and d.deptno = e.deptno

order by e.sal;

ANSI Join ~ On ~ Join ~ On ~ 반복

select  e.empno, e.ename, d.deptno, d.dname, e.sal, s.grade

from emp e join salgrade s on e.sal >= s.losal and e.sal <= s.hisal join dept d on d.deptno = e.deptno

order by e.sal;

-- 자기 자신을 조인하는 경우

-- 사번, 이름, 매니저 사번, 매니저 이름 출력

Oracle

select e.empno, e.ename, m.empno, m.ename

from emp e, emp m

where e.mgr = m.empno;

ANSI

select e.empno, e.ename, m.empno, m.ename

from emp e 

join emp m on e.mgr = m.empno;

Join : 두 개의 테이블이 공통으로 가지고 있는 정보를 보여줌.

Outer Join : 공통으로 가지고 있지는 않은 정보도 모두 보여주고 싶음.

왼쪽 것을 출력하면 Left (Outer) Join, 오른 것을 출력하면 Right (Outer) Join 이다.

-- Oracle

select e.empno, e.ename, d.deptno, d.dname --> e.deptno가 아니라 d.deptno를 해주어야 한다

from emp e, dept d

where  e.deptno (+) = d.deptno;  --> Right Join

where  e.deptno = d.deptno (+); --> Left Join

(+) : Null 을 추가함 -- 으로 외우자. e.deptno에 (+)널 값이 생성된다.

--ANSI

select  e.empno, e.ename, e.deptno, d.dname

from emp e right join dept d                     ---->   from emp e left join dept d

    on e.deptno = d.deptno;

※ from과 where, on에서 테이블의 순서를 바꾸면 다른 결과가 나온다.

Full outer join

-- full join은 ANSI 방식의 문법만 있고, Oracle 방식의 문법은 없음.

-- Oracle 방식에서는 Union 연산자를 활용해야 한다.

select e1.empno, e1.ename, e2.empno, e2.ename

from emp e1 full join emp e2

on e1.mgr = e2.empno;

Exercise_05

3) emp, dept, salgrade 테이블에서

부서번호, 부서이름, 사번, 이름, 매니저사번, 매니저 이름, 급여, 급여 등급을 출력.

단, 모든 부서와 모든 사원이 출력되어야 함. 부서번호 오름차순 출력.

(결과 예시)

deptno  dname           empno   ename   mgr    mgr_name sal    grade

--------------------------------------------------------------------

10    ACCOUNTING    7934    MILLER    7782    CLARK    1300    2

10    ACCOUNTING    7839    KING            5000    5

...

40    OPERATIONS            

ANSI

1 select d.deptno, d.dname, e.empno, e.ename, e.mgr, m.ename "mgrName", e.sal, s.grade

2 from emp e full join  dept d on e.deptno = d.deptno                                 좌측과 우측을 full join ==> 테이블 생성

3                    left   join emp m on e.mgr = m.empno                                     윗줄을 실행해 만들어진 테이블과 우측을 left join

4                    left   join salgrade s on e.sal between s.losal and s.hisal          윗줄을 실행해 만들어진 테이블과 우측을 left join

5 order by deptno asc;

Oracle

1 select d.deptno, d.dname, e.empno, e.ename, e.mgr, m.ename "mgrName", e.sal, s.grade

2 from emp e , dept d , emp m , salgrade s

3 where d.deptno = e.deptno(+) and                                                좌측의 정보를 보존 ==> 테이블 생성

4           e.mgr = m.empno(+) and                                                      새로운 테이블 : m.empno ==> 기존 테이블의 정보를 보존

5           e.sal between  s.losal(+) and s.hisal(+)                                새로운 테이블 : ㄴ>데이터가 없는 부분은 null로 추가하겠다.

6 order by deptno asc;