JOIN 기법은 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블 처럼 만들어 결과를 보여준다.
JOIN은 ORACLE 용 JOIN이 있고, 모든 제품들에서 공통적으로 사용가능한 표준(ANSI) JOIN 방법이 있다.
1. Cartesian Product ( 카티션 곱 )
카티션 곱이란 Join 쿼리 중에 WHERE 절에 기술하는 Join 조건이 잘못 기술되었거나 아예 없을 경우 발생하는 한다.
이런 경우 Join 작업에 참조 되는 테이블 행수를 모두 곱한 값의 결과가 만들어진다.
ANSI SQL에서는 CROSS JON 이라고도 부른다.
ORACLE
SELECT e.ename, d.dname
FROM emp e, dept d ;
ANSI
SELECT e.ename, d.dname
FROM emp e CROSS JOIN dept d ;
2. EQUI Join ( 등가 Join )
WHERE 절에 기술되는 Join 조건으로 양쪽 테이블에 같은 조건이 존재할 경우의 값만을 가져오는 Join이다.
ORACLE
SELECT s.name, d.dname, p.name
FROM student s, department d, professor p
WHERE s.deptno1 = d.deptno
AND s.profno = p.profno ;
ANSI
SELECT s.name, d.dname, p.name
FROM student s
JOIN department d
ON s.deptno1 = d.deptno
JOIN professor p
ON s.profno = p.profno ;
SELECT s.name, p.name
FROM student s, professor p
WHERE s.deptno1 = 101 검색조건
AND s.profno = p.profno ; 조인조건
※ 검색조건을 먼저 써줘야 속도가 더 빠르다.
SELECT s.name, p.name
FROM student s JOIN professor p
ON s.deptno1 = 101
AND s.profno = p.profno ;
3. Non-Equi Join ( 비등가 Join )
Join 조건에 같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 Join을 수행한다.
ORACLE
SELECT gi.gname "상품명", COUNT(*) "필요수량"
FROM gogak go, gift gi
WHERE go.point BETWEEN gi.g_start AND g_end
GROUP BY gi.gname;
ANSI
SELECT gi.gname "상품명", COUNT(*) "필요수량"
FROM gogak go JOIN gift gi
ON go.point BETWEEN gi.g_start AND g_end
GROUP BY gi.gname;
ORACLE
SELECT go.gname, go.point, gi.gname
FROM gogak go, gift gi
WHERE gi.gname = '산악용자전거'
AND gi.g_start <= go.point ;
ANSI
SELECT go.gname, go.point, gi.gname
FROM gogak go JOIN gift gi
ON gi.gname = '산악용자전거'
AND gi.g_start <= go.point ;
ORACLE
SELECT e.name "이름",
TRUNC((sysdate-e.birthday)/365,0) "현재나이",
e.position "현재직급",
g.position "예상직급"
FROM emp2 e, p_grade g
WHERE TRUNC((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age;
ANSI
SELECT e.name "이름",
TRUNC((sysdate-e.birthday)/365,0) "현재나이",
e.position "현재직급",
g.position "예상직급"
FROM emp2 e JOIN p_grade g
ON TRUNC((sysdate-e.birthday)/365,0) BETWEEN g.s_age AND g.e_age;
4. OUTER Join ( 아우터 조인 )
OUTER Join은 한쪽 테이블에는 데이터가 있고 한쪽 테이블에 없는 경우에 데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는 방법이다.
이 Join 방식은 DB 성능에 아주 나쁜 영향을 줄 수 있다는 것을 명심하고 주의해야 한다.
테이블에 인덱스가 있어도 인덱스를 쓰지 않고 Full Scan을 하기 때문이다.
학생이름과 지도교수 이름 출력하기. 단, 지도교수가 결정되지 않은 학생 명단도 출력
ORACLE
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 ;
ANSI Outer Join 구문은 ORACLE Outer Join 구문과 반대로 데이터가 있는 쪽에 표시를 해준다.
학생이름과 지도교수 이름 출력하기. 단, 지도학생이 결정되지 않은 교수 명단도 출력
ORACLE
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 ;
학생이름과 지도교수 이름 출력하기. 단, 지도교수가 결정되지 않은 학생 명단, 지도교수가 결정되지 않은 학생 모두 출력
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(+) ;
ANSI
SELECT s.name, p.name
FROM student s FULL OUTER JOIN professor p
ON s.profno = p.profno ;
5. SELF Join
원하는 데이터가 하나의 테이블에 다 들어있을 때 사용한다.
ORACLE
SELECT a.dname "부서명", b.dname "상위부서명"
FROM dept2 a, dept2 b
WHERE a.pdept = b.dcode ;
ANSI
SELECT a.dname "부서명", b.dname "상위부서명"
FROM dept2 a JOIN dept2 b
ON a.pdept = b.dcode ;
교수번호, 교수이름, 입사일, 자신보다 입사일 빠른 사람 인원수를 인원수 오름차순으로 출력.
ORACLE
SELECT a.profno, a.name, a.hiredate, COUNT(b.hiredate)
FROM professor a, professor b
WHERE b.hiredate(+) < a.hiredate
GROUP BY a.profno, a.name, a.hiredate
ORDER BY 4 ;
ANSI
SELECT a.profno, a.name, a.hiredate, COUNT(b.hiredate)
FROM professor a LEFT OUTER JOIN professor b
ON b.hiredate < a.hiredate
GROUP BY a.profno, a.name, a.hiredate
ORDER BY 4 ;
[출처] 다양한 예제로 쉽게 배우는 오라클 SQL과 PL/SQL 서진수 저