DB/Oracle
Oracle DB6_ 고유키,외래키,Join
Codezoy
2019. 9. 22. 14:44
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;