Oracle Join 유형 정리

먼저 예제로 사용할 테이블을 확인해보자.

SQL> SELECT *
FROM DEPT01;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH

SQL> SELECT *
FROM DEPT02;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        30 SALES

CROSS JOIN

SQL> SELECT *
FROM DEPT01, DEPT02;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
        10 ACCOUNTING             30 SALES
        20 RESEARCH               10 ACCOUNTING
        20 RESEARCH               30 SALES

SQL> SELECT *
FROM DEPT01 CROSS JOIN DEPT02;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
        10 ACCOUNTING             30 SALES
        20 RESEARCH               10 ACCOUNTING
        20 RESEARCH               30 SALES

EQUI JOIN

SQL> SELECT *
FROM DEPT01, DEPT02
WHERE DEPT01.DEPTNO = DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING

SQL> SELECT *
FROM DEPT01 INNER JOIN DEPT02 ON DEPT01.DEPTNO = DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING

NATURAL JOIN

SQL> SELECT *
FROM DEPT01 NATURAL JOIN DEPT02;

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING

SQL> SELECT *
FROM DEPT01 INNER JOIN DEPT02 USING ( DEPTNO, DNAME );

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING

OUTER JOIN

SQL> SELECT *
FROM DEPT01, DEPT02
WHERE DEPT01.DEPTNO=DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING

SQL> SELECT *
FROM DEPT01, DEPT02
WHERE DEPT01.DEPTNO(+)=DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
                                  30 SALES

SQL> SELECT *
FROM DEPT01, DEPT02
WHERE DEPT01.DEPTNO=DEPT02.DEPTNO(+);

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
        20 RESEARCH

ANSI OUTER JOIN

SQL> SELECT *
FROM DEPT01 LEFT OUTER JOIN DEPT02 ON DEPT01.DEPTNO=DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
        20 RESEARCH

SQL> SELECT *
FROM DEPT01 RIGHT OUTER JOIN DEPT02 ON DEPT01.DEPTNO=DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
                                  30 SALES

SQL> SELECT *
FROM DEPT01 FULL OUTER JOIN DEPT02 ON DEPT01.DEPTNO=DEPT02.DEPTNO;

    DEPTNO DNAME              DEPTNO DNAME
---------- -------------- ---------- --------------
        10 ACCOUNTING             10 ACCOUNTING
                                  30 SALES
        20 RESEARCH

SQL> SELECT *
FROM DEPT01 FULL OUTER JOIN DEPT02 USING(DEPTNO);

    DEPTNO DNAME          DNAME
---------- -------------- --------------
        10 ACCOUNTING     ACCOUNTING
        30                SALES
        20 RESEARCH