[Oracle] 비등가 조인(non equi join), 아우터 조인(outer join)

3. 비등가 조인 .(non equi join)

    : 조인 컬럼의 조건이 = 이 아닌 조인

예시 : 고객테이블과 상품테이블을 이용하여, 고객의 포인트로 받을 수 있는 상품을 조회하기.
   고객의 이름, 포인트, 상품명을 조회하기.
-- 오라클 방식
select g1.gname, g1.point, g2.gname 
from gogak g1,gift g2
where g1.point between g2.g_start and g2.g_end;    

-- ansi 방식
select g1.gname, g1.point, g2.gname 
from gogak g1 join gift g2
on g1.point between g2.g_start and g2.g_end;

문제 : 
 1. 학생의 이름, 점수, 학점 조회하기.
-- 오라클 방식
select s.name,e.total,h.grade 
from student s,exam_01 e,hakjum h 
where s.studno = e.studno 
and e.total between h.min_point and max_point 
order by 2 desc; 

-- ansi 방식
select s.name,e.total,h.grade 
from student s join exam_01 e on s.studno = e.studno 
join hakjum h on e.total between h.min_point and max_point 
order by 2 desc;

문제 : 
 2. 고객이 자기 포인트보다 낮은 포인트 상품 중 한가지를 선택 할 수 있다고 할때, 
   산악용 자전거를 선택할 수 있는 고객명,포인트,상품명을 조회하기.
-- 오라클 방식
select g1.gname,g1.point,g2.gname 
from gogak g1, gift g2
where g1.point >= g2.g_start 
and g2.gname = '산악용자전거'; 

-- ansi 방식
select g1.gname,g1.point,g2.gname 
from gogak g1 join gift g2
on g1.point >= g2.g_start 
where g2.gname = '산악용자전거';

문제 : 
 3. 고객이 자기 포인트보다 낮은 포인트 상품 중 한가지를 선택 할 수 있다고 할때, 
고객이 선택할 수 있는 상품의 갯수 출력하기. 고객명과 선택가능한 상품 갯수 출력하기.
select g1.gname,g1.point,g2.gname 
from gogak g1, gift g2
where g1.point >= g2.g_start 
order by 1
   
select g1.gname 고객명, count(*) 선택가능한상품갯수
from gogak g1, gift g2
where g1.point >= g2.g_start 
group by g1.gname 
order by 1

4. outer join

    : 조인 조건을 만족하지 않아도 한쪽 테이블의 모든 정보를 조회.

 

1) left outer join

       : 조건이 맞지 않아도 왼쪽 테이블의 모든 정보를 조회 (위치를 가지고 만듬.)

-- 오라클 방식 :  join컬럼을 할때 오른쪽에(+) 표시함
-- ansi방식 :  테이블 join시 left outer join 명시

 2) right outer join 
        :  조건이 맞지 않아도 오른쪽 테이블의 모든 정보를 조회

-- 오라클 방식 :  join컬럼을 할때 왼쪽에(+) 표시함
-- ansi방식 :  테이블 join시 right outer join 명시

   3) full outer join
       :  양쪽 테이블의 모든 정보를 조회

-- 오라클 방식 :  left outer join 과 right outer join을 union 함
-- ansi방식 :  테이블 join시 full outer join 명시

예시 : 학생의 이름과 지도교수의 이름을 조회하기. 단 지도교수가 없는 학생도 조회하기.
1)left outer join
-- 오라클 방식
select s.name,p.name from student s, professor p 
where s.profno = p.profno(+);                          

-- ansi방식
select s.name,p.name from student s left outer join professor p 
on s.profno = p.profno;

2)right outer join
-- 오라클 방식
select s.name,p.name from student s, professor p 
where s.profno(+) = p.profno;                         

-- ansi 방식
select s.name,p.name from student s right outer join professor p 
on s.profno = p.profno;

3)full outer join
--오라클 방식 조인 오류 발생 => union 방식 이용
select s.name,p.name from student s, professor p 
where s.profno = p.profno(+)  
union
select s.name,p.name from student s, professor p 
where s.profno(+) = p.profno 

-- ansi 방식
select s.name,p.name from student s full outer join professor p 
on s.profno = p.profno;

문제 : 교수별로 교수이름과 지도학생수를 출력하기. 지도학생이 없는 교수도 조회하도록 하기.
--오라클 방식 조인 
select p.name,count(s.profno+nvl(s.profno,0))지도학생수 
from student s,professor p 
where p.profno =s.profno(+) 
group by p.name 
order by 1

--오라클 방식 조인
select p.name,count(s.name)지도학생수 
from student s,professor p 
where p.profno =s.profno(+) 
group by p.name 
order by 1 

--ansi 방식
select p.name,count(s.profno+nvl(s.profno,0))지도학생수 
from student s right outer join professor p 
on p.profno =s.profno
group by p.name 
order by 1

--ansi 방식
select p.name,count(s.name)지도학생수 
from student s right outer join professor p 
on p.profno =s.profno(+) 
group by p.name 
order by 1

  4)self join 
          :  join되는 테이블의 같은 경우
          : 같은 테이블의 다른 컬럼을 join컬럼으로 설정함.
          : 테이블 이름에 반드시 별명을 설정해야 함.
          : 조회되는 모든 컬럼에 별명을 붙여야 함.

예시 : dept2 테이블에서 부서코드(dcde), 부서명(dname), 상위부서코드(pdept), 
  상위부서명을 출력.
-- 오라클 방식
select d1.dcode 부서코드,d1.dname 부서명, d1.pdept 상위부서코드,d2.dname 상위부서명
from dept2 d1, dept2 d2
where d1.pdept= d2.dcode
order by 1

-- ansi 방식
select d1.dcode 부서코드,d1.dname 부서명, d1.pdept 상위부서코드,d2.dname 상위부서명
from dept2 d1 join dept2 d2
on d1.pdept= d2.dcode
order by 1

문제 : 
 1. dept2 테이블에서 부서코드(dcde), 부서명(dname), 상위부서코드(pdept), 상위부서명을 출력.
   모든 부서가 조회하기.
-- 오라클 방식
select d1.dcode 부서코드,d1.dname 부서명, d1.pdept 상위부서코드,d2.dname 상위부서명
from dept2 d1, dept2 d2  
where d1.pdept= d2.dcode(+)
order by 1  

-- ansi 방식   
select d1.dcode 부서코드,d1.dname 부서명, d1.pdept 상위부서코드,d2.dname 상위부서명
from dept2 d1 left outer join dept2 d2  
on d1.pdept= d2.dcode
order by 1

문제 : 
 2. dept2 테이블에서 상위부서명,하위부서의 갯수 출력. 모든 부서가 조회하기. 
-- 오라클 방식   
select d2.dname 상위부서명,count(d1.dcode) 하위부서수 
from dept2 d1, dept2 d2
where d1.pdept(+) = d2.dcode
group by d2.dname
order by 2 desc

-- ansi 방식 
select d2.dname 상위부서명,count(d1.dcode) 하위부서수 
from dept2 d1 right outer join dept2 d2
on d1.pdept = d2.dcode
group by d2.dname
order by 2 desc