◆Join

 데이터베이스 내의 여러 테이블 레코드를 조합하여 하나의 열로 표현한 .(위키백과)





Oracle Join

Ansi Join


1.     equal join

2.     outer join

3.     non equal join

4.     sef join

5.     threeways join


1.     inner join ~ on = join ~ on

2.     left outer join ~ on = left join ~ on

3.     right outer join ~on = right join ~ on

4.     full outer join ~ on = full join ~ on

5.     natural join

6.     join ~ using

 

Oracle Join

 

1) equal join

select ename,dname

from emp e, dept d

where e.deptno = d.deptno;

관계 (=)연산을 사용할 수 없을 떄 즉, join조건에 포함되지 못해(deptno = null) 나오지 못한 데이터가 있을 때

 

2)outer join

 

a) left outer join

select ename,nvl(dname,'대기발령')

from emp e, dept d

where e.deptno = d.deptno(+);


b) right outer join

select dname as deptName, ename

from dept d,emp e

where D.DEPTNO = E.DEPTNO(+)

order by deptName;


c) full outer join

오라클에서는 지원 x

 

3)non equal join

select ename

       ,sal

       ,s.grade

from emp e, salgrade s

where e.sal between s.losal and s.hisal;

 

4)self join

select e1.ename as mentee

      ,e2.ename as mento

from emp e1, emp e2

where e1.mgr = e2.empno(+)

order by mento;

 

 

Ansi Join

 

a)    inner join ~ on

select last_name

      ,department_name

from employees e join departments d

on e.department_id = d.department_id and e.last_name = initcap('&NAME')

order by department_name, last_name;

 

b)    left outer join ~ on, right outer join ~ on 

select last_name

      ,department_name

from employees e left outer join departments d

on e.department_id = d.department_id

order by department_name, last_name;

 

c)     full outer join ~ on

select department_name

      ,last_name as last_name

from employees e full outer join departments d

on e.department_id = d.department_id

order by department_name desc, last_name;

 

 

d)    natural join

select last_name,department_name

from employees natural join departments;

 

l  natural join 은 사용하지 않습니다.

테이블과 테이블의 공통된 컬럼을 알아서 합쳐주는 것 인데, 잘못된 결과가 나올 위험이 크다.

e)    join ~ using 

select last_name, department_name

from employees join departments

using (department_id);

 


p.s)마지막으로 카르테시안 조인(=크로스 조인)이 있습니다.


크로스 조인 = 모든 경우의 수를 나열하는 것.

select last_name,department_name

from employees cross join departments;

사원 수 (20) * 부서 수 (8) = 160개의 행이 리턴된다.

+ Recent posts