Oracle Join 종류: CROSS, INNER, OUTER, NATURAL,EQUI, SELF join

CROSS JOIN/ Cartesian Products

 - Cartesian product 값을 얻을때 사용 합니다. 

 - 즉 join을 통해 생성가능한 모든 row를 return 합니다. 

SQL>SELECT ename FROM emp CROSS JOIN dept;

SQL>SELECT ename FROM emp, dept;

참고: CROSS가 생략되어 INNER join으로 수행되는 경우(join condition이 없는 경우) 나 CROSS join에서 join condition이 기술된 경우 에러발생합니다. 

SQL>SELECT e.lname, d.name FROM employee e JOIN department d;

ORA-00905: missing keyword

SQL>SELECT e.lname, d.name FROM employee e CROSS JOIN department d ON e.dept_id = d.dept_id;

ORA-00933: SQL command not properly ended

INNER JOIN 

 - 일반 조인시 Table간의 ','를 생략하고 'INNER JOIN'을 추가하고 WHERE절대신 ON절을 사용해야 합니다.

 - INNER는 생략 가능합니다.

SQL>SELECT e.empno, e.ename FROM dept d INNER JOIN emp e ON d.deptno=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno;

EQUI-JOIN/ NON-EQUI-JOIN 

 - Join condition에 equal operation(=)을 사용하는 JOIN 

 - Join condition에 equal operation 외의 다른 operation을 사용하는 JOIN

SQL> SELECT s.name supplier_name, p.name part_name FROM supplier s JOIN part p ON s.supplier_id = p.supplier_id;

SQL> SELECT p.name part_name, c.inv_class inv_class FROM part p JOIN inventory_class c ON p.unit_cost BETWEEN c.low_cost AND c.high_cost;

SELF JOIN

 - 동일 TABLE에 대한 JOIN

SQL> SELECT e.lname employee, m.lname manager FROM employee e JOIN employee m ON e.manager_emp_id = m.emp_id;

OUTER JOIN 

 - Join시 특정 table의 join 조건에 일치하지 않은 row를 추가할때 사용해야 합니다.

 - { LEFT | RIGHT | FULL } [OUTER]

SQL>SELECT e.empno, e.ename FROM dept d LEFT OUTER JOIN emp e ON d.deptno=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno=e.deptno(+);

SQL>SELECT e.empno, e.ename FROM dept d RIGHT OUTER JOIN emp e ON d.deptno=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d , emp e WHERE d.deptno(+)=e.deptno;

SQL>SELECT e.empno, e.ename FROM dept d FULL OUTER JOIN emp e ON d.deptno=e.deptno;

참고: full outer join의 비밀 [Science of DataBase]

PARTITION OUTER JOIN 

 - PARTITION OUTER JOIN을 사용하여 분석 대상이 되는 디멘션의 densification을 수행할 수 있으며, 

 - 파티션이나 테이블 내부에서 OUTER JOIN을 적용할 수 있습니다.

select hiredate,d.dname,  nvl(sum_sal,0) sum_sal

from dept d

left outer join (select deptno,to_char(hiredate,'YYYY') hiredate, sum(sal) sum_sal from emp group by deptno,to_char(hiredate,'YYYY')) e

partition by (e.hiredate)

on d.deptno = e.deptno

order by 1,2

/

HIREDATE     DNAME                                         SUM_SAL

------------ ------------------------------------------ ----------

1980         ACCOUNTING                                          0

1980         OPERATIONS                                          0

1980         RESEARCH                                          800

1980         SALES                                               0

1981         ACCOUNTING                                       7450

1981         OPERATIONS                                          0

1981         RESEARCH                                         5975

1981         SALES                                            9400

1982         ACCOUNTING                                       1300

1982         OPERATIONS                                          0

1982         RESEARCH                                         3000

1982         SALES                                               0

1983         ACCOUNTING                                          0

1983         OPERATIONS                                          0

1983         RESEARCH                                         1100

1983         SALES                                               0

참고:  

Oracle Database 10g의 DSS 성능 DSS Performance in Oracle Database 10g

NATURAL JOIN 

 - Equijoin과 동일하다고 보시면 됩니다.

 - 두 테이블의 동일한 이름을 가지는 칼럼은 모두 조인이 됩니다. (oracle forum : HR유저에서 NATURAL JOIN 결과가 다릅니다 )

 - 동일한 컬럼을 내부적으로 찾게 되므로 테이블 Alias를 주면 오류가 발생 합니다. 

    (ORA-25155: column used in NATURAL join cannot have qualifier)

 - 동일한 컬럼이 두개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어 할 수 있습니다.

 

SQL>SELECT empno, ename, deptno FROM emp NATURAL JOIN dept 

SQL>SELECT e.empno, e.ename, d.deptno FROM emp e, dept d WHERE e.deptno=d.deptno

JOIN ~ USING 

 - NATURAL JOIN의 단점은 동일한 이름을 가지는 칼럼은 모두 조인이 되었는데 USING 문을 사용하면 컬럼을 선택해서 조인을 할 수가 있습니다.

 - USING절 안에 포함되는 컬럼에 Alias를 지정하면 오류가 발생 합니다.

  

SQL>SELECT e.empno, e.ename, deptno FROM emp e JOIN dept d USING(deptno)

ON 구문

 - 조인 조건을 지정 할 수 있습니다. 

 - 모든 논리 연산 및 서브쿼리를 지정할 수 있습니다.

SQL>SELECT e.empno, e.ename, e.sal

       FROM emp e JOIN dept    d  ON (e.deptno=d.deptno)

                           JOIN bonus  b ON (b.ename = e.ename)

       WHERE e.sal IS NOT NULL

 

원문: CROSS JOIN, INNER JOIN, NATURAL JOIN, USING, ON  [oracleclub.com]

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!