다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양한예제로쉽게배우는 오라클 SQL 과 PL/SQL 서진수저

4 장 JOIN 을배웁니다 1

2

1. Cartesian Product ( 카티션곱, CROSS Join) - Oracle Join 문법 SQL> SELECT e.ename, d.dname 2 FROM emp e, dept d ; - ANSI Join 문법 SQL> SELECT e.ename, d.dname 2 FROM emp e CROSS JOIN dept d ; 3

2. EQUI Join ( 등가 Join) 4. JOIN 을배웁니다 1) Equi join 예 1: 학생테이블 (student) 과학과테이블 (department) 테이블을사용하여학생이름, 1 전공학과번호 (deptno1), 1 전공학과이름을출력하세요. 4

- Oracle Join 문법 SCOTT>SELECT s.name " 학생이름 ", s.deptno1 " 학과번호 ", d.dname " 학과이름 " 2 FROM student s, department d 3 WHERE s.deptno1 = d.deptno ; - ANSI Join 문법 SCOTT>SELECT s.name " 학생이름 ", s.deptno1 " 학과번호 ", d.dname " 학과이름 " 2 FROM student s JOIN department d 3 ON (s.deptno1 = d.deptno) ; 5

2) Equi Join 예 2: 학생테이블 (student) 과교수테이블 (professor) 을 join 하여학생의이름과지도교수번호, 지도교수이름을출력하세요. 6

- Oracle Join 문법 SCOTT>SELECT s.name " 학생이름 ", s.profno " 교수번호 ", p.name " 교수이름 " 2 FROM student s, professor p 3 WHERE s.profno = p.profno ; - ANSI Join 문법 SCOTT>SELECT s.name " 학생이름 ", s.profno " 교수번호 ",p.name " 교수이름 " 2 FROM student s JOIN professor p 3 ON (s.profno = p.profno ); 7

3) Equi Join 예 3: 학생테이블 (student) 과학과테이블 (department), 교수테이블 (professor) 을 Join하여학생의이름과학과이름, 지도교수이름을출력하세요. 8

- Oracle Join 문법 4. JOIN 을배웁니다 SCOTT>SELECT s.name " 학생이름 ", d.dname " 학과이름 ", p.name " 교수이름 " 2 FROM student s, department d, professor p 3 WHERE s.deptno1 = d.deptno 4 AND s.profno = p.profno ; - ANSI Join 문법 SCOTT> SELECT s.name " 학생이름 ", d.dname " 학과이름 ", p.name " 교수이름 " 2 FROM student s JOIN department d 3 ON s.deptno1 = d.deptno 4 JOIN professor p 5 ON s.profno = p.profno ; 9

4) Equi Join 예 4 : emp2 테이블과 p_grade 테이블을조회하여사원의이름과직급, 현재연봉, 해당직급의연봉의하한금액과상한금액을출력하세요. 10

- Oracle Join 문법 - ANSI Join 문법 SCOTT> SELECT e.name " 사원이름 ", 2 e.position " 현재직급 ", 3 e.pay " 현재연봉 ", 4 p.s_pay " 하한금액 ", 5 p.e_pay " 상한금액 " 6 FROM emp2 e, p_grade p 7 WHERE e.position = p.position ; SCOTT> SELECT e.name " 사원이름 ", 2 e.position " 현재직급 ", 3 e.pay " 현재연봉 ", 4 p.s_pay " 하한금액 ", 5 p.e_pay " 상한금액 " 6 FROM emp2 e JOIN p_grade p 7 ON e.position = p.position ; 11

5) Equi Join 예 5 : 1 전공 (deptno1) 이 101 번인학생들의학생이름과지도교수이름을출력하세요. 12

- Oracle Join 문법 4. JOIN 을배웁니다 SCOTT>SELECT s.name " 학생이름 ", p.name " 지도교수명 " 2 FROM student s, professor p 3 WHERE s.profno = p.profno 4 AND s.deptno1 = 101 ; - ANSI Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 지도교수명 " 2 FROM student s JOIN professor p 3 ON s.profno = p.profno 4 AND s.deptno1 = 101 ; 13

3. Non-Equi Join ( 비등가 Join) 1) Non-Equi Join 예제 1: Gogak 테이블과 gift 테이블을 Join 하여고객의마일리지포인트별로받을수있는상품을조회하여고객의이름과상품명을출력하세요. 14

- Oracle Join 구문 SCOTT>SELECT go.gname " 고객명 ", go.point "POINT", gi.gname " 상품명 " 2 FROM gogak go, gift gi 3 WHERE go.point BETWEEN gi.g_start AND gi.g_end ; - ANSI Join 구문 SCOTT>SELECT go.gname " 고객명 ", go.point "POINT", gi.gname " 상품명 " 2 FROM gogak go JOIN gift gi 3 ON go.point BETWEEN gi.g_start AND gi.g_end ; 15

2) Non-Equi Join 예제 2: 4. JOIN 을배웁니다 위예제 1 번에서조회한상품의이름과필요수량이몇개인지조회하세요. 16

- Oracle Join 구문 SCOTT> SELECT gi.gname " 상품명 ", COUNT(*) " 필요수량 " 2 FROM gogak go, gift gi 3 WHERE go.point BETWEEN gi.g_start AND g_end 4 GROUP BY gi.gname ; - ANSI Join 구문 SCOTT>SELECT gi.gname " 상품명 ", COUNT(*) " 필요수량 " 2 FROM gogak go JOIN gift gi 3 ON go.point BETWEEN gi.g_start AND gi.g_end 4 GROUP BY gi.gname ; 17

3) Non-Equi Join 예제 3: Student 테이블과 exam_01 테이블, hakjum 테이블을조회하여학생들의이름과점수와학점을출력하세요. 18

