2012.01.06 SQL 4일차(정규화, JOIN중 카티션곱, EQUI, NON-EQUI, OUTER JOIN)

정규화설명

JOIN 들어가기 전에 테이블을 어떤 방식으로 메모리에서 가져오는지 알아보자

고객TABLE

A	B	C	.............	100개의 칼럼
				테이블용량 10GB

위와 같은 테이블이 존재하고, 

SELECT A,B FROM 고객;  을 실행시켰을떄, 어떻게 자료를 가져와서 사용자에게 보여줄까?

사람이라면 그냥 A, B칼럼만 쏙 가져와서 보여줄 것이다.

하지만 컴퓨터는 고객테이블에 무슨 내용이 있는지 모르므로 A, B칼럼만 쏙 가져올 수가 없다.

그래서 저 10GB를 메모리에 올려서 A, B 일일히 찾아서 화면에 출력을 하게 된다.

그런데 이렇게 되면 너무나도 비효율적이다. 그래서 고안해낸 방법이 정규화(Normalizing) 이다.

정규화 : 간단히 말하면 테이블을 여러 단위로 쪼개 놓는 것이다. 예를들어 A,B  /  C,D 이런식으로 쪼갤 수가 있겠다.

이런식으로 쪼개놓고 사용자가 원하는 자료를 요청할 경우 전체 테이블을 메모리에 올리지 않고 쪼갠것들을 메모리에 올려서 효율성있게 자료를 가져오는 방법이다.(자세한 설명은 다음에)

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

JOIN

; 필요한 데이터들이 각각 다른 테이블에 존재할때, 그 테이블들을 결합해서 조회하게 하는 기능이다.

종류

; 카티션 곱, EQUI JOIN, NON-EQUI JOIN, OUTER JOIN, SELF JOIN 등이 있다.

철칙

; 무조건 앞에 적어준 테이블의 행수가 적어야 한다.

둘중 한쪽 테이블에 자료가 없으면 해당 행을 데이터가 아예 출력되지 않는다.

방법 : 

select A.colA, B.colB

from student A, professor B

WHERE 조건

아래 예시를 통해서 조인을 배워보자.

카티션 곱 : 연결가능한 행을 모두 결합하는 방법

A테이블에 학번칼럼(100개의 행)과, B테이블에 학과칼럼(5개의 행)이 있을 시, 

카티션 곱을 하게 되면 100 * 5개의 행이 출력된다.

; WHERE절의 조건이 모자라거나, WHERE절이 없거나 할때 출력되고, 때때로 쿼리의 속도를 측정해보기 위해 일부러 쓰기도 한다.

1  select student.name, professor.profno   2* from student, professor (각 profno에 해당하는 이름들을 모조리 출력해준다.)

NAME           PROFNO ---------- ---------- 전인하           9901 이동훈           9901 박미경           9901 김영균           9901 박동진           9901 김진영           9901 ...... 하나리           9908 임유진           9908 서재진           9908 윤진욱           9908 이광훈           9908 김진경           9908 조명훈           9908 류민정           9908 128 rows selected.

EQUI JOIN

; WHERE절에 '=' 조건으로 값이 같은 행을 연결하면 조인하는 방법

1  select s.name 학생이름, p.name 담당교수   2  from student s, professor p   3* where s.profno = p.profno (3행에 = 조건으로 조인했음을 알 수 있다.)	학생이름   담당교수 ---------- ---------- 전인하     성연희 김영균     이만식 김진영     권혁일 지은경     전은지 오유석     권혁일 임유진     전은지 윤진욱     권혁일 이광훈     성연희 김진경     이재우 류민정     전은지 10 rows selected.
1  select s.name 학생이름, d.dname 과이름, p.name 담당교수   2  from student s, department d, professor p   3* where s.profno = p.profno AND s.deptno = d.deptno  (조건절에 조건이 여러개일때 AND 또는 OR를 사용할 수 있다.)	학생이름   과이름           담당교수 ---------- ---------------- ---------- 김진경     전자공학과       이재우 이광훈     컴퓨터공학과     성연희 전인하     컴퓨터공학과     성연희 윤진욱     멀티미디어학과   권혁일 오유석     멀티미디어학과   권혁일 김진영     멀티미디어학과   권혁일 김영균     컴퓨터공학과     이만식 류민정     컴퓨터공학과     전은지 임유진     컴퓨터공학과     전은지 지은경     컴퓨터공학과     전은지 10 rows selected.

