Oracle DB ( join (inner join , outer join )

inner join

--JOIN 여러테이블을 하나의 테이블 처럼 사용
--1) 내부 JOIN ( inner join)  : 여러 개의 테이블에서 공통된 부분만 추출 
--    등가조인 : 두개의 열이 일치할 때 값 추출
--    비등가조인 : 범위에 해당할 때 값 추출

--2) 외부 JOIN (outer join) :
--    left outer join
--    right outer join 
--    full outer join 

--cross join  : 나올수 있는 모든 조합을 출력 ( 잘 사용 안함)
select *
from emp , dept
order by empno;

-- inner join 
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e, dept d
where  e.deptno = d.deptno;

--sql-99 표준
--join ~ on
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e join dept d on e.deptno =  d.deptno;

-- 급여가 2500 이하 이고 , 사원번호가 9999 이하인 사원 정보 조회
select e.empno , e.ename , d.deptno , d.dname , d.loc
from emp e, dept d
where e.deptno = d.deptno and sal < 2500 and empno < 9999;

-- emp , salgrade 조인
-- 일치하는 필드가 없기때문에 losal , hisal 범위에 들어가는 형태로 조인

select *
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

 

 

outer join 

-- outer join 
-- left outer join 오른쪽에 (+)
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 , emp e2
where e1.mgr = e2.empno(+);

--join ~on
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 left outer join  emp e2 on  e1.mgr = e2.empno;

-- right outrer join 왼쪽에(+)
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 , emp e2
where e1.mgr(+) = e2.empno;

--join ~ on
select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 right outer join  emp e2 on  e1.mgr = e2.empno;

select  e1.ename , e1.empno  , e2.empno as mgrempno , e2.ename as  mgremname
from emp e1 full outer join  emp e2 on  e1.mgr = e2.empno;