DB Oracle 정리 2탄 (오라클 SQL과 PL/SQL Ch4)

관계형 데이터베이스 (RDBMS)의 핵심적인 기능인  Join

 

예시) A테이블에 a, b 컬럼을 대상으로 SELECT를 수행하면 디스크에서는

A테이블의 모든 컬럼을 메모리로 가져온다. (비효율적)

 

정규화 : 하나로 되어있는 것을 작은 조각으로 분리하는 기술

정규화를 수행하면 하나의 테이블이 여러개의 테이블로 나누어진다.

여러 테이블에 흩어진 데이터를 조합해서 가져오는 것이 Join 이라는 기술이다!

 

JOIN (INNER JOIN / OUTER JOIN)

---

---

-----------------------
▶Oracel JOIN 문법

SELECT d.dept_nm, s.s_id, s.s_nm
FROM student s, dept d 
WHERE s.D_ID = d.D_ID ;
 /*WHERE student.D_ID = dept.D_ID */

-----------------------
▶ANSI JOIN 문법 (사용)

SELECT d.dept_nm, s.s_id, s.s_nm
FROM student s
JOIN dept d ON s.D_ID = d.D_ID ;

-----------------------
EMP -> ENPNO, ENAME
DEPT -> DEPTNO, DNAME, LOC

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DEPTNO,
D.DNAME, D.LOC
FROM EMP E
INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

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

INNER JOIN C ON A.d = C.d

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

SELECT
FORM Z
INNER JOIN Y ON Z.a = Y.a
AND Z.b = Y.b

▶JOIN 추가하기

SELECT
FORM Z
INNER JOIN Y ON Z.a = Y.a AND Z.b = Y.b
INNER JOIN X ON Z.d = X.d

 

▶oracle 문법

SELECT
FORM Z,Y,Z
WHERE Z.a = Y.a AND Z.b = Y.b 
AND Z.d = X.d

 

(1) Cartesian Product  (카티션 곱)

Join 에서 가장 중요한 부분은 조인 조건 지정 부분.

 

조인 조건절을 적지 않으면 테이블에 대한 모든 데이터를 전부 가져온다. 이것을 카티션 곱이라고 부름

 

join쿼리 중에 WHERE절에 기술하는  join 조건이 잘못 기술 되었거나 아예 없을 경우 발생하는 현상이다.

 

사용하는 이유

1 : 데이터를 복제하여 원본 테이블을 반복해서 읽는 것을 피하기 위해서

2 : 실수로 조인 조건 컬럼 중 일부를 빠뜨리는 경우

 

-----------------------
/*카티션 나오는 건 막아야함*/
-----------------------

---

EQUL Join (등가조인)

 

선행 테이블에서 데이터를 가져온 후 조인 조건절을 검사해서 동일한 조건을 가진 데이터를 후행 테이블에서 꺼내오는 방법

 

조건절에 = 를 사용해서 EQUL Join 이라고 한다.

 

"테이블이름 . 컬럼이름" 같은 형태로 적을 때 컬럼이름이 하나의 테이블에만 있는 경우에는

테이블이름을 생략해도됨

 

검색조건, Join조건 => 검색 조건을 먼저 검색하고 데이터 범위를 줄인 다음 Join 조건을 작업을 수행한다.

(검색조건을 잘 활용하면 작업의 속도가 빨라진다 !!)

 

 

▶등가조인
양쪽 테이블 모두 데이터가 존재해야야 결과가 나옴 
(INNER 은 최소(작게) 매핑시킴 여러개 조건도 작은 쪽 매핑으로 출력한다. )

SELECT S.NAME "STU_NAME", P.NAME "PRO_NAME" 
FROM STUDENT S, PROFESSOR P
WHERE S.PROFNO= P.PROFNO;

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

- 데이터가 많 -> 적
- 계획설명 F10 COST(비용기반) 낮을 수록 좋음
-----------------------

 

SELECT s.name stu_name
, d.dname 
, p.name prof_name
FROM student s
INNER JOIN department d
ON s.deptno1 = d.deptno
INNER JOIN professor p
ON d.deptno = s.deptno1
AND p.profno = s.profno;

 

SELECT s.name stu_name
,p.name prof_name
FROM student s 
INNER JOIN professor p 
ON s.profno = p.profno --AND s.deptno = 101
WHERE s.deptno1 = 101;
/* AND 가 더 효율적 101인 애들만 (대상만) 조인을 건다*/

---

 

Non-EQUL Join (비등가조인)

 

조건이 같지않고 크거나 작은 경우의 조건을 조회해야할 경우

 

BETWEEN or 비교연산자 (성능 더 좋음/ 권장)

 

 

사용예2>

