[IT실무고급교육★탑크리에듀][오라클조인]내부조인,외부조인(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