08 조인(JOIN) 조인의 개념 칼럼 이름의 애매모호성, 테이블 별명, 조인 조건식 조인의 종류.

Presentation on theme: "08 조인(JOIN) 조인의 개념 칼럼 이름의 애매모호성, 테이블 별명, 조인 조건식 조인의 종류."— Presentation transcript:

1 08 조인(JOIN) 조인의 개념 칼럼 이름의 애매모호성, 테이블 별명, 조인 조건식 조인의 종류

2 조인(Join) 조인의 개념 하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할 수 있는 기능 관계형 데이터베이스 분야의 표준 두개 이상의 테이블을 ‘결합’ 한다는 의미 조인의 필요성 조인을 사용하지 않는 일반적인 예 학생 주소록을 출력하기 위해 학생들의 학번, 이름, 소속학과 이름을 검색 학생에 대한 정보 검색하는 단계 필요 학생 정보에서 소속학과번호 정보를 추출하여 소속학과 이름을 검색하는 단계 필요 8장. 조인(JOIN)

3 조인의 개념 조인의 필요성 조인을 사용하지 않는 일반적인 예 8장. 조인(JOIN)

4 = 조인을 사용하지 않은 경우 조인을 사용한 경우 SELECT studno, name, deptno FROM student;
학생 테이블의 학번, 학생이름, 학과코드 검색 SELECT studno, name, deptno FROM student; 조인을 이용한 학생이름과 학과이름 검색 SELECT studno, name, student.deptno, department.dname FROM student, department WHERE student.deptno = department.deptno; 학생 테이블 STUDNO NAME …... DEPTNO 10101 전인하 …… 101 20101 이동훈 201 ……… 10203 윤진욱 102 10107 이광훈 학생 테이블 부서 테이블 STUDNO NAME … DEPTNO 10101 전인하 101 20101 이동훈 201 ……… 10203 윤진욱 102 10107 이광훈 DEPTNO DNAME 101 컴퓨터공학과 102 멀티미디어학과 201 전자공학과 …… …………. 10 공과대학 부서 테이블의 학과코드, 학과명 검색 SELECT deptno, dname FROM department WHERE deptno in (101,102, 201) ; = STUDNO NAME DEPTNO DNAME 10101 전인하 101 컴퓨터공학과 20101 이동훈 201 전자공학과 ……… …… 10203 윤진욱 102 멀티미디어학과 10107 이광훈 부서 테이블 DEPTNO DNAME 101 컴퓨터공학과 102 멀티미디어학과 201 전자공학과

5 = 학생 테이블 부서 테이블 학생 테이블과 부서 테이블 조인
STUDNO NAME USERID …... DEPTNO 10101 전인하 jun123 …… 101 20101 이동훈 Dals 201 ……… 10203 윤진욱 Samba7 102 10107 이광훈 huriky DEPTNO DNAME LOC 101 컴퓨터공학과 1호관 102 멀티미디어학과 2호관 201 전자공학과 3호관 202 기계공학과 4호관 10 공과대학 = 학생 테이블과 부서 테이블 조인 SELECT studno, userid, name, student.deptno, ….. FROM student, department WHERE student.deptno = department.deptno 조인 결과 STUDNO NAME USERID …... DEPTNO DNAME LOC 10101 전인하 jun123 …… 101 컴퓨터공학과 1호관 20101 이동훈 Dals 201 전자공학과 3호관 ……… 10203 윤진욱 Samba7 102 멀티미디어학과 2호관 10107 이광훈 huriky

6 칼럼 이름의 애매모호성 칼럼 이름의 애매모호성 해결방법 서로 다른 테이블에 있는 동일한 칼럼 이름을 연결할 경우
컬럼 이름앞에 테이블 이름을 접두사로 사용 테이블 이름과 칼럼 이름은 점(.)으로 구분 SQL 명령문에 대한 구문분석 시간(parsing time) 줄임 Student.deptno와 department.deptno 8장. 조인(JOIN)

7 칼럼 이름의 애매모호성 8장. 조인(JOIN)

8 테이블 별명 테이블 이름이 너무 긴 경우 사용 테이블 이름을 대신하는 별명 사용 가능
FROM절에서 테이블 이름 다음에 공백을 두고 별명 정의 테이블 별명 작성 규칙 테이블의 별명은 30자 까지 가능, 너무 길지 않게 작성 FROM 절에서 테이블 이름을 명시하고 공백을 둔 다음 테이블 별명지정 하나의 SQL 명령문에서 테이블 이름과 별명을 혼용할 수 없다 테이블의 별명은 해당 SQL 명령문 내에서만 유효 8장. 조인(JOIN)