- Oracle Join 문법 SCOTT>SELECT s.name " 학생이름 ", e.total " 점수 ", h.grade " 학점 " 2 FROM student s, exam_01 e, hakjum h 3 WHERE s.studno = e.studno 4 AND e.total BETWEEN h.min_point AND h.max_point ; - ANSI Join 문법 SCOTT>SELECT s.name " 학생이름 ",e.total " 점수 ", h.grade " 학점 " 2 FROM student s JOIN exam_01 e 3 ON s.studno = e.studno 4 JOIN hakjum h 5 ON e.total BETWEEN h.min_point AND h.max_point ; 19

4) Non-Equi Join 예제 4 : 4. JOIN 을배웁니다 Gogak 테이블과 gift 테이블을 Join 하여고객이자기포인트보다낮은포인트의상품중한가지를선택할수있다고할때산악용자전거를선택할수있는고객명과포인트, 상품명을출력하세요. 20

- Oracle Join 문법 SCOTT>SELECT go.gname " 고객명 ", go.point "POINT", gi.gname " 상품명 " 2 FROM gogak go, gift gi 3 WHERE gi.g_start <= go.point 4 AND gi.gname =' 산악용자전거 '; - ANSI Join 문법 SCOTT>SELECT go.gname " 고객명 ", go.point "POINT", gi.gname " 상품명 " 2 FROM gogak go JOIN gift gi 3 ON gi.g_start <= go.point 4 AND gi.gname =' 산악용자전거 '; 21

5) Non-Equi Join 예제 5: Emp 2 테이블과 p_grade 테이블을조회하여사원들의이름과나이, 현재직급, 예상직급을출력하세요. 예상직급은나이로계산하며해당나이가받아야하는직급을의미합니다. 나이는오늘 (sysdate) 를기준으로하되 trunc 로소수점이하는절삭해서계산하세요. 22

- Oracle Join 문법 SCOTT>SELECT e.name " 이름 ", 2 trunc((sysdate-e.birthday)/365,0) " 현재나이 ", 3 e.position " 현재직급 ", 4 g.position " 예상직급 " 5 FROM emp2 e, p_grade g 6 WHERE trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ; - ANSI Join 문법 SCOTT>SELECT e.name " 이름 ", 2 trunc((sysdate-e.birthday)/365,0) " 현재나이 ", 3 e.position " 현재직급 ", 4 g.position " 예상직급 " 5 FROM emp2 e JOIN p_grade g 6 ON trunc((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age ; 23

4. OUTER Join ( 아우터조인 ) vs INNER Join ( 이너조인 ) 1) Outer Join 예제 1: Student 테이블과 Professor 테이블을 Join 하여학생이름과지도교수이름을출력하세요. 단지도교수가결정되지않은학생의명단도함께출력하세요. - Oracle Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s, professor p 3 WHERE s.profno = p.profno(+) ; - Oracle Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s LEFT OUTER JOIN professor p 3 ON s.profno = p.profno ; 24

- 결과화면 지도교수가없는학생들입니다. 교수이름에 * 가나오는이유는 SQL> set null ***** ; 로설정했기때문입니다. 25

2) Outer Join 예제 2 : Student 테이블과 Professor 테이블을 Join 하여학생이름과지도교수이름을출력하세요. 단지도학생이결정되지않은교수의명단도함께출력하세요. 지도학생이결정 안된교수들입니다. 26

- Oracle Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s, professor p 3 WHERE s.profno(+) = p.profno 4 ORDER BY 1 ; - ANSI Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s RIGHT OUTER JOIN professor p 3 ON s.profno = p.profno 4 ORDER BY 1 ; 27

3) Outer Join 예제 3 : Student 테이블과 Professor 테이블을 Join 하여학생이름과지도교수이름을출력하세요. 단지도학생이결정안된교수명단과지도교수가결정안된학생명단을한꺼번에출력하세요. 지도교수가없는학생들입니다 지도학생이없는교수들입니다 28

- Oracle Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s, professor p 3 WHERE s.profno(+) = p.profno 4 UNION 5 SELECT s.name " 학생이름 ", p.name " 교수이름 " 6 FROM student s, professor p 7 WHERE s.profno = p.profno(+) ; - Oracle Outer Join 문법 SCOTT>SELECT s.name " 학생이름 ", p.name " 교수이름 " 2 FROM student s FULL OUTER JOIN professor p 3 ON s.profno = p.profno ; 29

5. SELF Join 경영지원부의상위부서는사장실이다 30

31

- Oracle Join 문법 SCOTT> SELECT a.dname 부서명, b.dname 상위부서명 2 FROM dept2 a, dept2 b 3 WHERE a.pdept = b.dcode ; - ANSI Join 문법 SCOTT> SELECT a.dname 부서명, b.dname 상위부서명 2 FROM dept2 a JOIN dept2 b 3 ON a.pdept = b.dcode ; 32

- 출력결과 33

** Join 연습문제 1 : professor 테이블에서교수의번호, 교수이름, 입사일, 자신보다입사일빠른사람인원수를출력하세요. 단자신보다입사일이빠른사람수를오름차순으로출력하세요. 34

6. JOIN 원리 4. JOIN 을배웁니다 35

1) Nested Loop Join ( 가장기본적인 Join 기법입니다 ) SQL> SELECT e.ename, d.dname 2 FROM emp e, dept d 3 WHERE e.deptno = d.deptno ; 36

2) Sort-Merge join 4. JOIN 을배웁니다 인덱스가없을경우 인덱스가있을경우 37

- Sort 후 Merge 작업 38

3) Hash Join (CBO 에서만가능합니다 ) 39