조인종류 & SQL-99 표준문법

1. 등가 조인(equi join)

- 테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식

- 등가 조인은 내부 조인(inner join) 또는 단순 조인(simple join)으로 부르기도 합니다.

- 즉, 특정 열 값이 일치한 출력 결과를 사용하는 방식

- emp 테이블

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7839	KING	PRESIDENT	-	17-Nov-81	5000	-	10
7698	BLAKE	MANAGER	7839	01-May-81	2850	-	30
7782	CLARK	MANAGER	7839	09-Jun-81	2450	-	10
7566	JONES	MANAGER	7839	02-Apr-81	2975	-	20
7788	SCOTT	ANALYST	7566	19-Apr-87	3000	-	20
7902	FORD	ANALYST	7566	03-Dec-81	3000	-	20
7369	SMITH	CLERK	7902	17-Dec-80	800	-	20
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30
7876	ADAMS	CLERK	7788	23-May-87	1100	-	20
7900	JAMES	CLERK	7698	03-Dec-81	950	-	30
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10

- dept 테이블

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

1. 열 이름에 각각의 테이블 이름도 함께 명시할 때

결과)

EMPNO	ENAME	DEPTNO	DNAME	LOC
7782	CLARK	10	ACCOUNTING	NEW YORK
7934	MILLER	10	ACCOUNTING	NEW YORK
7839	KING	10	ACCOUNTING	NEW YORK
7902	FORD	20	RESEARCH	DALLAS
7788	SCOTT	20	RESEARCH	DALLAS
7566	JONES	20	RESEARCH	DALLAS
7369	SMITH	20	RESEARCH	DALLAS
7876	ADAMS	20	RESEARCH	DALLAS
7521	WARD	30	SALES	CHICAGO
7654	MARTIN	30	SALES	CHICAGO
7844	TURNER	30	SALES	CHICAGO
7900	JAMES	30	SALES	CHICAGO
7499	ALLEN	30	SALES	CHICAGO
7698	BLAKE	30	SALES	CHICAGO

2. 비등가 조인(non-equi join)

- 등가 조인과 달리 열의 일치 여부가 불확실할 때 사용하는 방법

- SALDRAGE 테이블

GRADE	LOSAL	HISAL
1	0	1000
2	1001	2000
3	2001	3000
4	3001	4000
5	4001	5000
6	5001	6000
7	6001	7000
8	7001	8000
9	8001	9000
10	9001	9999

결과)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	GRADE	LOSAL	HISAL
7369	SMITH	CLERK	7902	17-Dec-80	800	-	20	1	0	1000
7900	JAMES	CLERK	7698	03-Dec-81	950	-	30	1	0	1000
7876	ADAMS	CLERK	7788	23-May-87	1100	-	20	2	1001	2000
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	2	1001	2000
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	2	1001	2000
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10	2	1001	2000
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	2	1001	2000
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	2	1001	2000
7782	CLARK	MANAGER	7839	09-Jun-81	2450	-	10	3	2001	3000
7698	BLAKE	MANAGER	7839	01-May-81	2850	-	30	3	2001	3000
7566	JONES	MANAGER	7839	02-Apr-81	2975	-	20	3	2001	3000
7902	FORD	ANALYST	7566	03-Dec-81	3000	-	20	3	2001	3000
7788	SCOTT	ANALYST	7566	19-Apr-87	3000	-	20	3	2001	3000
7839	KING	PRESIDENT	-	17-Nov-81	5000	-	10	5	4001	5000

3. 자체 조인(self join)

- 하나의 테이블을 여러 개의 테이블 처럼 활용하여 조인하는 방식

- 테이블 안에 EMPNO와 MGR코드를 연결시키는 예제

※ FROM에 같은 테이블이름을 똑같이 사용한다.

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7566	JONES	7839	7839	KING
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7654	MARTIN	7698	7698	BLAKE
7844	TURNER	7698	7698	BLAKE
7900	JAMES	7698	7698	BLAKE
7934	MILLER	7782	7782	CLARK
7788	SCOTT	7566	7566	JONES
7902	FORD	7566	7566	JONES
7876	ADAMS	7788	7788	SCOTT
7369	SMITH	7902	7902	FORD