NON-EQUI JOIN

; WHERE절에 범위로 지정되는 조건이 사용되었으면 NON-EQUI JOIN 이다.

SQL> desc test13  Name                                      Null?    Type  ----------------------------- -------- -----------  LEV                                       NOT NULL VARCHAR2(2)  FPOINT                                    NOT NULL NUMBER  TPOINT                                    NOT NULL NUMBER  GIFT                                      NOT NULL VARCHAR2(40)	SQL> desc test14  Name                                      Null?    Type  -------------------- -------- ---------  CUST                                      NOT NULL VARCHAR2(14)  POINT                                     NOT NULL NUMBER
Test13 , test14 테이블을 사용하여  1) 고객별로 받을 수 있는 상품을 출력하세요. SQL> select cust.cust, gift.gift   2  from test14 cust, test13 gift   3  where cust.point between gift.fpoint and gift.tpoint; 고객주민번호와, 상품명을 출력하는데, 각 고객의 포인트가 특정상품의 하한에서 상한인 행을 고르고 싶다. between사용으로 non-equi 조인임을 알 수 있다.	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) 각 상품이 몇 개가 필요한지 출력하세요   1  select gift.gift,   2  count( cust.cust )   3  from test13 gift, test14 cust   4  where cust.point between gift.fpoint and gift.tpoint   5* group by gift.gift 상품이름과, 주민번호의 개수(다른 칼럼의 개수를 써도 상관없음)를 출력하고 싶은데, 고객의 포인트가 상품의 하한, 상한 사이에 있는 행을 선택. count를 썻으므로, 나머지 칼럼은 group by 로 묶어줌.	GIFT                                     COUNT(CUST.CUST) ---------------------------- ---------------- COMPUTER                                          2 의류교환권                                             1 굴비세트                                                1 갈비세트                                                1 PCS무료가입권                                      1 구두상품권                                             1 AUDIO                                                   1 녹차세트                                                1 도서상품권                                             1 9 rows selected.

OUTER JOIN

; 테이블의 자료 중 NULL값이 포함된 값을 조인하여 뽑고 싶은데, NULL인 행도 출력하고 싶을때 사용

(주의 : 쿼리 속도를 느리게 하는 주범 중 하나)

방법 : NULL이 없는 쪽 조건에 (+) 추가

주의 사항

* OUTER JOIN 에서는 IN 연산자를 사용할 수 없다. 

* 다른 조건과 OR 연산자로 결합 할 수 없다.

학생테이블과 교수테이블을 조인하여 이름, 학년,지도교수 이름,직급을 출력하세요. 단 지도학생을 배정받지 않은 교수이름도 함께 출력하세요.   1  select s.name 학생이름, s.grade,   p.name 담당교수, p.position 직급   2  from student s, professor p   3* where s.profno = p.profno(+) 학생들 중에 담당교수가 배정되지 않은 학생도 있으므로 s.profno 칼럼에 NULL값이 존재하게 된다. 그러므로 NULL값이 존재하지 않는 p.profno쪽에 (+) 추가해서 OUTER JOIN 사용

학생이름   G 담당교수   직급 ---------- - ---------- -------------------- 김진경     2 이재우     조교수 이광훈     4 성연희     조교수 전인하     4 성연희     조교수 윤진욱     3 권혁일     교수 오유석     4 권혁일     교수 김진영     2 권혁일     교수 김영균     3 이만식     부교수 류민정     2 전은지     전임강사 임유진     2 전은지     전임강사 지은경     2 전은지     전임강사 조명훈     1 서재진     1 하나리     1 박동진     1 박미경     1 이동훈     1 16 rows selected.