[오라클조인]내부조인,외부조인(inner join, outer join)

[오라클조인]내부조인,외부조인(inner join, outer join)

내부조인(EQUI JOIN, NON EUQI JOIN, NATURAL JOIN, SELF JOIN)

-. EQUI JOIN
select empno,  dname from emp, dept where emp.deptno = dept.deptno
select empno,  dname from emp join dept on emp.deptno = dept.deptno
select empno,  dname from emp inner join dept on emp.deptno = dept.deptno
select empno,  dname from emp join dept using(deptno)

- NON EQUI JOIN
select empno, ename, grade from emp e, salgrade s where e.sal between s.losal and s.hisal

- NATURAL JOIN
select empno,  dname from emp natural join dept  --자연조인

- SELF JOIN
select "사원".empno, "사원".ename, "관리자".ename from emp "사원", emp "관리자"
where "사원".mgr = "관리자".empno


외부조인(LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN)

- left outer join
select empno,  ename, nvl(dname,'부서없음') from emp, dept where emp.deptno = dept.deptno(+)
select empno,  ename, nvl(dname,'부서없음') from emp left outer join dept on emp.deptno = dept.deptno

- right outer join
select nvl(empno,9999),  nvl(ename,'사원없음') , nvl(dname,'부서없음') from emp, dept
where emp.deptno(+) = dept.deptno
select nvl(empno,9999),  nvl(ename,'사원없음') , nvl(dname,'부서없음') from emp right outer join dept
on emp.deptno = dept.deptno

- full outer join, 오라클은 양쪽 더하기(+)는 허용하지 않는다.
select empno,  ename, nvl(dname,'부서없음') from emp full outer join dept
on emp.deptno = dept.deptno
[출처] 오라클자바커뮤니티 - http://ojc.asia/bbs/board.php?bo_table=LecSQLnPlSql&wr_id=427