[SQL Plus] JOIN - Equi , Outer, Self, ANSI

JOIN : 하나 이상의 테이블을 가지고 검색하는 방법

 

조인의 종류

    1. inner join (default)  -> 두 테이블의 데이터가 비슷할 때 사용

    2. outer join             -> 한 테이블의 데이터가 더 많을 때 사용

 

1. EQUI JOIN - 동일한 필드 기준 조인

SQL>

select e.ename, d.deptno, d.dname

from emp e, dept d                    <- emp테이블에 e, dept테이블에 d라고 별칭을 줌

where e.deptno=d.deptno;

결과>

ENAME          DEPTNO DNAME
---------- ---------- --------------
SMITH              20 RESEARCH
ALLEN              30 SALES
WARD               30 SALES
JONES              20 RESEARCH
MARTIN             30 SALES
BLAKE              30 SALES
CLARK              10 ACCOUNTING
SCOTT              20 RESEARCH
KING               10 ACCOUNTING
TURNER             30 SALES
ADAMS              20 RESEARCH

ENAME          DEPTNO DNAME
---------- ---------- --------------
JAMES              30 SALES
FORD               20 RESEARCH
MILLER             10 ACCOUNTING

14 개의 행이 선택되었습니다.

 

2. OUTER JOIN - 조인 조건에 만족하지 않는 행도 나타냄

SQL> 

select e.ename, e.job, d.deptno, d.dname

from emp e,dept d
where e.deptno(+)=d.deptno;

 

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
SMITH      CLERK             20 RESEARCH
ALLEN      SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
JONES      MANAGER           20 RESEARCH
MARTIN     SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
CLARK      MANAGER           10 ACCOUNTING
SCOTT      ANALYST           20 RESEARCH
KING       PRESIDENT         10 ACCOUNTING
TURNER     SALESMAN          30 SALES
ADAMS      CLERK             20 RESEARCH

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
JAMES      CLERK             30 SALES
FORD       ANALYST           20 RESEARCH
MILLER     CLERK             10 ACCOUNTING
                             40 OPERATIONS

15 개의 행이 선택되었습니다.

 

3. SELF JOIN - 한 테이블 내에서 조인

SQL>

select w.ename, m.ename 직속상관

from emp w, emp m

where w.mgr=m.empno;

 

결과>

ENAME      직속상관 
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING

ENAME      ENAME
---------- ----------
JONES      KING
SMITH      FORD

13 개의 행이 선택되었습니다.

 

4. ANSI JOIN - INNER JOIN

ANSI SQL -> DBMS 표준 언어, 호환성 좋음

SQL>

select e.ename,e.job,d.deptno,d.dname
from emp e INNER JOIN dept d
ON e.deptno=d.deptno;

결과>

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
SMITH      CLERK             20 RESEARCH
ALLEN      SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
JONES      MANAGER           20 RESEARCH
MARTIN     SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
CLARK      MANAGER           10 ACCOUNTING
SCOTT      ANALYST           20 RESEARCH
KING       PRESIDENT         10 ACCOUNTING
TURNER     SALESMAN          30 SALES
ADAMS      CLERK             20 RESEARCH

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
JAMES      CLERK             30 SALES
FORD       ANALYST           20 RESEARCH
MILLER     CLERK             10 ACCOUNTING

14 개의 행이 선택되었습니다.

 

5. ANSI - INNER JOIN - USING 사용

SQL>

select e.ename,e.job,d.dname
from emp e INNER JOIN dept d
USING(deptno);

 

결과>

ENAME      JOB       DNAME
---------- --------- --------------
SMITH      CLERK     RESEARCH
ALLEN      SALESMAN  SALES
WARD       SALESMAN  SALES
JONES      MANAGER   RESEARCH
MARTIN     SALESMAN  SALES
BLAKE      MANAGER   SALES
CLARK      MANAGER   ACCOUNTING
SCOTT      ANALYST   RESEARCH
KING       PRESIDENT ACCOUNTING
TURNER     SALESMAN  SALES
ADAMS      CLERK     RESEARCH

ENAME      JOB       DNAME
---------- --------- --------------
JAMES      CLERK     SALES
FORD       ANALYST   RESEARCH
MILLER     CLERK     ACCOUNTING

14 개의 행이 선택되었습니다.

 

6. ANSI - OUTER JOIN

[dept LEFT]

SQL>

select e.ename,e.job,d.deptno,d.dname from dept d 

LEFT OUTER JOIN emp e on d.deptno=e.deptno;

결과>

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
SMITH      CLERK             20 RESEARCH
ALLEN      SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
JONES      MANAGER           20 RESEARCH
MARTIN     SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES
CLARK      MANAGER           10 ACCOUNTING
SCOTT      ANALYST           20 RESEARCH
KING       PRESIDENT         10 ACCOUNTING
TURNER     SALESMAN          30 SALES
ADAMS      CLERK             20 RESEARCH

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
JAMES      CLERK             30 SALES
FORD       ANALYST           20 RESEARCH
MILLER     CLERK             10 ACCOUNTING
                             40 OPERATIONS

15 개의 행이 선택되었습니다.

 

[emp RIGHT]

SQL>

select e.ename,e.job,d.deptno,d.dname from dept d 

RIGHT OUTER JOIN emp e on d.deptno=e.deptno;

 

결과>

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
MILLER     CLERK             10 ACCOUNTING
KING       PRESIDENT         10 ACCOUNTING
CLARK      MANAGER           10 ACCOUNTING
FORD       ANALYST           20 RESEARCH
ADAMS      CLERK             20 RESEARCH
SCOTT      ANALYST           20 RESEARCH
JONES      MANAGER           20 RESEARCH
SMITH      CLERK             20 RESEARCH
JAMES      CLERK             30 SALES
TURNER     SALESMAN          30 SALES
BLAKE      MANAGER           30 SALES

ENAME      JOB           DEPTNO DNAME
---------- --------- ---------- --------------
MARTIN     SALESMAN          30 SALES
WARD       SALESMAN          30 SALES
ALLEN      SALESMAN          30 SALES

14 개의 행이 선택되었습니다.