[SQL] ANSI Join

조인 방법에는 오라클전용 조인과 ANSI 조인이 있다.
해당 포스팅에서는 ANSI 조인에 대해서 알아볼 것이다.

◆ ANSI 기본 조인 문법

from table1 alias1 join table2 alias2
on (alias1.column_name1 = alias2.column_name2);

테이블이 3개 이상일 때는 어떻게 조인할까?

from table1
join table2
on 조인조건
join table3
on 조인조건

◆ Non-Equi Join
ANSI의 Non-Equi Join 문법도 오라클 전용에서의 그것과 사용 방법은 동일하다.

from table1 join table2
on column between 조건1 and 조건2;

◆ Outer Join
한가지 오라클 전용에서와 다른 점 및 주의해야 할 점은 오라클에서는 없는 쪽에 가중치(+)를 두지만 ANSI에서는 데이터가 있는 쪽에 가중치를 둔다.
Outer Join의 종류에는 Left Outer Join, Right Outer Join, Full Outer Join이 있다.

<Left Outer Join>
from table1 left outer join table2
on 조건;

<Right Outer Join>
from table1 right outer join table2
on 조건;

<Full Outer Join>
from table1 full outer join table2
on 조건;

◆ Cross Join(Cartesian Product)
ANSI의 Cross Join 문법도 오라클 전용에서의 그것과 사용 방법은 동일하다. 조건을 주지 않으면 된다.

from table1 cross join table2;

---

[문제] 교수별로 학과번호, 교수이름, 교수별 지도학생수, 학과별 소계 학생수, 총 학생수를 출력하시오.

SCOTT> select     p.deptno "학과번호",
  2     p.name "교수이름",
  3     count(s.studno)
  4     from professor p, student s
  5     where p.profno = s.profno(+)
  6    group by rollup(p.deptno, p.name)

  학과번호 교수이름   COUNT(S.STUDNO)
---------- ---------- ---------------
       101 김도훈                   0
       101 성연희                   2
       101 이만식                   1
       101 전은지                   3
       101                             6
       102 권혁일                   3
       102 염일웅                   0
       102                             3
       201 이재우                   1
       201                             1
       202 남은혁                   0
       202                             0
                                       10

13 rows selected.


또는 아래와 같이 주어도 된다.
select p.deptno, p.name, nvl(count(s.name), 0) "지도학생수"
 from professor p, student s
 where s.profno(+) = p.profno
 group by rollup(p.deptno, p.name)
 order by 1;

[문제] 각 학과별로 학과번호, 학과이름, 소속학생이름, 학생의 지도교수 이름,
지도교수 직급, 지도교수 소속 학과번호, 지도교수 소속 학과명을 출력하세요.
(단, 지도교수가 없는 학생과 지도학생이 없는 교수 모두 출력하세요)

SCOTT> select d1.deptno "학과번호",
  2   d1.dname "학과이름",
  3   s.name "소속학생이름",
  4   p.name "학생지도교수이름",
  5   p.position "지도교수직급",
  6   d2.deptno "지도교수소속학과번호",
  7   d2.dname "지도교수소속학과명"
  8   from department d1 join student s
  9   on d1.deptno = s.deptno full outer join professor p
 10   on s.profno = p.profno left outer join department d2
 11   on p.deptno = d2.deptno
 12  order by 1

학과번호  학과이름       소속학생이  학생지도교 지도교수직급  지도교수소속학과번호 지도교수소속학과
-------- ------------- ---------- ---------- ------------- -------------------- ----------------
       101 컴퓨터공학과       지은경         전은지   전임강사                                101 컴퓨터공학과
       101 컴퓨터공학과       서재진
       101 컴퓨터공학과       박미경
       101 컴퓨터공학과       이광훈         성연희   조교수                                   101 컴퓨터공학과
       101 컴퓨터공학과       전인하         성연희   조교수                                   101 컴퓨터공학과
       101 컴퓨터공학과       김영균         이만식   부교수                                   101 컴퓨터공학과
       101 컴퓨터공학과       류민정         전은지   전임강사                                101 컴퓨터공학과
       101 컴퓨터공학과       임유진         전은지   전임강사                                101 컴퓨터공학과
       102 멀티미디어학과    김진영         권혁일   교수                                      102 멀티미디어학과
       102 멀티미디어학과    오유석         권혁일   교수                                      102 멀티미디어학과
       102 멀티미디어학과    윤진욱         권혁일   교수                                      102 멀티미디어학과
       102 멀티미디어학과    하나리
       201 전자공학과          박동진
       201 전자공학과          이동훈
       201 전자공학과          김진경         이재우   조교수                                   201 전자공학과
       201 전자공학과          조명훈
                                                       김도훈   교수                                      101 컴퓨터공학과
                                                       남은혁   부교수                                   202 기계공학과
                                                       염일웅   전임강사                                102 멀티미디어학과

19 rows selected.


또는 아래와 같이 해줘도 된다.
select d.deptno, d.dname, s.name, p.name, p.position, dept.deptno, dept.dname
 from student s
 full outer join professor p on p.profno = s.profno
 left outer join department d on d.deptno = s.deptno
 left outer join department dept on dept.deptno = p.deptno
 order by 1, 3;