[SQL] 오라클 전용 Join

◆ 조인의 개념
두 개 이상의 테이블을 결합하여 필요한 데이터를 조회하게 하는 기능.
예를 들어 학번이 10101인 학생의 이름과 소속 학과 이름을 출력하려 할 때 student table과 department table을 두 번 조회해야 하지만 조인을 이용하면 한번에 조회가 가능하다.


◆ 조인의 종류
카티션 곱(Cartesian product, cross join), Equi join, Non-equi join, Outer join, Self join 등


◆ 조인 문법

SQL> select table1.column, table2.column
   from table1, table2
   where join_condition;

A = 빵가게
B = 과자가게

select A.빵, B.짱구
   from 빵가게 A, 과자가게 B
   where 조건;

즉 조인의 핵심은 where 조건에 무엇을 주는 지이다.

---

내가 100장 분량의 Report.hwp파일에서 2페이지만을 수정하고 싶을 때 2페이지만 열기가 가능한가? 결론은 안된다. 100장 분량의 Report.hwp파일을 연 후 2페이지로 가서 수정을 해야만 한다.
오라클도 마찬가지로 여러 컬럼이 있는 테이블에서 두 개의 컬럼만 뽑아낼려고 하면 두 개의 컬럼만 가져오는 것이 아니라 테이블 전체를 하드에서 메모리로 다 가져와서 두 개의 컬럼만 보여주는 식으로 동작한다.

이에 자주 사용자들이 요구하는 테이블은 작게 쪼개놓는 작업을 하곤 하는데 이를 정규화(Normalization)라 한다.
이와는 반대로 쪼개진 테이블들을 다시 뭉치는 작업을 역정규화(DeNormalization)라 한다.

동아전과 = 전과목(역정규화)
표준전과 = 과목별 분리(정규화)

정규화가 무조건 좋은 것만은 아니다.
예를 들어 우리가 대형가게에서 물건을 사는 것이 편할까? 아니면 작은 가게 여러 군데서 사는 것이 편할까?
즉, 요점은 경우에 따라 정규화 또는 역정규화가 구분되어 행해져야 한다는 것이다.

---

가상의 메모리공간(조인 시 사용하는 곳)
PGA(Runtime area) : 대용량에서는 Array라 부름.

조인을 걸었을 때 속도가 떨어지는 원인은 정규화가 잘못 되었다든지, 인덱스 정책이 잘못 되어서이다.
참조되는 컬럼에는 인덱스가 있어야 속도가 붙는다.

조인을 걸 때 먼저 읽는 테이블의 데이터(여기서 데이터라는 것은 테이블의 건수가 적은 것이 아니라 내가 찾고자 하는 데이터가 적은 것)가 적은 것이 먼저 와야 한다.

'100만건 중 3건'과 '100건 중 10건'이 있다면 '100만건 중 3건'을 먼저 기술해야 한다.(퍼센트가 적은 것)

조인의 기본적인 원리 : Nested Loop Join(NL Join)

조인의 종류에는 오라클에서만 작동하는 '오라클 전용 조인'이 있고, DBMS 구분없이 모두 공통적으로 작동하는 'ANSI 조인'이 있다.

오라클을 정말 잘하고 싶으면
1. 옵티마이저를 잘 알아야 함(오라클 비중이 100%라면 그 중 옵티마이저가 90%라고 할 정도)
2. 조인
3. 인덱스 제어
4. 정렬 제어

---

◆ 카티션 곱(Cartesian Product, Cross Join)
두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합하는 조인방법.
where절에서 조인 조건절을 생략하거나 조인 조건을 잘못 설정하여 양쪽 테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우에 발생한다.


◆ Equi Join
SQL문에서 가장 많이 사용되는 조인.
조인 대상 테이블에서 공통 컬럼을 '=' 비교를 통해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법.

SQL> select table1.column, table2.column
   from table1, table2
   where table1.column = table2.column;

[문제] 학생이름, 학과이름, 지도교수이름을 출력하시오.(지도교수가 없는 학생은 제외)
SCOTT> select  s.name "학생이름",
  2   d.dname "학과이름",
  3   p.name "지도교수이름"
  4   from student s, department d, professor p
  5   where s.profno = p.profno
  6  and s.deptno = d.deptno

학생이름   학과이름     지도교수이름
---------- ---------------- --------------------
류민정    컴퓨터공학과     전은지
이광훈    컴퓨터공학과     성연희
임유진    컴퓨터공학과     전은지
지은경    컴퓨터공학과     전은지
김영균    컴퓨터공학과     이만식
전인하    컴퓨터공학과     성연희
윤진욱    멀티미디어학과   권혁일
오유석    멀티미디어학과   권혁일
김진영    멀티미디어학과   권혁일
김진경    전자공학과     이재우

