16.조인(2)(natural,equi,outer,self)

[ 조인 ]
 분리된 두 테이블의 데이터를 동시에 한 쿼리로 출력하거나, 다른 테이블 데이터를 참조하는 조건이 필요할 경우 사용
 oracle 표준과 ansi 표준 존재
 oracle 표준은 조인에 필요한 테이블을 from절에 컴마로 나열
        조인조건은 일반조건과 마찬가지로 where절에 나열
 ansi 표준은 조인에 필요한 테이블과 조인 형태를 from절 기술(컴마 전달 불가)
        조인조건은 일반조건과 분리되어 on/using절에 나열

** 조인 시 주의사항
 - 테이블 별칭 정의는 from절에 as 없이 사용
 - 테이블 별칭이 정의된 경우 컬럼 앞의 컬럼 구분자로 테이블명 전달 불가
select emp.deptno
  from emp e, dept d;   -- 에러
  
select e.deptno
  from emp e, dept d;   -- 정상


** 조인 종류
1. 데이터 생략 여부에 따라
1) inner join : 조인 조건에 성립하는 데이터만 출력(성립하지 않는 행은 생략)
2) outer join : 조인 조건에 성립하지 않을 경우도 출력을 원할 때 사용
   2-1) left outer join
   2-2) right outer join
   2-3) full outer join

2. cross join
 - 모든 발생 가능한 조합을 출력
 - oracle에서 조인 조건 생략 시 발생

3. natural join
 - 조인 테이블의 같은 이름의 컬럼끼리 조인되는 형태
 - oracle 문법에는 존재하지 X

4. 조인 조건의 형태에 따라
1) equi join : 조인 조건이 등호(=)일 경우
2) non equi join : 조인 조건이 동등비교가 아닌 경우



[ cross join ]

oracle 표준)
select *
  from emp, dept;   -- 56건 출력

ansi 표준)
select *
  from emp cross join dept;  


1. natural join 
 조인 조건 명시 불가 : on절, using절 전달 불가
select *
  from emp natural join dept;      -- emp.deptno = dept.deptno 조인 조건 자동 발생
  
select *
  from emp natural join dept
    on emp.deptno = dept.deptno;   -- 에러

select *
  from emp natural join dept
 using (deptno);                   -- 에러

 

 

** natural join 잘못된 예
 student, professor 테이블 조인하여 각 학생의 이름, 지도교수 이름 출력
select s.name as 학생이름, p.name as 지도교수이름
  from student s join professor p
    on s.profno = p.profno;

select *
  from student s natural join professor p;  -- 공집합 출력
  -> name, profno 컬럼의 값이 각각 같은 행을 출력

 

 

2. equi join 
 ansi 표준)
 - on절을 사용하여 조인 조건 전달 가능
 - using절을 사용하여 같은 컬럼명에 대한 equi join 가능
 - using절 사용 시 괄호 생략 불가
 - using절 사용 시 컬럼명 앞에 컬럼 구분자 전달 불가

select *
  from emp join dept
    on emp.deptno = dept.deptno;   -- 정상
    
select *
  from emp join dept               -- 정상
 using (deptno);

select *
  from emp join dept               -- 에러
 using deptno;

select *
  from emp join dept               -- 에러
 using (emp.deptno);
 
select e.ename, e.hiredate, d.dname, e.deptno
  from emp e join dept d                       -- 에러
 using (deptno); 
 
 

3. 세 테이블 이상 조인 
- N개 테이블 조인 시 최소 (N-1)개의 조인 조건 필수
oracle 표준)
select *
  from a, b, c, d
 where a.col1 = b.col1
   and c.col2 = a.col2
   and c.col3 = d.col3;
  
ansi 표준)
select *
  from a join b 
    on a.col1 = b.col1
  join c
    on c.col2 = a.col2
  join d
    on c.col3 = d.col3; 
 

예제) student, department, exam_01, hakjum 테이블을 사용하여
    각 학생의 이름, 학년, 제1전공명, 성적 출력

oracle 표준)
select s.name, s.grade, d.dname, e.total, h.grade
  from student s, department d, exam_01 e, hakjum h
 where s.deptno1 = d.deptno
   and s.studno = e.studno
   and e.total between h.min_point and h.max_point;

 

ansi 표준)
select s.name, s.grade, d.dname, e.total, h.grade
  from student s join department d
    on s.deptno1 = d.deptno
  join exam_01 e
    on s.studno = e.studno
  join hakjum h  
    on e.total between h.min_point and h.max_point;

 

 

 

4. outer join 
- inner join의 반대
- 조인 조건에 성립하지 않아 생략되는 데이터도 출력을 위해 사용
- 생략된 데이터의 방향(기준이 되는)에 따라 left/right/full outer join으로 구분
- oracle 표준에서는 기준이 되는 테이블 반대쪽 테이블 조건컬럼에 모두 (+)를 붙임

 

