toggle menu

Oracle Join 유형 정리

2012. 7. 10. 17:40 Database/Oracle

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

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








Database/Oracle 관련 포스팅 더보기