4. 외부 조인(outer join)

- 앞서 했던 자체 조인에서 문제점 : KING은 MGR코드가 없으므로 결과에서 제외되었음, NULL이 되더라도 강제로 출력하는 방식

- 왼쪽 외부 조인(Left Outer Join)

결과)

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7566	JONES	7839	7839	KING
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7654	MARTIN	7698	7698	BLAKE
7844	TURNER	7698	7698	BLAKE
7900	JAMES	7698	7698	BLAKE
7934	MILLER	7782	7782	CLARK
7788	SCOTT	7566	7566	JONES
7902	FORD	7566	7566	JONES
7876	ADAMS	7788	7788	SCOTT
7369	SMITH	7902	7902	FORD
7839	KING	-	-	-

- 오른쪽 외부 조인(Right Outer Join)

결과)

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7566	JONES	7839	7839	KING
7788	SCOTT	7566	7566	JONES
7902	FORD	7566	7566	JONES
7369	SMITH	7902	7902	FORD
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7654	MARTIN	7698	7698	BLAKE
7844	TURNER	7698	7698	BLAKE
7876	ADAMS	7788	7788	SCOTT
7900	JAMES	7698	7698	BLAKE
7934	MILLER	7782	7782	CLARK
-	-	-	7876	ADAMS
-	-	-	7521	WARD
-	-	-	7499	ALLEN
-	-	-	7900	JAMES
-	-	-	7369	SMITH
-	-	-	7934	MILLER
-	-	-	7654	MARTIN
-	-	-	7844	TURNER

---

- SQL-99 방식의 문법은 오라클 9i 버전부터 가능

- 다른 DBMS제품에서도 사용가능

1. NATURAL JOIN

- 등가조인과 동일

결과)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	DNAME	LOC
7782	CLARK	MANAGER	7839	09-Jun-81	2450	-	10	ACCOUNTING	NEW YORK
7839	KING	PRESIDENT	-	#######	5000	-	10	ACCOUNTING	NEW YORK
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10	ACCOUNTING	NEW YORK
7369	SMITH	CLERK	7902	#######	800	-	20	RESEARCH	DALLAS
7566	JONES	MANAGER	7839	#######	2975	-	20	RESEARCH	DALLAS
7788	SCOTT	ANALYST	7566	#######	3000	-	20	RESEARCH	DALLAS
7876	ADAMS	CLERK	7788	#######	1100	-	20	RESEARCH	DALLAS
7902	FORD	ANALYST	7566	#######	3000	-	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	7698	#######	1600	300	30	SALES	CHICAGO
7521	WARD	SALESMAN	7698	#######	1250	500	30	SALES	CHICAGO
7654	MARTIN	SALESMAN	7698	#######	1250	1400	30	SALES	CHICAGO
7698	BLAKE	MANAGER	7839	#######	2850	-	30	SALES	CHICAGO
7844	TURNER	SALESMAN	7698	#######	1500	0	30	SALES	CHICAGO
7900	JAMES	CLERK	7698	#######	950	-	30	SALES	CHICAGO

2. JOIN ~ USING

- 기존 등가 조인을 대신하는 조인 방식

- NATURAL JOIN이 자동으로 조인 기준열을 지정하는 것과 달리 USING 키워드에 조인 기준으로 사용할 열을 명시

결과)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	DNAME	LOC
7839	KING	PRESIDENT	-	17-Nov-81	5000	-	10	ACCOUNTING	NEW YORK
7788	SCOTT	ANALYST	7566	19-Apr-87	3000	-	20	RESEARCH	DALLAS
7902	FORD	ANALYST	7566	03-Dec-81	3000	-	20	RESEARCH	DALLAS

3. JOIN ~ ON

- 가장 범용성 있음

- 기존 WHERE절에 있는 조인 조건식을 ON 키워드 옆에 작성

