Oracle 다중 테이블로부터의 데이타 검색

----   [A]        [B]
----  | 1 |        | a |   : A
----  | 1 | ---> | a |   : = [(1,a), (1,b) .... (2,a), (2,b) ....... (3,d)]
----  | 1 |        | a |   : ==> "데카르트곱"
----                 | a |   :
----    3    X     4     =  12개
select count(*)
from dept;

select count(*)
from EMP;

select *
from EMP, DEPT;  -- 모든경우의 수(cartesian product)가 다나오게 된다.

select *
from EMP, DEPT
where Emp.deptno = Dept.deptno; -- 이와 같이하면 같은 것끼리 연결이 된다.

/* select deptno, dname, ename, sal -- 이렇게 명시하면 deptno가 어떤테이블의 것인지 불확실해서 오류가난다. */
select E.deptno, D.dname, E.ename, E.sal -- 두 테이블에서 중첩이 없는 컬럼도 테이블명을 명시해주는 것을 권장.
from emp E, dept D                           -- 명시를 안하면 해당 컬럼을 검색하기 위해 두개의 테이블을
where E.deptno = D.deptno;               -- 모두 검색하나 명시하면 그것에 해당하는 테이블만 검색하게 된다.
--//// 바로위 문법은 SQL 1992 CODE, 바로 아래 문법은 SQL 1999 CODE(Oracle 9i 부터사용)
---- inner join (equi-join) : 중복되는 컬럼이있어도 출력값은 변함없다.
select E.deptno, D.dname, E.ename, E.sal
from emp E inner join dept D -- inner는 생략 가능하다.
on E.deptno = D.deptno;
--//// from emp E join dept D 는 from dept D join emp E 해도 무방하나
--//// from절에서 가장멀리있는 부분에 row가 적은것 또는 부모테이블을 적용시키는 것을 권장한다.
select E.deptno, D.dname, E.ename, E.sal -- E.DEPTNO를 D.deptno를 써도 무방하다.
from emp E, dept D
where E.deptno = D.deptno;
---- outer-join (+) : 중복되는 컬럼이 있으면 출력값이 달라진다. (애초에 부모컬럼(P.K)을 사용한다.)
select D.deptno, D.dname, E.ename, E.sal -- D.deptno를 E.DEPTNO로 쓰면 값이 달라진다.
from emp E, dept D
where E.deptno(+) = D.deptno;
--/// (+) 가 있는쪽을 다 보여주고 해당하는 값을 매핑한다.
/* sys계정으로 접속해서 실행해야한다. */
/* 다음 두문장(inner join)은 동일하다. 위는 1992년식, 아래는 1999년식이다. */
select D.department_id, E.FIRST_NAME
from hr.employees E, hr.departments D
where E.department_id = D.department_id;

select D.department_id, D.department_name, E.first_name
from hr.employees E left outer join hr.departments D   -- outer는 생략가능
on E.department_id = D.department_id;
--/// left는 왼쪽의 테이블을 다보여주고 매핑, right는 오른쪽에 테이블을 다 보여주고 매핑
/* 다음 두문장(outer join)은 동일하다. 위는 1992년식, 아래는 1999년식이다. */
select D.department_id, E.FIRST_NAME
from hr.employees E, hr.departments D
where E.department_id(+) = D.department_id;

select D.department_id, D.department_name, E.first_name
from hr.employees E right outer join hr.departments D   -- outer는 생략가능
on E.department_id = D.department_id;
--//////////////////////////////////////////////////////////////////////////////
select *
from hr.employees E full outer join hr.departments D   -- 정규화 하기 이전상태
on E.department_id = D.department_id;                 
------/// (+) left(right)가 없으면 inner(equi) join, 있으면 out join이다.
--/////////////////////////////////////////////////////////////////////////////--

---- non equi-join : 범위에 들어오는 값을 매핑시켜 준다.
------- 연습문제 : emp테이블에서 성명, 급여, 호봉
-------            단. 호봉은 1000미만이면 1호봉, 1000이상이면 2호봉, 2000이상이면 3호봉
-------            3000이상이면 4호봉, 4000이상이면 5호봉
select ename 성명, sal 급여,
    case when sal > 4000 then 5
            when sal >= 3000 then 4
            when sal >= 2000 then 3
            when sal >= 1000 then 2
            else 1
            end "호봉"
from EMP; -- 업데이트 할려면 소스에 써진 모든 값을 다 변경해야 해서 유지 보수가 힘들다.
/*
select *
from salgrade; */
--/// 위 문장과 동일하나 아래 문장은 테이블값만 변경하면 되기때문에 유지보수가 편하다. ///--
select E.ename, E.sal, G.GRADE
from emp E, SALGRADE G
where E.sal between G.losal and G.hisal;

---- self join :
select *
from EMP;

/* 1992년식 */
select E1.empno 사원번호, E1.ename 사원명, E1.job 직종, E2.EMPNO 관리자사원번호,
          E2.ENAME 관리자명, E2.JOB 관리자직종 
from emp E1, emp E2
where E1.mgr = E2.empno(+); -- E1에 mgr은 F.K가 되고 E2의 empno는 P.K가 된다.

/* 1999년식 */
select E1.empno 사원번호, E1.ename 사원명, E1.job 직종, E2.EMPNO 관리자사원번호,
          E2.ENAME 관리자명, E2.JOB 관리자직종 
from emp E1 left join emp E2
    on E1.mgr = E2.empno; -- E1에 mgr은 F.K가 되고 E2의 empno는 P.K가 된다.