=다양한 조건 조회
=조건 사이에 걸리지 않으면 출력되지않음

SELECT C.*
FROM CUSTOMER C
INNER JOIN GIFT G 
	ON C.POINT BETWEEN G.G_START AND G.G_END;
    
    --
    
SELECT C.GNAME CUST_NAME, G.GNAME GIF_NAME, C.POINT, G.G_START, G.G_END
FROM CUSTOMER C
INNER JOIN GIFT G 
	ON C.POINT BETWEEN G.G_START AND G.G_END;

=비교 연산자가 성능에 좋다.

SELECT C.GNAME CUST_NAME, G.GNAME GIF_NAME, C.POINT, G.G_START, G.G_END
FROM CUSTOMER C
INNER JOIN GIFT G 
	ON C.POINT >= G.G_START 
    AND C.POINT <= G.G_END;

 

사용예2>

SELECT S.NAME, O.TOTAL, H.GRADE
FROM STUDENT
INNER JOIN SCORE O
  ON S.STUDNO = O.STUDNO 

INNER JOIN HAKJUM H --학점 기준테이블
  ON O.TOTAL >= H.MIN_POINT 
    AND O.TOTAL <= H.MAX_POINT

---

 

 

OUTER Join(아우터 조인)

 

equi , non-equi 모두 모든 테이블에 데이터가 존재하는 경우에만 결과값을 출력했다. (inner join)

한쪽 테이블엔 있고 다른 한쪽에는 없는 경우 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법 (outer join)

 

DB성능에 아주 나쁜 영향을 줄 수 있음 (신중히 사용)

 

- 인덱스를 쓰지않고 Full Scan을 함

- 튜닝에서) 조인순서가 고정되어 사용자 뜻대로 변경할 수 없음

 

Oracle -> Where 조건절 데이터가 없는 쪽에 (+) 표시해준다.

ANSI -> 데이터가 존재하는 쪽에 (+)

LEFT OUTER -> = 기호를 기준으로 왼오 표시

 

Outer Join 합쳐서 출력하기 -> UNION ( UNION ALL) 연산자 사용

 

주의사항

1. Where 절의 outer join 되는 컬럼에 전부 join 연산자(+) 를 붙여야함 -> 하나라도 빠지면 일반 조인과 동일하게 됨

2. Where 절의 조건을 ON절로 위치시키는 경우... !!

 

 

 

사용예1>

 

= 참조 순서에 따라서 1) LEFT / RIGHT , 2) 주테이블 변경

= 어떤 쪽이 주테이블이 되는지 파악해야함

SELECT S.NAME STD_NAME, P.NAME PROF_NAME
FROM STUDENT S
INNER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
-- 15명

SELECT S.NAME STD_NAME, P.NAME PROF_NAME
FROM STUDENT S 
LEFT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
--20 (주) s 1:1 방식

 

사용예2>

SELECT S.NAME STD_NAME, P.NAME PROF_NAME
FROM STUDENT S 
RIGHT OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
--22 (주)

 

 

사용예3>

SELECT S.NAME STD_NAME, P.NAME PROF_NAME
FROM STUDENT S 
FULL OUTER JOIN PROFESSOR P
ON S.PROFNO = P.PROFNO;
--27 FULL

 

참고 2 >

= WHERE 절 때문에 OUTER 조인 효과가 사라진다. (ON 으로 묶어줘야됨.

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, D.LOC
FROM EMP E 
LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'CHICAGO';

--
SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, D.LOC
FROM EMP E 
LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND D.LOC = 'CHICAGO'
WHERE E.JOB = 'CLERK'; --만들고 -> WHERE 전체의조건

--

SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME, D.LOC
FROM EMP E 
LEFT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
AND D.LOC = 'CHICAGO'
AND E.JOB = 'CLERK'; --ON 안에 들어가면 JOIN 안의 조건 -> 조건에 맞지않아도 NULL을 만듬

 

---

SELF Join

 

원하는 데이터가 하나의 테이블에 모두 있는 경우

데이터를 가진 테이블을 메모리에서 별명 두 개로 사용하여 호출한다. (2개의 테이블로 만듬)

 

 

 

SELF  => 재귀

= 마지막 MGR 없는 KING 은 나오지않는다. 

 

SELECT E1.ENAME,E2.ENAME
FROM EMP E1
INNER JOIN EMP E2
ON E1.MGR = E2.EMPNO;

 

= OUTER JOIN 을 하면 KING 도 나온다.

= 주테이블이 E1 이므로 LEFT 로 해준다.

SELECT E1.ENAME,E2.ENAME
FROM EMP E1
LEFT OUTER JOIN EMP E2
ON E1.MGR = E2.EMPNO;

---