결과)

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO	DNAME	LOC
7782	CLARK	MANAGER	7839	09-Jun-81	2450	-	10	ACCOUNTING	NEW YORK
7934	MILLER	CLERK	7782	23-Jan-82	1300	-	10	ACCOUNTING	NEW YORK
7369	SMITH	CLERK	7902	17-Dec-80	800	-	20	RESEARCH	DALLAS
7566	JONES	MANAGER	7839	02-Apr-81	2975	-	20	RESEARCH	DALLAS
7788	SCOTT	ANALYST	7566	19-Apr-87	3000	-	20	RESEARCH	DALLAS
7876	ADAMS	CLERK	7788	23-May-87	1100	-	20	RESEARCH	DALLAS
7902	FORD	ANALYST	7566	03-Dec-81	3000	-	20	RESEARCH	DALLAS
7499	ALLEN	SALESMAN	7698	20-Feb-81	1600	300	30	SALES	CHICAGO
7521	WARD	SALESMAN	7698	22-Feb-81	1250	500	30	SALES	CHICAGO
7654	MARTIN	SALESMAN	7698	28-Sep-81	1250	1400	30	SALES	CHICAGO
7698	BLAKE	MANAGER	7839	01-May-81	2850	-	30	SALES	CHICAGO
7844	TURNER	SALESMAN	7698	08-Sep-81	1500	0	30	SALES	CHICAGO
7900	JAMES	CLERK	7698	03-Dec-81	950	-	30	SALES	CHICAGO

4. OUTER JOIN

- WHERE절이 아닌 FROM절에서 외부 조인을 선언

- 왼쪽 외부 조인을 SQL-99로 작성

결과)

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7369	SMITH	7902	7902	FORD
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7566	JONES	7839	7839	KING
7654	MARTIN	7698	7698	BLAKE
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7788	SCOTT	7566	7566	JONES
7839	KING	-	-	-
7844	TURNER	7698	7698	BLAKE
7876	ADAMS	7788	7788	SCOTT
7900	JAMES	7698	7698	BLAKE
7902	FORD	7566	7566	JONES
7934	MILLER	7782	7782	CLARK

- 오른쪽 외부 조인

결과)

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7369	SMITH	7902	7902	FORD
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7566	JONES	7839	7839	KING
7654	MARTIN	7698	7698	BLAKE
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7788	SCOTT	7566	7566	JONES
7844	TURNER	7698	7698	BLAKE
7876	ADAMS	7788	7788	SCOTT
7900	JAMES	7698	7698	BLAKE
7902	FORD	7566	7566	JONES
7934	MILLER	7782	7782	CLARK
-	-	-	7900	JAMES
-	-	-	7369	SMITH
-	-	-	7934	MILLER
-	-	-	7654	MARTIN
-	-	-	7521	WARD
-	-	-	7876	ADAMS
-	-	-	7844	TURNER
-	-	-	7499	ALLEN

- 전체 외부 조인

결과)

EMPNO	ENAME	MGR	MGR_EMPNO	MGR_ENAME
7369	SMITH	7902	7902	FORD
7499	ALLEN	7698	7698	BLAKE
7521	WARD	7698	7698	BLAKE
7566	JONES	7839	7839	KING
7654	MARTIN	7698	7698	BLAKE
7698	BLAKE	7839	7839	KING
7782	CLARK	7839	7839	KING
7788	SCOTT	7566	7566	JONES
7839	KING	-	-	-
7844	TURNER	7698	7698	BLAKE
7876	ADAMS	7788	7788	SCOTT
7900	JAMES	7698	7698	BLAKE
7902	FORD	7566	7566	JONES
7934	MILLER	7782	7782	CLARK
-	-	-	7499	ALLEN
-	-	-	7521	WARD
-	-	-	7654	MARTIN
-	-	-	7876	ADAMS
-	-	-	7900	JAMES
-	-	-	7934	MILLER
-	-	-	7369	SMITH
-	-	-	7844	TURNER