JOIN
조인의 개념
-하나의 SQL 명령문에 의해 여러 테이블에 저장된 데이터를 한번에 조회할수 있는 기능
-관계형 데이터베이스 분야의 표준
-두개 이상의 테이블을 ‘결합’ 한다는 의미
조인의 필요성
-조인을 사용하지 않는 일반적인 예
학생 주소록을 출력하기 위해 학생들의 학번, 이름, 소속학과 이름을 검색
학생에 대한 정보 검색하는 단계 필요
학생 정보에서 소속학과번호 정보를 추출하여 소속학과 이름을 검색하는 단계 필요
문번)
SELECT * | COLUMN
FROM 테이블1,테이블2.... == 테이블1 CROSS JOIN 테이블2....
칼럼 이름의 애매모호성
칼럼 이름의 애매모호성 해결방법
-서로 다른 테이블에 있는 동일한 칼럼 이름을 연결할경우
-컬럼 이름앞에 테이블 이름을 접두사로 사용
-테이블 이름과 칼럼 이름은 점(.)으로 구분
-SQL 명령문에 대한 구문분석 시간(parsing time) 줄임
SQL> SELECT EMPNO, ENAME, SAL, DNAME, DEPTNO
2 FROM EMP, DEPT;
SELECT EMPNO, ENAME, SAL, DNAME, DEPTNO
*
1행에 오류:
ORA-00918: 열의 정의가 애매합니다
* 두 테이블에 같은 값이 존재하기 떄문에 오류 발생
SQL> SELECT EMP.EMPNO, EMP.ENAME, EMP.SAL, DEPT.DNAME, DEPT.DEPTNO
2 FROM EMP, DEPT;
SELECT EMPNO, ENAME, SAL, DNAME, DEPTNO
테이블 별명
-테이블 이름이 너무 긴 경우 사용
-테이블 이름을 대신하는 별명 사용 가능
-FROM절에서 테이블 이름 다음에 공백을 두고 별명 정의
-테이블 별명 작성 규칙
테이블의 별명은 30자 까지 가능, 너무 길지 않게 작성
FROM 절에서 테이블 이름을 명시하고 공백을 둔 다음 테이블 별명지정
하나의 SQL 명령문에서 테이블 이름과 별명을 혼용할 수 없다
테이블의 별명은 해당 SQL 명령문내에서만 유효
SQL> SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME, D.DEPTNO
2 FROM EMP E, DEPT D; //별칭을 이용한 조회.
카티션 곱
카티션 곱
-두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합
-WHERE 절에서 조인 조건절을 생략하거나 잘못 설정한 경우
-대용량 테이블에서 발생할 경우 SQL명령문의 처리속도 저하
-개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능
-오라클 9i 이후 버전에서 FROM절에 CROSS JOIN 키워드 사용
CROSS JOIN
EQUI JOIN : 조인기법중 사용빈도 높아.
EQUI JOIN
개념
조인 대상 테이블에서 공통 칼럼을 ‘=‘(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 조인 방법
SQL 명령문에서 가장 많이 사용하는 조인 방법
조인 애트리뷰트(join attribute(속성))
WHERE 절을 이용한 EQUI JOIN
SQL> SELECT EMPNO, ENAME, SAL, DNAME, DEPT.DEPTNO
2 FROM EMP, DEPT
3 WHERE EMP.DEPTNO = DEPT.DEPTNO;
EMPNO ENAME SAL DNAME DEPTNO
---------- ---------- ---------- -------------- ----------
7782 CLARK 2450 ACCOUNTING 10
7839 KING 5000 ACCOUNTING 10
7934 MILLER 1300 ACCOUNTING 10
7369 SMITH 800 RESEARCH 20
7876 ADAMS 1100 RESEARCH 20
7902 FORD 3000 RESEARCH 20
7788 SCOTT 3000 RESEARCH 20
7566 JONES 2975 RESEARCH 20
7499 ALLEN 1600 SALES 30
7698 BLAKE 2850 SALES 30
7654 MARTIN 1250 SALES 30
7900 JAMES 950 SALES 30
7844 TURNER 1500 SALES 30
7521 WARD 1250 SALES 30
140 권덕용 2900 OPERATIONS 40
141 표희태 2900 OPERATIONS 40
142 정영선 3000 OPERATIONS 40
143 이성민 3000 OPERATIONS 40
18 개의 행이 선택되었습니다.
EQUI JOIN – NATURAL JOIN
자연조인을 이용한 EQUI JOIN
-오라클 9i 버전부터 EQUI JOIN을 자연조인이라 명명
-WHERE 절을 사용하지 않고 NATURAL JOIN 키워드 사용
-오라클에서 자동적으로 테이블의 모든 칼럼을 대상으로 공통 칼럼을 조사 후, 내부적으로 조인문 생성
2개 이상의 테이블의 공동컬럼을 기준으로 논리적 결합
JOIN ~USING() 기법
:Natural 조인시 공통컬럼을 정의하지 않으면 오라클 서버는 해당 테이블의 모든 컬럼을 대사응로 논리적결합
select E.EMPNO, E.NAME, E.SAL, D.ANME
FROM EMP E JOIN DEPT D USING(DEPTNO);
E.EMPNO, NAME, SAL, ANME, DNAME, LOC, DEPTNO
FROM EMP JOIN
EQUI JOIN – JOIN ~ USING
JOIN ~ USING 절을 이용한 EQUI JOIN
USING절에 조인 대상 칼럼을 지정
칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야함
주의
조인 애트리뷰트에 테이블 별명을 사용하면 오류가 발생
NON-EQUI JOIN
NON-EQUI JOIN
‘<‘ , BETWEEN a AND b 와 같이 ‘=‘ 조건이 아닌 연산자 사용
WHERE 절에 BERWEEN ~ AND사용
문제1) EMP 테이블과 SALGRADE테이블에서
EMPNO, ENAME, SAL, GRADE 컬럼을 조인하여 조회
SQL> SELECT E.EMPNO, E.ENAME, E.SAL, SG.GRADE
2 FROM EMP E, SALGRADE SG
3 WHERE E.SAL BETWEEN SG.LOSAL AND SG.HISAL;
EMPNO ENAME SAL GRADE
---------- ---------- ---------- ----------
7369 SMITH 800 1
7876 ADAMS 1100 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
126 정난정 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7788 SCOTT 3000 4
7902 FORD 3000 4
140 권덕용 2900 4
141 표희태 2900 4
142 정영선 3000 4
143 이성민 3000 4
7839 KING 5000 5
19 개의 행이 선택되었습니다.
SELF JOIN
개요
하나의 테이블내에 있는 칼럼끼리 연결하는 조인이 필요한 경우 사용
조인 대상 테이블이 자신 하나라는 것 외에는 EQUI JOIN과 동일
WHERE 절을 사용한 SELF JOIN
한 테이블에서 두 개의 칼럼을 연결하여 EQUI JOIN
FROM절에서 하나의 테이블에 테이블 별명지정
같은 테이블에서 서로다른 컬럼을 참고해서 보는것.
문제2)
사원번호, 사원명, MGR - 조회
사원번호,MGR - 조회
== MGR 이 사원번호만 보는 것이 아니라 그 이름 까지 보기 원하는것.
SQL> SELECT E1.ENAME || '의 상관은 ' || E2.ENAME
2 FROM EMP E1, EMP E2
3 WHERE E1.MGR = E2.EMPNO;
E1.ENAME||'의상관은'||E2.ENAME
------------------------------
SCOTT의 상관은 JONES
FORD의 상관은 JONES
ALLEN의 상관은 BLAKE
WARD의 상관은 BLAKE
JAMES의 상관은 BLAKE
TURNER의 상관은 BLAKE
MARTIN의 상관은 BLAKE
MILLER의 상관은 CLARK
ADAMS의 상관은 SCOTT
JONES의 상관은 KING
CLARK의 상관은 KING
BLAKE의 상관은 KING
SMITH의 상관은 FORD
13 개의 행이 선택되었습니다.
JOIN ~ ON 절을 사용한 SELF JOIN
오라클 9i에서 JOIN ~ ON 지원
1 SELECT E1.ENAME || '의 상관은 ' || E2.ENAME
2 FROM EMP E1 JOIN EMP E2
3* ON E1.MGR = E2.EMPNO
SQL> /
E1.ENAME||'의상관은'||E2.ENAME
------------------------------
SCOTT의 상관은 JONES
FORD의 상관은 JONES
ALLEN의 상관은 BLAKE
WARD의 상관은 BLAKE
JAMES의 상관은 BLAKE
TURNER의 상관은 BLAKE
MARTIN의 상관은 BLAKE
MILLER의 상관은 CLARK
ADAMS의 상관은 SCOTT
JONES의 상관은 KING
CLARK의 상관은 KING
BLAKE의 상관은 KING
SMITH의 상관은 FORD
13 개의 행이 선택되었습니다.
OUTER JOIN
개요
-EQUI JOIN의 조인 조건에서 양측 칼럼 값 중, 어느 하나라도 NULL 이면 ‘=‘ 비교 결과가 거짓이
되어 NULL 값을 가진 행은 조인 결과로 출력 불가
NULL 에 대해서 어떠한 연산을 적용하더라고 연산 결과는 NULL
-EQUI JOIN에서 양측 칼럼 값중의 하나가 NULL 이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용
(+) 기호를 사용한 OUTER JOIN
WHERE 절의 조인 조건에서 OUTER JOIN 연산자인 ‘(+)’ 기호 사용
조인 조건문에서 NULL 이 출력되는 테이블의 칼럼에 (+) 기호 추가
OUTER JOIN의 제약사항
OUTER JOIN 연산자(+)는 NULL이 존재하는 칼럼쪽에 표시
OUTER JOIN에서는 IN 연산자를 사용 불가
다른 조건과 OR 연산자로 결합 불가
OUTER JOIN ~ON 절을 사용한 OUTER JOIN
오라클 9i 버전부터 OUTER JOIN 절 사용
LEFT OUTER JOIN
FROM 절의 왼쪽에 위치한 테이블이 NULL 을 가질 경우에 사용
WHERE절의 오른편에 ‘(+)’ 기호를 추가한 것과 동일
예 : 지도교수가 배정되지 않은 학생 명단을 출력하여라
RIGHT OUTER JOIN
FROM 절의 오른쪽에 위치한 테이블이 NULL 을 가질 경우, 사용
WHERE 절의 왼편’(+)’ 기호를 추가한 것과 동일
예
지도학생이 배정되지 않은 교수 명단을 출력하여라.
FULL OUTER JOIN
LEFT OUTER JOIN 과 RIGHT OUTER JOIN 을 동시에 실행한 결과를 출력
예
지도교수가 없는 학생과 지도학생이 배정되지 않은 교수를 함께 출력하라.
1 CREATE TABLE MEMBER
2 (
3 NO NUMBER(5),
4 NAME VARCHAR2(10)
5* )
SQL> /
테이블이 생성되었습니다.
1 CREATE TABLE M_INFO(
3 NO NUMBER(5),
4 HOBBY VARCHAR2(10)
5* )
SQL> /
테이블이 생성되었습니다.
SQL> BEGIN
2 FOR z in 1..10 loop
3 insert into member values(z,'mem'||z);
4 end loop;
5 end;
6 /
PL/SQL 처리가 정상적으로 완료되었습니다.
SQL> select * from member;
NO NAME
---------- ----------
1 mem1
2 mem2
3 mem3
4 mem4
5 mem5
6 mem6
7 mem7
8 mem8
9 mem9
10 mem10
10 개의 행이 선택되었습니다.
SQL> BEGIN
2 FOR z in 6..16 loop
3 insert into m_info values(z,'h'||z);
4 end loop;
5 end;
6 /
SQL> select * from m_info;
NO HOBBY
---------- ----------
6 h6
7 h7
8 h8
9 h9
10 h10
11 h11
12 h12
13 h13
14 h14
15 h15
16 h16
11 개의 행이 선택되었습니다.
SQL> select m1.no, m1.name, m2.no , m2. hobby
2 from member m1, M_info m2
3 where m1.no = m2.no;
NO NAME NO HOBBY
---------- ---------- ---------- ----------
6 mem6 6 h6
7 mem7 7 h7
8 mem8 8 h8
9 mem9 9 h9
10 mem10 10 h10
null 없는 것만 출력, 공통된것만 가져와라
member 의 null 값도 출력
1 select m1.no, m2.hobby
2 from member m1, m_info m2
3* where m1.no = m2.no(+) /// + 기준으로 왼쪽 값을 출력하라
SQL> /
NO HOBBY
---------- ----------
1
2
3
4
5
6 h6
7 h7
8 h8
9 h9
10 h10
10 개의 행이 선택되었습니다.
1 select m1.no, m2.hobby
2 from member m1 left outer join m_info m2
3* on m1.no = m2.no
SQL> /
NO HOBBY
---------- ----------
6 h6
7 h7
8 h8
9 h9
10 h10
5
3
1
2
4
10 개의 행이 선택되었습니다.
SQL> 1 select m1.no, m2.hobby
SQL> 2 from member m1 right outer join m_info m2
SQL> 3 on m1.no = m2.no
SQL> /
NO HOBBY
---------- ----------
6 h6
7 h7
8 h8
9 h9
10 h10
h13
h15
h12
h11
h14
h16
11 개의 행이 선택되었습니다.
SQL> 1 select m1.no, m2.hobby
SQL> 2 from member m1, m_info m2
SQL> 3* where m1.no(+) = m2.no
SQL> /
NO HOBBY
---------- ----------
6 h6
7 h7
8 h8
9 h9
10 h10
h11
h12
h13
h14
h15
h16
11 개의 행이 선택되었습니다.
SQL> 1 select m1.no, m2.hobby
SQL> 2 from member m1, m_info m2
SQL> 3 where m1.no(+) = m2.no(+)
SQL> /
where m1.no(+) = m2.no(+)
*
3행에 오류:
ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
SQL> 1 select m1.no, m2.hobby
SQL> 2 from member m1 full outer join m_info m2
SQL> 3 on m1.no = m2.no
SQL> /
NO HOBBY
---------- ----------
6 h6
7 h7
8 h8
9 h9
10 h10
5
3
1
2
4
h13
h15
h12
h11
h14
h16
16 개의 행이 선택되었습니다.
숙제
===============
* 학생, 교수, 부서테이블을 참조하여 답하시오.
1. 학생테이블과 부서테이블을 EQUI-JOIN하여 학번, 이름, 학과번호, 소속학과이름, 학과위치를 출력하여라.
2. NATURAL JOIN을 이용하여 교수번호, 이름, 학과번호, 학과이름을 출력하여라
3. NATURAL JOIN을 이용하여 4학년 학생의 이름, 학년, 학과번호와 학과이름을 출력하여라
4. JOIN~USING절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치를 출력하여라.
5. EQUI JOIN의 3가지 방법을 이용하여 성이 ‘김’씨의 학생들의 이름, 학과이름, 학과위치를 출력하여라.
6. 학생테이블과 교수테이블을 조인하여 이름, 학년, 지도교수이름, 직급을 출력하여라
단, 지도 학생을 배정받지 않은 교수이름도 함께 출력하여라
7. 부서테이블에서 SELF JOIN을 이용하여 부서이름과 상위 부서이름을 출력하여라
출력결과 ; 정보미디어학부의 소속은 공과대학