10 rows selected.

[문제] 학생이름, 학과이름, 지도교수이름을 출력하시오.(지도교수가 없는 학생 포함)
SCOTT> select  s.name "학생이름",
  2   d.dname "학과이름",
  3   p.name "지도교수이름"
  4   from student s, department d, professor p
  5   where s.profno = p.profno(+)
  6  and s.deptno = d.deptno

학생이름   학과이름     지도교수이름
---------- ---------------- --------------------
김진경    전자공학과     이재우
이광훈    컴퓨터공학과     성연희
전인하    컴퓨터공학과     성연희
윤진욱    멀티미디어학과   권혁일
오유석    멀티미디어학과   권혁일
김진영    멀티미디어학과   권혁일
김영균    컴퓨터공학과     이만식
류민정    컴퓨터공학과     전은지
임유진    컴퓨터공학과     전은지
지은경    컴퓨터공학과     전은지
조명훈    전자공학과
서재진    컴퓨터공학과
하나리    멀티미디어학과
박동진    전자공학과
박미경    컴퓨터공학과
이동훈    전자공학과

16 rows selected.

◆ Non-Equi Join
조인 조건에서 '=' 조건이 아닌 다른 종류의 연산자(크거나 같다 또는 Between 연산자 등)를 사용하는 조인 방법.

학생테이블

학번	이름	점수
100	A	95
101	B	87
102	C	97

학점테이블

Low	High	학점
96	100	A+
90	95	A0
86	89	B+
80	85	B0

내가 뽑고 싶은 것

이름	학점
A	A0
B	B+
C	A+

이것을 오라클에게 어떻게 시킬 것인가?

select 학생.이름, 학점.학점
   from 학생, 학점
   where 학생.점수 between 학점.Low and 학점.High;

[문제] Test13, Test14 테이블을 사용하여
1) 고객별로 받을 수 있는 상품을 출력하고
SCOTT> select t14.cust cust, t13.gift gift
  2   from test13 t13, test14 t14
  3   where t14.point between t13.fpoint and t13.tpoint;

CUST        GIFT
-------------- --------------------
771201-2233445 COMPUTER
620908-2121232 COMPUTER
500823-1132762 AUDIO
672102-2123452 의류교환권
650207-1765152 굴비세트
680801-1234455 갈비세트
701212-1143211 PCS무료가입권
710125-1144951 구두상품권
721109-2144952 녹차세트
721101-2144952 도서상품권

10 rows selected.

2) 각 상품이 몇 개가 필요한 지 출력하시오.
SCOTT> select t13.gift gift, count(t14.cust)
  2   from test13 t13, test14 t14
  3   where t14.point between t13.fpoint and t13.tpoint
  4  group by t13.gift

GIFT                        COUNT(T14.CUST)
-------------------- ---------------
COMPUTER                                   2
의류교환권                                     1
굴비세트                                        1
갈비세트                                        1
PCS무료가입권                               1
구두상품권                                     1
AUDIO                                           1
녹차세트                                        1
도서상품권                                     1

9 rows selected.

3) 고객의 포인트보다 낮은 모든 모든 등급의 상품을 선택할 수 있다고 할 때 갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트, 선물을 보여주는 쿼리를 작성하시오.
SCOTT> select  t14.cust cust,
  2   t14.point point,
  3   t13.gift gift
  4   from test13 t13, test14 t14
  5   where t14.point >= t13.fpoint
  6  and t13.gift = '갈비세트'

CUST      POINT GIFT
-------------- ---------- --------------------
650207-1765152  20239650 갈비세트
672102-2123452  40935040 갈비세트
500823-1132762  75636408 갈비세트
771201-2233445 129855120 갈비세트
680801-1234455  10059470 갈비세트
620908-2121232 123674200 갈비세트

6 rows selected.


SCOTT> select  t14.cust cust,
  2   t14.point point,
  3   case when t14.point>=10000001 then '갈비세트' end gift
  4   from test13 t13, test14 t14
  5   where t14.point between t13.fpoint and t13.tpoint
  6  and t14.point >= 10000001

CUST      POINT GIFT
-------------- ---------- --------------------
680801-1234455  10059470 갈비세트
650207-1765152  20239650 갈비세트
672102-2123452  40935040 갈비세트
500823-1132762  75636408 갈비세트
620908-2121232 123674200 갈비세트
771201-2233445 129855120 갈비세트

6 rows selected.

