[SQL] Oracle조인,ANSI조인(cross,equi,non-equi,outer,self)

-- 오라클 조인
--크로스조인,카티션 프로덕트
select last_name,department_name 
from employees,departments
order by last_name;

-- equi join
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id
order by last_name;

--non equi join
select e.last_name, e.salary,j.grade_level
from employees e, job_grades j
where j.lowest_sal<= e.salary and e.salary <= j.highest_sal;
--where e.salary between j.lowest_sal and j.highest_sal;

--outer join
--left outer join
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e,departments d
where e.department_id=d.department_id(+)
order by last_name;

--right outer join
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e,departments d
where e.department_id(+)=d.department_id(+)
order by last_name;

--self join
select e.last_name, m.last_name
from employees e, employees m
where e.manager_id = m.employee_id(+);

--ANSI Join
--,대신 join where대신 on
--크로스조인,카티션 프로덕트
select last_name,department_name 
from employees cross join departments
order by last_name;

--equi join
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e inner join departments d
on e.department_id=d.department_id
order by last_name;

--non equi join
select e.last_name, e.salary,j.grade_level
from employees e inner join job_grades j
on j.lowest_sal<= e.salary and e.salary <= j.highest_sal;
--where e.salary between j.lowest_sal and j.highest_sal;

--outer join
--left outer join (outer 생략가능)
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e left join departments d
on e.department_id=d.department_id
order by last_name;

--right outer join (outer 생략가능)
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e right outer join departments d
on e.department_id=d.department_id
order by last_name;

--full outer join (outer 생략가능)
select e.last_name, e.department_id,d.department_id,d.department_name
from employees e full join departments d
on e.department_id=d.department_id
order by last_name;

--self join
select e.last_name, m.last_name
from employees e left join employees m
on e.manager_id = m.employee_id;
 
--natural join
--scott계정--
-------------
conn scott/TIGER;

select ename,dname
from emp natural join dept;