예제) student, professor 테이블을 사용하여 각 학생의 이름, 지도교수 이름 출력
단, 지도교수가 없는 학생정보도 출력
select s.name, p.name
  from student s, professor p
 where s.profno = p.profno;     -- 15건 출력(지도교수가 없는 학생 5건 생략)

oracle 표준)
select s.name, p.name
  from student s, professor p
 where s.profno = p.profno(+);  -- 20건 출력(지도교수가 없는 학생도 출력)

ansi 표준)  아래 두 결과는 같고 ansi  표준이 더 oracle 표준보다 outer join 에서 활용도가 높고 가독성이 좋다. 
1) select s.name, p.name
  from student s left outer join professor p
    on s.profno = p.profno; 

 

 

2) select s.name, p.name
  from professor p right outer join student s 
    on s.profno = p.profno; 

 

 

5. full outer join 
- 양쪽 테이블 기준 outer join 결과의 합집합
- left outer join + right outer join(union)
- oracle full outer join 지원 X
- ansi 표준으로는 지원

select s.name, p.name
  from student s, professor p
 where s.profno(+) = p.profno(+);   -- 불가(outer-join된 테이블은 1개만 지정할 수 있습니다)

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


oracle)
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; 



[ 연습문제 ]
1. student, department 테이블을 사용하여 각 학생의 이름, 학년, 
   제1전공이름, 제2전공이름을 출력
   (단, 제2전공이 없는 학생도 출력)

sol1)

select s.name, s.grade, 
       d1.dname as 제1전공명, 
       d2.dname as 제2전공명 
  from student s, department d1, department d2
 where s.deptno1 = d1.deptno
   and s.deptno2 = d2.deptno(+);

sol2)
select s.name, s.grade, 
       d1.dname as 제1전공명, 
       d2.dname as 제2전공명 
  from student s join department d1
    on s.deptno1 = d1.deptno
  left join department d2
    on s.deptno2 = d2.deptno;
   

 

 

2. gogak, gift 테이블을 사용하여
   각 고객이 가진 포인트 기준 받을 수 있는 상품의 수를 출력
   단, 고객이름, 고객번호와 함께 출력

sol)

select g1.gno, g1.gname, g1.jumin, g1.point, 
       count(g2.GNO) as 상품수
  from gogak g1, gift g2
 where g1.POINT >= g2.G_START
 group by g1.gno, g1.gname, g1.jumin, g1.point
 order by g1.gname;

 

 

3. student, department, professor 테이블을 사용하여
각 학생의 이름, 학년, 소속학과명(제1전공번호로), 지도교수이름, 지도교수의 소속학과명 출력
(단, 지도교수가 없는 학생도 출력)

d1 - s - p(+) - d2(+)

sol1) oracle 표준
select s.name as 학생명, s.grade, d1.dname as 전공명, p.name as 교수명, d2.dname as 교수학과명
  from student s, department d1, department d2, professor p
 where s.deptno1 = d1.deptno
   and s.profno = p.profno(+)
   and p.deptno = d2.deptno(+);

 

 

 

sol2) ansi 표준
select s.name as 학생명, s.grade, d1.dname as 전공명, p.name as 교수명, d2.dname as 교수학과명
  from student s join department d1
    on s.deptno1 = d1.deptno
  left outer join professor p
    on s.profno = p.profno
  left outer join department d2
    on p.deptno = d2.deptno;

 

 

 

6. 셀프 조인 
- 하나의 테이블을 스스로 조인하는 형태

예제) emp를 사용하여 각 직원의 이름, 급여와 함께 상위관리자의 이름, 급여를 함께 출력

sol) 아래 두개의 풀이는 같은 정답이 나옴을 알 수 있다. 

 

select e1.ename as 사원명, e1.sal as 급여, 
       e2.ename as 상사명, e2.sal as 상사급여
  from emp e1, emp e2
 where e1.mgr = e2.empno(+);

 

 

select e2.ename as 사원명, e2.sal as 급여, 
       e1.ename as 상사명, e1.sal as 상사급여
  from emp e1, emp e2
 where e1.empno(+) = e2.mgr;

 

 

 

 

예제) dept2 테이블을 사용하여 각 부서명과 상위부서명 함께 출력
단, 상위부서가 없는 사장실도 함께 출력
select d1.dname as 부서명, d2.dname as 상위부서명
 from dept2 d1, dept2 d2
 where d1.pdept = d2.dcode(+);

 

 

예제) emp 테이블을 사용하여 각 직원의 이름, 입사일과 함께 입사연도 같은 동기 수 출력
단, 동기가 없는 경우도 생략없이 출력
select e1.ename, e1.hiredate, count(e2.empno) as 동기수
  from emp e1, emp e2
 where to_char(e1.hiredate,'YYYY') = to_char(e2.hiredate(+),'YYYY')
   and e1.empno != e2.empno(+)
 group by e1.ename, e1.hiredate;