JOIN
흩어져 있는 데이터를 서로 관계시킴
- 데이터의 수평적 결합
- 수직적 결합 : 집합연산자(Union, Minus, Intersection)
- 내용에 의한 참조(Content Reference)
- 서로 다른 TABLE에 나누어 저장되어 있는 정보를 조회 시 JOIN 연산
- FROM에 복수개의 TABLE
WHERE조건문에 JOIN 연산 & 필터링 실행
- ANSI-SQL의 경우
WHERE필터링,ON조인 조건 분리- 결과 집합
SELECT,JOIN에 의한 결과JOIN은 서로 다른 2개의 TABLE간의 결합만 가능- 복수 TABLE의
JOIN실행 : 하나의JOIN실행 → 결과 집합 생성 →JOIN연쇄 실행 → …- JOIN 방식(nested loop, merge, hash)
- 필요성
- 관계형 데이터베이스는 테이블 독립성, 데이터의 최소 중복성, 데이터간의 함수적 종속성을 통해
각각의 데이터를 각각의 테이블로 나누어(분할) 저장하도록 설계
SELECT DNAME, ENAME,SAL FROM DEPT, EMP
WHERE DEPT.DEPTNO = EMP.DEPTNO ;
DNAME ENAME SAL
-------------- ---------- ----------
RESEARCH SMITH 800
SALES ALLEN 1600
SALES WARD 1250
RESEARCH JONES 2975
SALES MARTIN 1250
SALES BLAKE 2850
ACCOUNTING CLARK 2450
RESEARCH SCOTT 3000
ACCOUNTING KING 9500
SALES TURNER 9000
RESEARCH ADAMS 1100
DNAME ENAME SAL
-------------- ---------- ----------
SALES JAMES 950
RESEARCH FORD 3000
ACCOUNTING MILLER 1300
종류
desc emp
이름 널? 유형
-------- -------- ------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
desc dept
이름 널? 유형
------ -- ------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
desc salgrade
이름 널? 유형
----- -- ------
GRADE NUMBER
LOSAL NUMBER
HISAL NUMBER
select * from salgrade;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Equi-Join(Simple Join, Inner Join)
- 가장 일반적인 조인
- 동등 연산자(
=) 사용 - 공통적으로 존재하는 데이터의 값이 일치되는 행 연결
WHERE절의 TABLE을 미지정할 시 에러(열의 정의가 애매합니다) 발생- Schima : ~소유의, login 계정 소유
SCOTT: 현재 DB의 User nameSCOTT.EMP: SCOTT 소유의 EMP TABLE
- 필터링 이후(data size↓) JOIN 처리
SELECT DNAME,ENAME,JOB,SAL FROM EMP, DEPT WHERE DEPTNO = DEPTNO;
-- ➔ORA-00918: 열의 정의가 애매합니다.. 이유는?
명령의 1 행에서 시작하는 중 오류 발생 -
SELECT DNAME,ENAME,JOB,SAL FROM EMP, DEPT WHERE DEPTNO = DEPTNO
오류 발생 명령행: 1 열: 58
오류 보고 -
SQL 오류: ORA-00918: 열의 정의가 애매합니다
00918. 00000 - "column ambiguously defined"
*Cause:
*Action:
SELECT DNAME,ENAME,JOB,SAL FROM SCOTT.EMP, SCOTT.DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO;
-- OBJECT Full name표기법: [SCHEMA.]OBJECT_NAME EX) EMP ➔SCOTT.EMP
-- COLUMN Full name 표기법: [TABLE_NAME.]COLUMN_NAME EX) EMPNO ➔EMP.EMPNO
DNAME ENAME JOB SAL
-------------- ---------- --------- ----------
RESEARCH SMITH CLERK 800
SALES ALLEN SALESMAN 1600
SALES WARD SALESMAN 1250
...
14개 행이 선택되었습니다.
SELECT DNAME,ENAME,JOB,SAL FROM EMP, DEPT
WHERE
EMP.DEPTNO = DEPT.DEPTNO AND -- JOIN구문
EMP.JOB IN ('MANAGER','CLERK') -- 필터링 구문
ORDER BY DNAME;
--조건절 처리 순서?? (a)JOIN 처리(EMP.DEPTNO = DEPT.DEPTNO)→필터링(EMP.JOB IN ('MANAGER','CLERK'))
--(b) 필터링 →JOIN 처리 ✅
DNAME ENAME JOB SAL
-------------- ---------- --------- ----------
ACCOUNTING CLARK MANAGER 2450
ACCOUNTING MILLER CLERK 1300
RESEARCH SMITH CLERK 800
RESEARCH JONES MANAGER 2975
RESEARCH ADAMS CLERK 1100
SALES BLAKE MANAGER 2850
SALES JAMES CLERK 950
7개 행이 선택되었습니다.
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- TABLE ALIAS (a) 편의성 (b) 가독성(의미있는 이름사용) EX) EMP E , EMP A
-- (c) SelfJoin시 필수 사용 (d) 동일 컬럼명이 존재하는 경우
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO; // ANSI-SQL, ON 조인조건
14개 행이 선택되었습니다.
DNAME ENAME JOB SAL
-------------- ---------- --------- ----------
RESEARCH SMITH CLERK 800
SALES ALLEN SALESMAN 1600
SALES WARD SALESMAN 1250
...
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO -- 조인 조건
WHERE E.DEPTNO IN (10,20) AND D.DNAME = 'RESEARCH'; // 필터링 조건
DNAME ENAME JOB SAL
-------------- ---------- --------- ----------
RESEARCH SMITH CLERK 800
RESEARCH JONES MANAGER 2975
RESEARCH SCOTT ANALYST 3000
RESEARCH ADAMS CLERK 1100
RESEARCH FORD ANALYST 3000
Non Equi-join
- 동등 연산자(
=) 이외의 비교 연산자 사용
SELECT E.ENAME, E.JOB,E.SAL,S.GRADE, S.LOSAL, S.HISAL FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL; // 범위 연산자
ENAME JOB SAL GRADE LOSAL HISAL
---------- --------- ---------- ---------- ---------- ----------
SMITH CLERK 800 1 700 1200
JAMES CLERK 950 1 700 1200
ADAMS CLERK 1100 1 700 1200
WARD SALESMAN 1250 2 1201 1400
MARTIN SALESMAN 1250 2 1201 1400
MILLER CLERK 1300 2 1201 1400
ALLEN SALESMAN 1600 3 1401 2000
CLARK MANAGER 2450 4 2001 3000
BLAKE MANAGER 2850 4 2001 3000
JONES MANAGER 2975 4 2001 3000
FORD ANALYST 3000 4 2001 3000
ENAME JOB SAL GRADE LOSAL HISAL
---------- --------- ---------- ---------- ---------- ----------
SCOTT ANALYST 3000 4 2001 3000
TURNER SALESMAN 9000 5 3001 9999
KING PRESIDENT 9500 5 3001 9999
14개 행이 선택되었습니다.
E.DEPTNO = D.DEPTNO(1번째 결과집합)
→AND E.SAL(2번째 결과집합)
→BETWEEN S.LOSAL AND S.HISAL(3번째 결과집합)
// 다수 TABLE의 JOIN 실행
-- 3개 테이블JOIN , 최소JOIN조건: N(테이블개수) –1
SELECT DNAME,ENAME,JOB,SAL,GRADE
FROM EMP E, DEPT D, SALGRADE S -- Join 순서는 누가 결정? , 기술 순서? KMA
WHERE E.DEPTNO = D.DEPTNO AND E.SAL BETWEEN S.LOSAL AND S.HISAL;
DNAME ENAME JOB SAL GRADE
-------------- ---------- --------- ---------- ----------
ACCOUNTING KING PRESIDENT 9500 5
SALES TURNER SALESMAN 9000 5
RESEARCH FORD ANALYST 3000 4
RESEARCH SCOTT ANALYST 3000 4
RESEARCH JONES MANAGER 2975 4
SALES BLAKE MANAGER 2850 4
ACCOUNTING CLARK MANAGER 2450 4
SALES ALLEN SALESMAN 1600 3
ACCOUNTING MILLER CLERK 1300 2
SALES MARTIN SALESMAN 1250 2
SALES WARD SALESMAN 1250 2
DNAME ENAME JOB SAL GRADE
-------------- ---------- --------- ---------- ----------
RESEARCH ADAMS CLERK 1100 1
SALES JAMES CLERK 950 1
RESEARCH SMITH CLERK 800 1
14개 행이 선택되었습니다.
- GRADE가 5~로 중복(2등급이라면 5,4,3,2)
SELECT E.DEPTNO, E.ENAME, E.JOB,E.SAL,S.LOSAL,S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL < S.LOSAL AND E.DEPTNO IN (10,30)
ORDER BY E.ENAME;
-- 원하는 결과가 아닌 무의미(불필요)한 다량의 곱집합(Cartesian product) 결과가발생한 이유는?
-- JOIN 조건X
DEPTNO ENAME JOB SAL LOSAL GRADE
---------- ---------- --------- ---------- ---------- ----------
30 ALLEN SALESMAN 1600 3001 5
30 ALLEN SALESMAN 1600 2001 4
30 BLAKE MANAGER 2850 3001 5
10 CLARK MANAGER 2450 3001 5
30 JAMES CLERK 950 1401 3
30 JAMES CLERK 950 1201 2
30 JAMES CLERK 950 2001 4
30 JAMES CLERK 950 3001 5
30 MARTIN SALESMAN 1250 1401 3
30 MARTIN SALESMAN 1250 3001 5
30 MARTIN SALESMAN 1250 2001 4
DEPTNO ENAME JOB SAL LOSAL GRADE
---------- ---------- --------- ---------- ---------- ----------
10 MILLER CLERK 1300 2001 4
10 MILLER CLERK 1300 1401 3
10 MILLER CLERK 1300 3001 5
30 WARD SALESMAN 1250 1401 3
30 WARD SALESMAN 1250 2001 4
30 WARD SALESMAN 1250 3001 5
17개 행이 선택되었습니다.
Outer-Join
- 조인 조건에 직접적으로 만족되지 않는 행을 연결하여 결과 집합 생성
- Inner-Join의 반대 개념
- ex) DEPT 40부서의 실제 근무자가 존재 X (EMP의 DEPTNO=40가 존재 X)
(+): 결과 집합 중 data가 부족한 TABLE에 NULL을 채움(+)가 없는 TABLE의 data가 많음 → 기준 TABLE(+)의 방향이 잘못되어 불필요한 연산 발생 가능성 존재
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DNAME;
-- 40번부서(OPERATIONS)에 근무하는 직원이 없기 때문에
-- EquiJoin에서는40번부서관련 정보 조회가 안된다. 14건
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+)
ORDER BY D.DNAME;
--Equi-Join과 동일한 결과 출력, 불필요한 아웃터 조인은 비효율적인 자원 사용 유발 가능
DNAME ENAME JOB SAL
-------------- ---------- --------- ----------
ACCOUNTING CLARK MANAGER 2450
ACCOUNTING KING PRESIDENT 9500
...
SALES BLAKE MANAGER 2850
SALES MARTIN SALESMAN 1250
14개 행이 선택되었습니다.
SELECT D.DEPTNO, D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO(+)= D.DEPTNO
ORDER BY D.DNAME;
-- 기준이되는 테이블(DEPT)과 조인되는 대상 테이블(EMP)의 조인 조건에 (+) 표시,
-- 직접 매핑되지 않는 컬럼에는 NULL , 15건 (OPERATIONS ?)
DEPTNO DNAME ENAME JOB SAL
---------- -------------- ---------- --------- ----------
10 ACCOUNTING CLARK MANAGER 2450
10 ACCOUNTING KING PRESIDENT 9500
10 ACCOUNTING MILLER CLERK 1300
76 LOCAL_PART1
86 LOCAL_PART2
50 M연구소
40 OPERATIONS
66 OUTER_BLK
20 RESEARCH JONES MANAGER 2975
20 RESEARCH FORD ANALYST 3000
20 RESEARCH SMITH CLERK 800
DEPTNO DNAME ENAME JOB SAL
---------- -------------- ---------- --------- ----------
20 RESEARCH ADAMS CLERK 1100
20 RESEARCH SCOTT ANALYST 3000
30 SALES JAMES CLERK 950
30 SALES TURNER SALESMAN 9000
30 SALES BLAKE MANAGER 2850
30 SALES MARTIN SALESMAN 1250
30 SALES WARD SALESMAN 1250
30 SALES ALLEN SALESMAN 1600
51 T연구소
20개 행이 선택되었습니다.
SELECT D.DNAME,NVL(E.ENAME,'비상근 부서'),E.JOB,E.SAL FROM EMP E,DEPT D
WHERE E.DEPTNO(+) = D.DEPTNO
ORDER BY D.DNAME;
-- 직접 매핑되지 않는 컬럼에는 NULL
DNAME NVL(E.ENAM JOB SAL
-------------- ---------- --------- ----------
ACCOUNTING CLARK MANAGER 2450
ACCOUNTING KING PRESIDENT 9500
ACCOUNTING MILLER CLERK 1300
LOCAL_PART1 비상근 부서
LOCAL_PART2 비상근 부서
M연구소 비상근 부서
OPERATIONS 비상근 부서
OUTER_BLK 비상근 부서
RESEARCH JONES MANAGER 2975
RESEARCH FORD ANALYST 3000
RESEARCH SMITH CLERK 800
DNAME NVL(E.ENAM JOB SAL
-------------- ---------- --------- ----------
RESEARCH ADAMS CLERK 1100
RESEARCH SCOTT ANALYST 3000
SALES JAMES CLERK 950
SALES TURNER SALESMAN 9000
SALES BLAKE MANAGER 2850
SALES MARTIN SALESMAN 1250
SALES WARD SALESMAN 1250
SALES ALLEN SALESMAN 1600
T연구소 비상근 부서
20개 행이 선택되었습니다.
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO(+)
ORDER BY D.DNAME;
-- ORACLE SQL은 양방향OUTER JOIN을 허용하지 않는다.
-- ANSI-SQL 1999 에서는 양방향 OUTER JOIN을 허용
명령의 18 행에서 시작하는 중 오류 발생 -
SELECT D.DNAME,E.ENAME,E.JOB,E.SAL FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO(+)
ORDER BY D.DNAME
오류 발생 명령행: 18 열: 72
오류 보고 -
SQL 오류: ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다
01468. 00000 - "a predicate may reference only one outer-joined table"
*Cause:
*Action:
Self-Join
- 하나의 테이블이 자기 자신(Self)과 조인을 하여 결과 집합 생성
SELECT E.ENAME||' ''S MANAGER IS '||M.ENAME
FROM EMP E, EMP M
WHERE E.MGR = M.EMPNO
ORDER BY M.ENAME;
/*
같은 테이블 끼리 조인
테이블 개체내 참조 무결성 관계시 ex) empnovs mgr
테이블Alias 필수 사용
*/
E.ENAME||'''SMANAGERIS'||M.ENAME
-----------------------------------
MARTIN 'S MANAGER IS BLAKE
JAMES 'S MANAGER IS BLAKE
WARD 'S MANAGER IS BLAKE
ALLEN 'S MANAGER IS BLAKE
TURNER 'S MANAGER IS BLAKE
MILLER 'S MANAGER IS CLARK
SMITH 'S MANAGER IS FORD
SCOTT 'S MANAGER IS JONES
FORD 'S MANAGER IS JONES
CLARK 'S MANAGER IS KING
JONES 'S MANAGER IS KING
E.ENAME||'''SMANAGERIS'||M.ENAME
-----------------------------------
BLAKE 'S MANAGER IS KING
ADAMS 'S MANAGER IS SCOTT
13개 행이 선택되었습니다.
CARTESIAN PRODUCT
- Join시 두 테이블(집합)간에 곱집합 연산으로 유용하지 않은 대량의 데이터를 생성하는 현상
- 발생원인
- Join 조건 생략
- 잘못된JOIN 조건
- 용도
- 테스트용 샘플데이타 생성
- 곱집합 기능을 이용한 빠른 연산 응용
SELECT ENAME,JOB,DNAME FROM EMP, DEPT;
SELECT ENAME,JOB,DNAME FROM EMP CROSS JOIN DEPT; // Ansi-SQL
-- Join 조건 생략시 발생 , 데이터 건수 체크
ENAME JOB DNAME
---------- --------- --------------
SMITH CLERK M연구소
ALLEN SALESMAN M연구소
WARD SALESMAN M연구소
JONES MANAGER M연구소
...
SMITH CLERK T연구소
ALLEN SALESMAN T연구소
WARD SALESMAN T연구소
JONES MANAGER T연구소
...
SMITH CLERK OPERATIONS
ALLEN SALESMAN OPERATIONS
WARD SALESMAN OPERATIONS
JONES MANAGER OPERATIONS
MARTIN SALESMAN OPERATIONS
BLAKE MANAGER OPERATIONS
CLARK MANAGER OPERATIONS
SCOTT ANALYST OPERATIONS
KING PRESIDENT OPERATIONS
TURNER SALESMAN OPERATIONS
ADAMS CLERK OPERATIONS
JAMES CLERK OPERATIONS
FORD ANALYST OPERATIONS
MILLER CLERK OPERATIONS
126개 행이 선택되었습니다.
SELECT ENAME,JOB,DNAME FROM EMP , DEPT
WHERE EMP.SAL > 2000 and DEPT.DEPTNO IN (10,20);
-- 필터링 조건(O) , Join 조건(X) , and 와 데이터 건수 체크
ENAME JOB DNAME
---------- --------- --------------
JONES MANAGER ACCOUNTING
BLAKE MANAGER ACCOUNTING
CLARK MANAGER ACCOUNTING
SCOTT ANALYST ACCOUNTING
KING PRESIDENT ACCOUNTING
TURNER SALESMAN ACCOUNTING
FORD ANALYST ACCOUNTING
JONES MANAGER RESEARCH
BLAKE MANAGER RESEARCH
CLARK MANAGER RESEARCH
SCOTT ANALYST RESEARCH
KING PRESIDENT RESEARCH
TURNER SALESMAN RESEARCH
FORD ANALYST RESEARCH
14개 행이 선택되었습니다.
SELECT ENAME,JOB,DNAME FROM EMP , DEPT
WHERE EMP.SAL > 2000 or DEPT.DEPTNO IN (10,20);
-- 필터링 조건(O) , Join 조건(X) , or 와 데이터 건수 체크
ENAME JOB DNAME
---------- --------- --------------
JONES MANAGER M연구소
BLAKE MANAGER M연구소
CLARK MANAGER M연구소
SCOTT ANALYST M연구소
KING PRESIDENT M연구소
TURNER SALESMAN M연구소
FORD ANALYST M연구소
...
JONES MANAGER OUTER_BLK
BLAKE MANAGER OUTER_BLK
CLARK MANAGER OUTER_BLK
SCOTT ANALYST OUTER_BLK
KING PRESIDENT OUTER_BLK
TURNER SALESMAN OUTER_BLK
FORD ANALYST OUTER_BLK
...
JONES MANAGER OPERATIONS
BLAKE MANAGER OPERATIONS
CLARK MANAGER OPERATIONS
SCOTT ANALYST OPERATIONS
KING PRESIDENT OPERATIONS
TURNER SALESMAN OPERATIONS
FORD ANALYST OPERATIONS
77개 행이 선택되었습니다.
데이터 생성 실행파일
- TABLE 생성 전 DROP 실행 : 동일 TABLE 생성(오류) 방지
- TABLE 처음 생성 시 의미 있는 초기 데이터 생성
- 개발 단계에서 참고 수월
DROP TABLE RESOURCE_USAGE;
DROP TABLE SYSTEM;
CREATE TABLE SYSTEM(
SYSTEM_ID VARCHAR2(5),
SYSTEM_NAME VARCHAR2(12)
);
INSERT INTO SYSTEM VALUES('XXX','혜화DB');
INSERT INTO SYSTEM VALUES('YYY','강남DB');
INSERT INTO SYSTEM VALUES('ZZZ','영등포DB');
CREATE TABLE RESOURCE_USAGE(
SYSTEM_ID VARCHAR2(5),
RESOURCE_NAME VARCHAR2(10)
);
INSERT INTO RESOURCE_USAGE VALUES('XXX','FTP');
INSERT INTO RESOURCE_USAGE VALUES('YYY','FTP');
INSERT INTO RESOURCE_USAGE VALUES('YYY','TELNET');
INSERT INTO RESOURCE_USAGE VALUES('YYY','EMAIL');
COMMIT;
SELECT
S.SYSTEM_ID,
S.SYSTEM_NAME,
DECODE(R.RESOURCE_NAME, 'FTP', '사용', '미사용') AS FTP,
DECODE(R.RESOURCE_NAME, 'TELNET', '사용', '미사용') AS TELNET,
DECODE(R.RESOURCE_NAME, 'EMAIL', '사용', '미사용') AS EMAIL
FROM SYSTEM S,RESOURCE_USAGE R
WHERE S.SYSTEM_ID = R.SYSTEM_ID(+);
Table RESOURCE_USAGE이(가) 삭제되었습니다.
Table SYSTEM이(가) 삭제되었습니다.
Table SYSTEM이(가) 생성되었습니다.
1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.
Table RESOURCE_USAGE이(가) 생성되었습니다.
1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.
1 행 이(가) 삽입되었습니다.
커밋 완료.
SYSTE SYSTEM_NAME FTP TEL EMA
----- ------------ --- --- ---
XXX 혜화DB 사용 미사용 미사용
YYY 강남DB 사용 미사용 미사용
YYY 강남DB 미사용 사용 미사용
YYY 강남DB 미사용 미사용 사용
ZZZ 영등포DB 미사용 미사용 미사용
C
Contents