9 테이블 별명 8장. 조인(JOIN)

10 AND 연산자를 사용한 검색 조건 추가 8장. 조인(JOIN)

11 AND 연산자를 사용한 검색 조건 추가 8장. 조인(JOIN)

12 카티션 곱 카티션 곱 두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합
WHERE 절에서 조인 조건절을 생략하거나 잘못 설정한 경우 대용량 테이블에서 발생할 경우 SQL명령문의 처리속도 저하 개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능 오라클 9i 이후 버전에서 FROM절에 CROSS JOIN 키워드 사용 8장. 조인(JOIN)

13 FROM student s, department d
카티션 곱 결과 (112건) 학생 테이블(16건) STUDNO NAME S.DEPTNO D.DEPTNO DNAME 10101 전인하 101 컴퓨터공학과 20101 이동훈 201 10102 박미경 10103 김영균 102 20102 박동진 10201 김진영 10104 지은경 10202 오유석 10203 하나리 10105 임유진 10106 서재진 10204 윤진욱 10107 이광훈 20103 김진경 20104 조명훈 10108 류민정 멀티미디어학과 …… ……………… 전자공학과 STUDNO NAME USERID DEPTNO 10101 전인하 jun123 101 20101 이동훈 Dals 201 ……… 10203 윤진욱 Samba7 102 10107 이광훈 huriky 카티션 곱 SELECT studno, name, s.deptno, d.deptno, dname FROM student s, department d 부서 테이블(7건) DEPTNO DNAME LOC 101 컴퓨터공학과 1호관 102 멀티미디어학과 2호관 201 전자공학과 3호관 202 기계공학과 4호관 100 정보미디어학부 200 메카트로닉스학부 10 공과대학

14 카티션 곱 8장. 조인(JOIN)

15 카티션 곱 8장. 조인(JOIN)