[문제] temp 테이블의 사람 중 emp_level 테이블에서의 부장직급을 받아야 할 나이를 가지고 있는 사람의 사번, 성명, 생일, 현재나이, 현재직급을 출력하시오.
SCOTT> select t.emp_id "사번",
  2   t.emp_name "성명",
  3   to_char(t.birth_date, 'YYYY-MM-DD') "생일",
  4   round(((sysdate-t.birth_date)/365)+0.5) "현재나이",
  5   t.lev "현재직급"
  6   from temp t, emp_level e
  7   where round(((sysdate-t.birth_date)/365)+0.5) between e.from_age and e.to_age
  8  and e.lev = '부장'

      사번 성명       생일    현재나이 현재직급
---------- ---------- ---------- ---------- ----------
  19970101 김길동     1974-01-25  37 부장
  19960101 홍길동     1973-03-22  38 과장
  19970201 박문수     1975-04-15  36 과장
  19930331 정도령     1976-05-25  35 차장
  19950303 이순신     1973-06-15  38 대리
  19966102 지문덕     1972-07-05  39 과장
  19930402 강감찬     1972-08-15  39 차장
  19960303 설까치     1971-09-25  39 사원
  19970112 연흥부     1976-11-05  34 대리
  19960212 배뱅이     1972-12-15  38 과장
  20000101 이태백     1980-01-25  31 수습
  20000102 김설악     1980-03-22  31 수습
  20000203 최오대     1980-04-15  31 수습
  20000334 박지리     1980-05-25  31 수습
  20000305 정북악     1980-06-15  31 수습
  20006106 유도봉     1980-07-05  31 수습
  20000407 윤주왕     1980-08-15  31 수습

17 rows selected.

SCOTT> select t.emp_id "사번",
  2   t.emp_name "성명",
  3   to_char(t.birth_date, 'YYYY-MM-DD') "생일",
  4   trunc(months_between(sysdate, t.birth_date)/12) "현재나이",
  5   t.lev "현재직급"
  6   from temp t, emp_level e
  7   where trunc(months_between(sysdate, t.birth_date)/12) between e.from_age and e.to_age
  8  and e.lev = '부장'

      사번 성명       생일    현재나이 현재직급
---------- ---------- ---------- ---------- ----------
  19970101 김길동     1974-01-25  36 부장
  19960101 홍길동     1973-03-22  37 과장
  19970201 박문수     1975-04-15  35 과장
  19930331 정도령     1976-05-25  34 차장
  19950303 이순신     1973-06-15  37 대리
  19966102 지문덕     1972-07-05  38 과장
  19930402 강감찬     1972-08-15  38 차장
  19960303 설까치     1971-09-25  38 사원
  19970112 연흥부     1976-11-05  33 대리
  19960212 배뱅이     1972-12-15  37 과장

10 rows selected.

◆ Outer Join
지금까지의 조인은 양쪽 모두에 값이 있어야 결과가 나왔지만, 아웃조인은 한쪽에 값이 없어도 결과가 나온다.
다시 말해서 조인 조건 중 하나의 값이 널일 경우 값이 널이 되어 출력이 되지 않으나, 결과가 널일지라도 출력해야 할 경우가 발생하는데 이때 사용되는 것이 Outer Join이다.

널이 출력되는 컬럼에 (+)기호를 추가하면 된다.
예를 들어 학생 테이블과 교수 테이블을 조인할 때 지도 교수가 배정되지 않은 학생의 명단(즉, 지도교수 값이 널인 명단)도 출력해야 할 경우 교수 테이블에 (+) 기호를 추가한다.
하지만 (+) 기호를 양쪽 테이블에 동시에 사용할 수는 없다.(대신 ANSI 조인의 Full Outer Join을 쓰면 해결된다.)
ANSI의 Outer Join과 오라클의 Outer Join의 (+) 연산자 표시 방향은 서로 반대임을 주의하자.

Outer Join의 단점은 where절에서 (+)가 붙은 테이블은 무조건 전부 다 읽는다는 것이 치명적이다. 이는 쿼리의 성능에 영향을 많이 주는 행위이다.
현장에 나가면 NVL함수와 Outer Join이 도배가 되어있는 것을 볼 수 있는데, 성능이 좋지 않은 쿼리들이다.
이는 쿼리를 그렇게 짠 개발자의 문제가 아니라 해당 테이블 설계 자체에 문제가 있는 것이다.

사원테이블

사번	이름	부서번호
100	A	1000
101	B	2000
102	C	4000

부서테이블

번호	부서명
1000	영업
2000	생산
3000	관리

위의 두 테이블에서 Outer Join을 걸면 어느 쪽에 (+)를 붙여야 할까?

