Oracle 문법 정리 - join

◆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개의 행이 리턴된다.