16 EQUI JOIN 개념 WHERE 절을 이용한 EQUI JOIN
조인 대상 테이블에서 공통 칼럼을 ‘=‘(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인 방법 SQL 명령문에서 가장 많이 사용하는 조인 방법 조인 애트리뷰트(join attribute(속성)) WHERE 절을 이용한 EQUI JOIN 8장. 조인(JOIN)

17 EQUI JOIN 8장. 조인(JOIN)

18 EQUI JOIN – NATURAL JOIN
WHERE 절을 사용하지 않고 NATURAL JOIN 키워드 사용 오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성 주의 : 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생 (조인 애트리뷰트: 조인 조건에 참여하는 attribute) 8장. 조인(JOIN)

19 EQUI JOIN – NATURAL JOIN

20 EQUI JOIN – NATURAL JOIN

21 EQUI JOIN – NATURAL JOIN

22 EQUI JOIN – JOIN ~ USING JOIN ~ USING 절을 이용한 EQUI JOIN
칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야함 주의 조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생 8장. 조인(JOIN)

23 EQUI JOIN – JOIN ~ USING 8장. 조인(JOIN)

24 EQUI JOIN – JOIN ~ USING 8장. 조인(JOIN)

25 NON-EQUI JOIN NON-EQUI JOIN
‘<‘,BETWEEN a AND b 와 같이 ‘=‘ 조건이 아닌 연산자 사용 8장. 조인(JOIN)

26 NON-EQUI JOIN 8장. 조인(JOIN)

27 PROFESSOR 테이블과 SALGRADE 테이블 NON-EQUI JOIN
PROFNO NAME POSITION SAL 9901 김도훈 교수 500 9902 이재우 조교수 320 9903 성연희 360 9904 염일웅 전임강사 240 9905 권혁일 450 9906 이만식 부교수 420 9907 전은지 210 9908 남은혁 400 500 GRADE : 3 400 GRADE : 2 300 200 GRADE : 1 100 PROFESSOR 테이블과 SALGRADE 테이블 NON-EQUI JOIN SELECT p.profno, p.name, p.sal, s.grade FROM professor p, salgrade s WHERE p.sal BETWEEN s.losal AND s.hisal PROFNO NAME POSITION SAL GRADE 9901 김도훈 교수 500 3 9902 이재우 조교수 320 2 …… … 9907 전은지 전임강사 210 1 9908 남은혁 부교수 400

28 OUTER JOIN 개요 EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL 이면 ‘=‘ 비교 결과가 거짓이 되어 NULL 값을 가진 행은 조인 결과로 출력 불가 NULL 에 대해서 어떠한 연산을 적용하더라고 연산 결과는 NULL 일반적인 EQUI JOIN 의 예 : 학생 테이블의 학과번호 칼럼과 부서 테이블의 부서번호 칼럼에 대한 EQUI JOIN ( student.deptno = department.deptno ) 한 경우 학생 테이블의 deptno 칼럼이 NULL 인 경우 해당 학생은 결과로 출력되지 못함 EQUI JOIN에서 양측 칼럼 값중의 하나가 NULL 이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용 OUTER JOIN의 예 : 학생 테이블과 교수 테이블을 EQUI JOIN하여 학생의 지도 교수 이름 출력 조건 : 지도 학생을 한 명도 배정받지 못한 교수 이름도 반드시 함께 출력 8장. 조인(JOIN)

29 OUTER JOIN (+) 기호를 사용한 OUTER JOIN
WHERE 절의 조인 조건에서 OUTER JOIN 연산자인 ‘(+)’ 기호 사용 조인 조건문에서 NULL 이 출력되는 테이블의 칼럼에 (+) 기호 추가 8장. 조인(JOIN)

30 OUTER JOIN 8장. 조인(JOIN)

31 OUTER JOIN OUTER JOIN의 제약사항 OUTER JOIN 연산자(+)는 NULL이 존재하는 칼럼쪽에 표시
OUTER JOIN에서는 IN 연산자를 사용 불가 다른 조건과 OR 연산자로 결합 불가 8장. 조인(JOIN)

32 (+) 학생 테이블 교수 테이블 학생 테이블과 교수 테이블 OUTER JOIN
STUDNO NAME GRADE … DEPTNO PROFNO 10101 전인하 4 101 9903 20101 이동훈 1 201 ……… …… 10103 김영균 3 ... 9906 20102 박동진 10201 김진영 2 102 9905 20103 김진경 9902 20104 조명훈 10108 류민정 9907 PROFNO NAME POSITION … DEPTNO 9901 김도훈 교수 101 9902 이재우 조교수 201 9903 성연희 9904 염일웅 전임강사 102 9905 권혁일 9906 이만식 부교수 9907 전은지 9908 남은혁 202 (+) 학생 테이블과 교수 테이블 OUTER JOIN SELECT s.name, s.grade, p.name, p.position FROM student s, professor p WHERE s.profno = p.profno(+) ; NAME GRADE POSITION 김진경 2 이재우 조교수 전인하 4 성연희 ……… …… 김진영 권혁일 교수 김영균 3 이만식 부교수 류민정 전은지 전임강사 이동훈 1 박동진 조명훈

33 OUTER JOIN 8장. 조인(JOIN)

34 OUTER JOIN OUTER JOIN ~ON 절을 사용한 OUTER JOIN LEFT OUTER JOIN =
오라클 9i 버전부터 OUTER JOIN 절 사용 LEFT OUTER JOIN FROM 절의 왼쪽에 위치한 테이블이 NULL 을 가질 경우에 사용 WHERE절의 오른편에 ‘(+)’ 기호를 추가한 것과 동일 예 : 지도교수가 배정되지 않은 학생 명단을 출력하여라 SELECT studno, s.name, s.profno, p.name FROM   student s LEFT OUTER JOIN professor p       ON   s.profno = p.profno; FROM   student s, professor p   WHERE  s.profno = p.profno(+); = 8장. 조인(JOIN)

35 OUTER JOIN RIGHT OUTER JOIN = FROM 절의 오른쪽에 위치한 테이블이 NULL 을 가질 경우, 사용
WHERE 절의 왼편’(+)’ 기호를 추가한 것과 동일 예 지도학생이 배정되지 않은 교수 명단을 출력하여라. SELECT studno, s.name, s.profno, p.name FROM   student s RIGHT OUTER JOIN professor p     ON   s.profno = p.profno; SELECT studno, s.name, s.profno, p.name FROM   student s, professor p   WHERE  s.profno(+) = p.profno; = 8장. 조인(JOIN)

36 OUTER JOIN FULL OUTER JOIN
LEFT OUTER JOIN 과 RIGHT OUTER JOIN 을 동시에 실행한 결과를 출력 예 지도교수가 없는 학생과 지도학생이 배정되지 않은 교수를 함께 출력하라. SELECT studno, s.name, s.profno, p.name FROM   student s FULL OUTER JOIN professor p ON   s.profno = p.profno; 8장. 조인(JOIN)

37 (+) (+) 학생 테이블 교수 테이블 학생 테이블과 교수 테이블 FULL OUTER JOIN
STUDNO NAME GRADE … DEPTNO PROFNO 10101 전인하 4 101 9903 20101 이동훈 1 201 ……… …… 10103 김영균 3 ... 9906 20102 박동진 10201 김진영 2 102 9905 20103 김진경 9902 20104 조명훈 10108 류민정 9907 PROFNO NAME POSITION … DEPTNO 9901 김도훈 교수 101 9902 이재우 조교수 201 9903 성연희 9904 염일웅 전임강사 102 9905 권혁일 9906 이만식 부교수 9907 전은지 9908 남은혁 202 (+) (+) 학생 테이블과 교수 테이블 FULL OUTER JOIN SELECT studno, s.name, grade, s.deptno, s.profno, p.name FROM student s FULL OUTER JOIN professor p ON s.profno = p.profno; STUDNO NAME GRADE DEPTNO PROFNO POSITION 10101 전인하 4 101 9903 성연희 조교수 20101 이동훈 1 201 ……… …… 10103 김영균 3 9906 이만식 부교수 20102 박동진 10201 김진영 2 102 9905 권혁일 교수 10108 류민정 9907 전은지 전임강사 9901 김도훈 9904 염일웅

38 OUTER JOIN 8장. 조인(JOIN)

39 SELF JOIN 개요 WHERE 절을 사용한 SELF JOIN
하나의 테이블내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용 조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일 WHERE 절을 사용한 SELF JOIN 한 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN FROM절에서 하나의 테이블에 테이블 별명지정 예 부서 테이블에서 WHERE 절을 사용하여 SELF JOIN을 처리 8장. 조인(JOIN)

40 WHERE c.college = d.deptno;
부서 테이블 부서 테이블 DEPTNO DNAME COLLEGE LOC 101 컴퓨터공학과 100 1호관 102 멀티어미디어학과 2호관 ……… … …… 정보미디어학부 10 200 메카트로닉스학부 공과대학 DEPTNO DNAME COLLEGE LOC 101 컴퓨터공학과 100 1호관 102 멀티어미디어학과 2호관 ……… … …… 정보미디어학부 10 200 메카트로닉스학부 공과대학 부서 테이블의 SELF JOIN SELECT c.deptno, c.dname, c.college d.dname college_name, … FROM department c, department d WHERE c.college = d.deptno; 조인 결과 DEPTNO DNAME COLLEGE COLLEGE_NAME LOC 101 컴퓨터공학과 100 정보미디어학부 1호관 102 멀티미디어학과 2호관 … …………… ……………… …… 10 공과대학 200 메카트로닉스학부

41 SELF JOIN JOIN ~ ON 절을 사용한 SELF JOIN 오라클 9i에서 JOIN ~ ON 지원

42 SELF JOIN 8장. 조인(JOIN)

43 SELF JOIN 8장. 조인(JOIN)

44 실습 1. 각 학과 별로 학과 이름 및 소속 교수와 각 소속 교수의 지도 학생을
1. 각 학과 별로 학과 이름 및 소속 교수와 각 소속 교수의 지도 학생을 다음과 같이 모두 출력하는 JAVA 프로그램을 작성하시오. (단, 지도 학생이 없는 교수도 출력 되어야 함, 학과이름 , 교수님 이름, 지도학생 이름 순으로 정렬 ) <출력> 기계공학과 소속 남은혁 교수님 지도학생은 null 멀티미디어학과 소속 권혁일 교수님 지도학생은 김진영 멀티미디어학과 소속 권혁일 교수님 지도학생은 오유석 멀티미디어학과 소속 권혁일 교수님 지도학생은 윤진욱 멀티미디어학과 소속 염일웅 교수님 지도학생은 null 전자공학과 소속 이재우 교수님 지도학생은 김진경 컴퓨터공학과 소속 김도훈 교수님 지도학생은 null 컴퓨터공학과 소속 성연희 교수님 지도학생은 이광훈 컴퓨터공학과 소속 성연희 교수님 지도학생은 전인하 컴퓨터공학과 소속 이만식 교수님 지도학생은 김영균 컴퓨터공학과 소속 전은지 교수님 지도학생은 류민정 컴퓨터공학과 소속 전은지 교수님 지도학생은 임유진 컴퓨터공학과 소속 전은지 교수님 지도학생은 지은경 8장. 조인(JOIN)