where 사원.부서번호 = 부서.번호(+)

◇ Outer Join의 제약사항
  - 연산자(+)는 널이 존재하는 컬럼 쪽에 표시한다.(지도교수가 없는 학생 출력시 지도교수값이 널임)
  - Outer Join에서는 IN 연산자를 사용할 수 없다.
  - 다른 조건과 OR 연산자로 결합할 수 없다.

[문제] temp테이블과 emp_level 테이블을 조인하여 각 사원의 사번, 이름, 직급, 현재연봉, 해당 직급의 연봉의 상,하한금액을 보고자 한다. 단 연봉의 상,하한이 결정 안된 수습사원은 사번, 이름, 직급, 현재연봉만 나오면 된다.
SCOTT> select t.emp_id "사번",
  2   t.emp_name "이름",
  3   t.lev "직급",
  4   t.salary "현재연봉",
  5   e.from_sal "연봉하한",
  6   e.to_sal "연봉상한"
  7   from temp t, emp_level e
  8  where t.lev = e.lev(+)

      사번 이름       직급   현재연봉 연봉하한   연봉상한
---------- ---------- ---- ---------- ---------- ----------
  19960303 설까치     사원   35000000 30000000   40000000
  19970112 연흥부     대리   45000000 35000000   60000000
  19950303 이순신     대리   56000000 35000000   60000000
  19960212 배뱅이     과장   39000000 37000000   75000000
  19966102 지문덕     과장   45000000 37000000   75000000
  19970201 박문수     과장   50000000 37000000   75000000
  19960101 홍길동     과장   72000000 37000000   75000000
  19930402 강감찬     차장   64000000 40000000   80000000
  19930331 정도령     차장   70000000 40000000   80000000
  19970101 김길동     부장  100000000 60000000  100000000
  20000210 나한라     수습   30000000
  20000119 장금강     수습   30000000
  20000308 강월악     수습   30000000
  20000407 윤주왕     수습   30000000
  20006106 유도봉     수습   30000000
  20000305 정북악     수습   30000000
  20000334 박지리     수습   30000000
  20000203 최오대     수습   30000000
  20000102 김설악     수습   30000000
  20000101 이태백     수습   30000000

20 rows selected.

◆ Self Join
하나의 테이블에서 두 개의 컬럼을 연결하여 Equi Join을 하는 조인 방법.

[문제] 부서번호가 201 이상인 부서 이름과 해당 부서가 소속된 상위 부서의 이름을 출력하시오.
SCOTT> select dept.dname || ' is belong to ' || org.dname
  2   from department dept, department org
  3   where dept.college = org.deptno
  4   and dept.deptno >= 201;

DEPT.DNAME||'ISBELONGTO'||ORG.DNAME
----------------------------------------------
기계공학과 is belong to 메카트로닉스학부
전자공학과 is belong to 메카트로닉스학부

---

[문제] temp 테이블을 이용하여 Non-Equi Join, Outer Join, Self Join이 모두 적용된 쿼리를 작성하시오.
컬럼은 사번, 성명, 생일, 자신보다 생일이 빠른 사람 수 이고 자신보다 생일이 빠른 사람 수를 기준으로 오름차순으로 정렬하시오.

SCOTT> select a.emp_id "사번",
  2   a.emp_name "성명",
  3   a.birth_date "생일",
  4   count(b.birth_date) "빠른사람수"
  5   from temp a, temp b
  6   where b.birth_date(+) < a.birth_date
  7   group by a.emp_id, a.emp_name, a.birth_date
  8   order by count(b.birth_date)

      사번 성명       생일    빠른사람수
---------- ---------- ------------ ----------
  19960303 설까치     25-SEP-71      0
  19966102 지문덕     05-JUL-72      1
  19930402 강감찬     15-AUG-72      2
  19960212 배뱅이     15-DEC-72      3
  19960101 홍길동     22-MAR-73      4
  19950303 이순신     15-JUN-73      5
  19970101 김길동     25-JAN-74      6
  19970201 박문수     15-APR-75      7
  19930331 정도령     25-MAY-76      8
  19970112 연흥부     05-NOV-76      9
  20000101 이태백     25-JAN-80     10
  20000102 김설악     22-MAR-80     11
  20000203 최오대     15-APR-80     12
  20000334 박지리     25-MAY-80     13
  20000305 정북악     15-JUN-80     14
  20006106 유도봉     05-JUL-80     15
  20000407 윤주왕     15-AUG-80     16
  20000308 강월악     25-SEP-80     17
  20000119 장금강     05-NOV-80     18
  20000210 나한라     15-DEC-80     19

20 rows selected.