JOIN
- DB에서 여러 테이블의 데이터가 필요한 경우 사용된다
- 어떤 테이블을 기준으로 다른 테이블에 있는 ROW를 찾아오는 것
- 서로 독립적인 데이터들간의 조인을 이용해서 필요한 정보를 참조한다
- 해당열에 존재하는 공통 값, 일반적으로 PK(기본키) 및 FK(외래키) 열을 조건으로 사용하여 한 테이블의 행을 다른 테이블의 행에 조인할 수 있다.
- N개의 테이블을 조인하려면 최소 N-1개의 조인 조건이 필요하다
- 조인 조건이 존재하지 않는다면 존재하는 모든 경우의 수를 조합하여 출력하기 때문에 데이터의 양이 비정상적으로 늘어난다
- ANSI 방식의 JOIN과 ORACLE 방식의 JOIN 문법이 존재한다.
Q1 ) EMP테이블과 DEPT테이블을 합쳐서 출력하라
SQL> SELECT *
2 FROM EMP,DEPT
3 ORDER BY EMPNO; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
-------- -------------------- ------------------ -------- -------- -------- -------- -------- -------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 10 ACCOUNTING NEW YORK
7369 SMITH CLERK 7902 80/12/17 800 20 30 SALES CHICAGO
7369 SMITH CLERK 7902 80/12/17 800 20 20 RESEARCH DALLAS
7369 SMITH CLERK 7902 80/12/17 800 20 40 OPERATIONS BOSTON
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAGO
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 10 ACCOUNTING NEW YORK
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 40 OPERATIONS BOSTON
.
.
.조건이 설정되어 있지 않기 때문에 모든 조건에 대해서 결과값이 출력된다.
Q2) EMP 테이블과 DEPT 테이블의 DEPTNO가 같은 경우로 출력하라
SQL> SELECT * FROM EMP,DEPT
2 WHERE EMP.DEPTNO = DEPT.DEPTNO
3 ORDER BY EMPNO; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC
-------- -------------------- ------------------ -------- -------- -------- -------- -------- -------- ---------------------------- --------------------------
7369 SMITH CLERK 7902 80/12/17 800 20 20 RESEARCH DALLAS
7499 ALLEN SALESMAN 7698 81/02/20 1600 300 30 30 SALES CHICAGO
7521 WARD SALESMAN 7698 81/02/22 1250 500 30 30 SALES CHICAGO
7566 JONES MANAGER 7839 81/04/02 2975 20 20 RESEARCH DALLAS
7654 MARTIN SALESMAN 7698 81/09/28 1250 1400 30 30 SALES CHICAGO
7698 BLAKE MANAGER 7839 81/05/01 2850 30 30 SALES CHICAGO
7782 CLARK MANAGER 7839 81/06/09 2450 10 10 ACCOUNTING NEW YORK
7839 KING PRESIDENT 81/11/17 5000 10 10 ACCOUNTING NEW YORK
7844 TURNER SALESMAN 7698 81/09/08 1500 0 30 30 SALES CHICAGO
7900 JAMES CLERK 7698 81/12/03 950 30 30 SALES CHICAGO
7902 FORD ANALYST 7566 81/12/03 3000 20 20 RESEARCH DALLAS
7934 MILLER CLERK 7782 82/01/23 1300 10 10 ACCOUNTING NEW YORK양 테이블의 DEPTNO가 같다는 조건을 붙였기 때문에 데이터의 양이 크게 정제되어 출력된 것을 확인할 수 있다.
1. INNER JOIN
- 각 테이블의 특정 열에 일치한 데이터를 기준으로 조인을 하는 방식
- 대부분 가장 많이 사용하는 조인 형식
- 두 테이블에 대해 교집합의 요소를 가져온다
- EQUI JOIN, SIMPLE JOIN이라고도 부른다
1) ANSI
- USING 문을 사용하면 컬럼을 선택해서 조인할 수 있다.
- ON은 다른 칼럼을 조회할 떄
Q1 ) INNER JOIN을 사용하여 TEST_EMP 테이블의 사원번호, 이름, 부서번호, 부서명을 조회하자
SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME FROM TEST_EMP JOIN DEPT USING(DEPTNO);
EMPNO ENAME DEPTNO DNAME
-------- -------------------- -------- ----------------------------
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
7566 JONES 20 RESEARCH
7369 SMITH 20 RESEARCH
333 333 20 RESEARCH
444 444 20 RESEARCH
7902 FORD 20 RESEARCH
7844 TURNER 30 SALES
7900 JAMES 30 SALES
222 222 30 SALES
7499 ALLEN 30 SALES
111 111 30 SALES
7521 WARD 30 SALES
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
Q2 ) TEST_EMP 테이블에 부서배치 받지 않는 사원을 추가해서 INNER JOIN을 실행하자
SQL> INSERT INTO TEST_EMP(ENAME,DEPTNO) VALUES(777,NULL);
1 row created.
SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME FROM TEST_EMP JOIN DEPT USING(DEPTNO);
EMPNO ENAME DEPTNO DNAME
-------- -------------------- -------- ----------------------------
7782 CLARK 10 ACCOUNTING
7934 MILLER 10 ACCOUNTING
7839 KING 10 ACCOUNTING
333 333 20 RESEARCH
7369 SMITH 20 RESEARCH
7902 FORD 20 RESEARCH
444 444 20 RESEARCH
7566 JONES 20 RESEARCH
222 222 30 SALES
111 111 30 SALES
7900 JAMES 30 SALES
7844 TURNER 30 SALES
7698 BLAKE 30 SALES
7654 MARTIN 30 SALES
7521 WARD 30 SALES
7499 ALLEN 30 SALES부서배치가 안되었기 때문에 조인문의 조건에 맞지않아 '777'이라는 사원은 출력되지 않았다.
2. ORACLE 방식
1) 주의할 점
SQL> SELECT EMPNO, ENAME, DEPTNO, DNAME, LOC
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO;
SELECT EMPNO, ENAME, DEPTNO, DNAME, LOC
*
ERROR at line 1:
ORA-00918: column ambiguously definedEMP 테이블과 DEPT 테이블의 공통된 데이터인 DEPTNO를 통해 조인하려했지만 실패했다
이유는 어느 테이블의 속해있는 DEPTNO인지 구분하지 못하기 때문이다.
Q1 ) INNER JOIN을 사용하여 EMP 테이블의 사원번호, 이름, 부서번호, 부서명을 조회하자
SQL> SELECT E.ENAME,E.EMPNO, D.DEPTNO, D.DNAME, D.LOC
2 FROM EMP E, DEPT D
3 WHERE E.DEPTNO = D.DEPTNO
4 ORDER BY E.DEPTNO, D.DEPTNO;
ENAME EMPNO DEPTNO DNAME LOC
-------------------- -------- -------- ---------------------------- --------------------------
CLARK 7782 10 ACCOUNTING NEW YORK
KING 7839 10 ACCOUNTING NEW YORK
MILLER 7934 10 ACCOUNTING NEW YORK
SMITH 7369 20 RESEARCH DALLAS
FORD 7902 20 RESEARCH DALLAS
JONES 7566 20 RESEARCH DALLAS
JAMES 7900 30 SALES CHICAGO
TURNER 7844 30 SALES CHICAGO
MARTIN 7654 30 SALES CHICAGO
WARD 7521 30 SALES CHICAGO
ALLEN 7499 30 SALES CHICAGO
BLAKE 7698 30 SALES CHICAGO위와같이 어느 테이블의 어느 데이터인지 명시를 해줘야 정확히 출력이 되는것을 확인 할 수 있다.
# SALESMAN의 사원번호, 이름, 급여, 부서명, 근무지를 리턴한다
1) ANSI
SQL> SELECT EMPNO, ENAME, SAL, JOB, DNAME, LOC
2 FROM EMP
3 JOIN DEPT USING(DEPTNO)
4 WHERE JOB = 'SALESMAN';
EMPNO ENAME SAL JOB LOC
-------- -------------------- -------- ------------------ --------------------------
7499 ALLEN 1600 SALESMAN CHICAGO
7844 TURNER 1500 SALESMAN CHICAGO
7654 MARTIN 1250 SALESMAN CHICAGO
7521 WARD 1250 SALESMAN CHICAGO
2. ORACLE
SQL> SELECT E.ENAME,E.EMPNO, D.DEPTNO, D.DNAME, D.LOC
2 FROM EMP E, DEPT D
3 WHERE E.JOB = 'SALESMAN' AND E.DEPTNO = D.DEPTNO;
ENAME EMPNO DEPTNO DNAME LOC
-------------------- -------- -------- ---------------------------- --------------------------
ALLEN 7499 30 SALES CHICAGO
TURNER 7844 30 SALES CHICAGO
MARTIN 7654 30 SALES CHICAGO
WARD 7521 30 SALES CHICAGO
2. OUTER JOIN(외부조인)
- 주종관계를 만들어서 주 테이블은 전체 출력, 종 테이블은 True값만 출력
- 어느 열의 어느 한쪽이 NULL값이어도 강제로 출력하는 방식이 OUTER JOIN이다.
Q) EMP 테이블과 DEPT 테이블에서 EMP 테이블에 있는 모든 자료를 사원번호, 이름, 직업 DEPT 테이블의 부서번호, 부서명을 조회하자
1) ANSI
SQL> SELECT EMPNO,ENAME,JOB,DEPTNO,DNAME
2 FROM EMP LEFT OUTER JOIN DEPT USING(DEPTNO);
EMPNO ENAME JOB DEPTNO DNAME
-------- -------------------- ------------------ -------- ----------------------------
7934 MILLER CLERK 10 ACCOUNTING
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALESSQL> SELECT EMPNO,ENAME,JOB,DEPTNO,DNAME
2 FROM EMP RIGHT OUTER JOIN DEPT USING(DEPTNO);
EMPNO ENAME JOB DEPTNO DNAME
-------- -------------------- ------------------ -------- ----------------------------
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7934 MILLER CLERK 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
40 OPERATIONS
2) ORACLE
SQL> SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME
2 FROM DEPT D, EMP E
3 WHERE D.DEPTNO = E.DEPTNO(+);
EMPNO ENAME JOB DEPTNO DNAME
-------- -------------------- ------------------ -------- ----------------------------
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7934 MILLER CLERK 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
40 OPERATIONSDEPT 테이블을 주 테이블, EMP 테이블을 종 테이블로 설정하고, 종 테이블의 조건에 (+)를 사용해야 한다.
이 OUTER JOIN을 LEFT OUTER JOIN이라고 부른다.
DEPT 테이블을 주 테이블로 구성했기에 DEPT 테이블의 DNAME 데이터가 전부 출력된 것을 확인할 수 있다.
SQL> SELECT E.EMPNO, E.ENAME, E.JOB, D.DEPTNO, D.DNAME
2 FROM DEPT D, EMP E
3 WHERE D.DEPTNO(+) = E.DEPTNO;
EMPNO ENAME JOB DEPTNO DNAME
-------- -------------------- ------------------ -------- ----------------------------
7934 MILLER CLERK 10 ACCOUNTING
7839 KING PRESIDENT 10 ACCOUNTING
7782 CLARK MANAGER 10 ACCOUNTING
7902 FORD ANALYST 20 RESEARCH
7566 JONES MANAGER 20 RESEARCH
7369 SMITH CLERK 20 RESEARCH
7900 JAMES CLERK 30 SALES
7844 TURNER SALESMAN 30 SALES
7698 BLAKE MANAGER 30 SALES
7654 MARTIN SALESMAN 30 SALES
7521 WARD SALESMAN 30 SALES
7499 ALLEN SALESMAN 30 SALES이 OUTER JOIN은 RIGHT OUTER JOIN 이다.
DEPT 테이블이 종 테이블이기 떄문에 40번에 대한 내용이 생략된 것을 알 수 있다.
EX) 테이블을 간단하게 만들어서 확인해보자
SQL> SELECT * FROM X;
S1 X2
---- ----
YA
YB 1
C
SQL> SELECT * FROM Y;
S1 Y2
---- ----
YA
YB 1
SQL> SELECT * FROM X LEFT OUTER JOIN Y USING(S1);
S1 X2 Y2
---- ---- ----
YA
YB 1 1
C
SQL> SELECT * FROM X RIGHT OUTER JOIN Y USING(S1);
S1 X2 Y2
---- ---- ----
YA
YB 1 1# LEFT JOIN의 경우 X 테이블 내용 전체 출력, RIGHT JOIN의 경우 Y테이블 S1전체내용 출력
즉 주테이블의 내용은 전체 출력, 종 테이블의 출력은 맞는 것만 출력한다.
3. SELF JOIN
- 동일 테이블에서의 조인을 말한다.
Q ) ##사원의 관리자는 ##이다.
1) ANSI
SQL> SELECT E.ENAME ||'사원의 관리자는'|| M.ENAME ||'이다' AS "관리자 정보"
2 FROM EMP E LEFT OUTER JOIN EMP M ON(E.MGR = M.EMPNO);
관리자 정보
------------------------------------------------------------------------------------------------
FORD사원의 관리자는JONES이다
JAMES사원의 관리자는BLAKE이다
TURNER사원의 관리자는BLAKE이다
MARTIN사원의 관리자는BLAKE이다
WARD사원의 관리자는BLAKE이다
ALLEN사원의 관리자는BLAKE이다
MILLER사원의 관리자는CLARK이다
CLARK사원의 관리자는KING이다
BLAKE사원의 관리자는KING이다
JONES사원의 관리자는KING이다
SMITH사원의 관리자는FORD이다
KING사원의 관리자는이다
2) ORACLE
SQL> SELECT E.ENAME ||'사원의 관리자는'|| M.ENAME ||'이다' AS "관리자 정보"
2 FROM EMP E, EMP M
3 WHERE E.MGR = M.EMPNO(+);
관리자 정보
------------------------------------------------------------------------------------------------
FORD사원의 관리자는JONES이다
JAMES사원의 관리자는BLAKE이다
TURNER사원의 관리자는BLAKE이다
MARTIN사원의 관리자는BLAKE이다
WARD사원의 관리자는BLAKE이다
ALLEN사원의 관리자는BLAKE이다
MILLER사원의 관리자는CLARK이다
CLARK사원의 관리자는KING이다
BLAKE사원의 관리자는KING이다
JONES사원의 관리자는KING이다
SMITH사원의 관리자는FORD이다
KING사원의 관리자는이다
4. NATURAL JOIN
- 조인 대상이 되는 두 테이블에 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가조인을 해주는 방식
SQL> SELECT E.ENAME, DEPTNO, D.DNAME FROM EMP E NATURAL JOIN DEPT D;
ENAME DEPTNO DNAME
-------------------- -------- ----------------------------
KING 10 ACCOUNTING
CLARK 10 ACCOUNTING
MILLER 10 ACCOUNTING
FORD 20 RESEARCH
SMITH 20 RESEARCH
JONES 20 RESEARCH
JAMES 30 SALES
TURNER 30 SALES
MARTIN 30 SALES
WARD 30 SALES
ALLEN 30 SALES
BLAKE 30 SALES공통 열 DEPTNO를 가지고 있는 두 테이블에 대해 NATURL JOIN은 DEPTNO 열을 기준으로 등가 조인을 시켜준다.
5. CROSS JOIN
- 한 쪽 테이블의 모든 행과 다른 테이블의 모든 행을 JOIN시킨다.
- 즉 이 조인의 결과는 각 행을 곱한것과 같다.
SQL> SELECT E.ENAME, D.DNAME FROM EMP E CROSS JOIN DEPT D;
ENAME DNAME
-------------------- ----------------------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
...
48 rows selected.ENAME = 12, DNAME = 4 -> 48
6. NON-EQUI JOIN
- 이름 그대로 등가 조인 방식 외의 방식을 뜻한다.
# SAL 등급을 보여주는 SALGRADE 테이블
SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
-------- -------- --------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
Q) 급여 범위를 확인해보자.
1) ANSI
SQL> SELECT ENAME,SAL,GRADE FROM EMP JOIN SALGRADE ON(SAL BETWEEN LOSAL AND HISAL);
ENAME SAL GRADE
-------------------- -------- --------
SMITH 800 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
TURNER 1500 3
ALLEN 1600 3
CLARK 2450 4
BLAKE 2850 4
JONES 2975 4
FORD 3000 4
KING 5000 5
2) ORACLE
SQL> SELECT EMPNO,ENAME,JOB,SAL,GRADE
2 FROM SALGRADE, EMP
3 WHERE SAL BETWEEN LOSAL AND HISAL;
EMPNO ENAME JOB SAL GRADE
-------- -------------------- ------------------ -------- --------
7369 SMITH CLERK 800 1
7900 JAMES CLERK 950 1
7521 WARD SALESMAN 1250 2
7654 MARTIN SALESMAN 1250 2
7934 MILLER CLERK 1300 2
7844 TURNER SALESMAN 1500 3
7499 ALLEN SALESMAN 1600 3
7782 CLARK MANAGER 2450 4
7698 BLAKE MANAGER 2850 4
7566 JONES MANAGER 2975 4
7902 FORD ANALYST 3000 4
7839 KING PRESIDENT 5000 5급여 등급은 급여가 일치하는 것이 아닌, 최소급여와 최대급여 사이에 값이 있어야 하기 때문에 열의 일치여부로 테이블을 조인하는 등가조인 방식은 사용할 수 없다. 따라서 BETWEEN A AND B를 이용하여 범위를 지정해줘야한다.
# WHERE 조건절이 없다면 ENAME = 14, SAL = 5 -> 70개의 결과가 출력된다.
'IT > DataBase' 카테고리의 다른 글
| SQL - SUBQUERY (0) | 2021.05.25 |
|---|---|
| SQL - SET (0) | 2021.05.25 |
| SQL - 그룹함수 (0) | 2021.05.21 |
| SQL - 날짜/문자 (0) | 2021.05.18 |
| SQL - DML(SELECT) (0) | 2021.05.17 |