[Oracle] ANSI 조인

ANSI(American Nastional Standards Institude) - SQL JOIN

natural join (equi join 형식)

: 조인조건 술어를 자동으로 만들어 준다.

-양 쪽 테이블에 동일한 이름의 모든 컬럼을 기준으로 조인조건술어를 만들어준다.

select 별칭1.컬럼명1, 별칭2.컬럼명2
from 테이블1 별칭1 natural join 테이블2 별칭2;

주의해야할 점

컬럼명만 같고 데이터가 다른 경우엔 틀렸으니 확인해야함

 

join using (equi join 형식)

: 조인조건의 기준 컬럼을 지정한다.

select 컬럼명1, 컬럼명2
from 테이블명1 join 테이블명2
using(기준 컬럼)
where 기준컬럼 조건;

주의해야할 점

using절에 사용된 기준컬럼은 어느 테이블이라고 지정하면 안됨(where절에서도 지정X)

-3개 테이블 조인할 때

select e.employee_id, d.department_name, l.city
from employees e join departments d
using(department_id)
join locations l
using(location_id)
where department_id=30;

 

join on(equi join)

: on절을 이용해서 조인조건 술어를 직접 만들어서 사용. 가장 많이 사용!

select e.employee_id, d.department_name, l.city
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id=l.location_id
where e.department_id in (30,50);

• self join + join on

select w.last_name, m.last_name
from employees w join employees m
on w.manager_id = m.employee_id;

• outer join + join on = left/right/full outer join

select e.employee_id, e.salary, d.department_name
from employees e left outer join departments d
on e.department_id = d.department_id;
=
select e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id (+) ;

 

ORACLE 전용의 outer join 사용할 때 양쪽에 (+)를 사용할 수 없다.

① 해결방법 :

select e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id (+) = d.department_id
union --합집합 연산자(중복제거)
select e.employee_id, e.salary, d.department_name
from employees e, departments d
where e.department_id = d.department_id(+);

② 해결방법 : ansi 표준의 full outer join 사용

 

cross join

: 의도적인 cartesian product 발생

---

[문제46] 사원들의 사번, 급여, 급여등급, 부서이름을 출력하세요. 부서배치를 받지 않는 사원은 제외시켜주세요. 단 join on절을 이용해주세요.

select e.employee_id, e.salary, j.grade_level, d.department_name
from employees e join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
join departments d
on e.department_id = d.department_id;

[문제47] 2006년도에 입사한 사원들의 부서이름별 급여의 총액, 평균을 출력해주세요.

1) 오라클 전용

select d.department_name, sum(e.salary), avg(e.salary)
from employees e, departments d
where e.department_id = d.department_id
and e.hire_date >= to_date('2006/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2007/01/01','yyyy-mm-dd')
group by d.department_name;

2) ANSI 표준

select d.department_name, sum(e.salary), avg(e.salary)
from employees e join departments d
on e.department_id = d.department_id
where e.hire_date >= to_date('2006/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2007/01/01','yyyy-mm-dd')
group by d.department_name;

[문제48] 2006년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요.

1) 오라클 전용

select l.city , sum(e.salary), avg(e.salary)
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.hire_date >= to_date('2006/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2007/01/01','yyyy-mm-dd')
group by l.city;

2) ANSI 표준

select l.city , sum(e.salary), avg(e.salary)
from employees e join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id
where e.hire_date >= to_date('2006/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2007/01/01','yyyy-mm-dd')
group by l.city;

[문제49] 2007년도에 입사한 사원들의 도시이름별 급여의 총액, 평균을 출력해주세요. 단 부서 배치를 받지 않은 사원들의 정보도 출력해주세요.

1) 오라클 전용

select l.city , sum(e.salary), avg(e.salary)
from employees e, departments d, locations l
where e.department_id = d.department_id(+)
and d.location_id = l.location_id(+)
and e.hire_date >= to_date('2007/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2008/01/01','yyyy-mm-dd')
group by l.city;

2) ANSI 표준

select l.city , sum(e.salary), avg(e.salary)
from employees e left outer join departments d
on e.department_id = d.department_id
left outer join locations l
on d.location_id = l.location_id
where e.hire_date >= to_date('2007/01/01','yyyy-mm-dd')
and e.hire_date < to_date('2008/01/01','yyyy-mm-dd')
group by l.city;

[문제50] 사원들의 last_name,salary,grade_level, department_name을 출력하는데 last_name에 a문자가 2개 이상 포함되어 있는 사원들을 출력하세요.

1) 오라클 전용

select  e.last_name, e.salary, j.grade_level, d.department_name
from employees e, departments d, job_grades j
where e.department_id = d.department_id
and e.salary between j.lowest_sal and j.highest_sal
and instr(e.last_name,'a',1,2)>=2;

2) ANSI 표준

select  e.last_name, e.salary, j.grade_level, d.department_name
from employees e join departments d
on e.department_id = d.department_id
join job_grades j
on e.salary between j.lowest_sal and j.highest_sal
where instr(e.last_name,'a',1,2)>=2;