1. 자기참조 조인 (SELF JOIN)
SELECT 별명1.컬럼1, ... 별명2.컬럼2, ...
FROM 테이블 별명1, 테이블 별명2, ... >> 같은 테이블에 ALIAS 만 다르게 줌
WHERE 조인조건
AND 일반조건
- 동일 테이블을 자기 참조에 의해 조인 (교집합 개념.)
- 별명 1과 별명 2는 동일 테이블에 대한 ALIAS이지만, 각각 별도의 테이블처럼 사용.
- 하나의 테이블에 별명을 다르게 해서 마치 여러개의 테이블간 조인하는 것처럼 문장을 구현.
- 서로 다른 별명을 별개의 테이블로 가정하면 보통의 등가조인이나 비 등가조인과 동일.
- 테이블에 반드시 별명을 붙여야 되므로 별명을 이용한 조인이라 부르기도 함.
2. 외부조인 (OUTER JOIN)
SELECT 테이블1.컬럼, ... 테이블2.컬럼, ...
FROM 테이블1, 테이블2, ...
WHERE 조인조건(+)
AND 일반조건
3. ANSI QUERY
SELECT 테이블1.컬럼, ... 테이블2.컬럼, ..
FROM 테이블1
[CROSS JOIN 테이블2]
| [NATURAL JOIN 테이블2]
| [JOIN 테이블2 USING (컬럼명)]
| [JOIN 테이블2 ON(테이블1.컬럼명=테이블2.컬럼명)]
| [LEFT | RIGHT | FULL [OUTER] JOIN 테이블2
ON(테이블1.컬럼명=테이블2.컬럼명)]
NATURAL JOIN과 USING JOIN은 두 테이블을 연결할 컬럼이 이름, 데이터타입, 데이터가 모두 일치할 때만 사용 가능. (컬럼명, 데이터타입이 일치해도 내부 데이터가 다른 컬럼이 존재할 경우 데이터가 조회되지 않는다)
두 테이블을 연결하는 컬럼은 소속 테이블을 지정하면 오류 발생 (EMP.DEPTNO 와 같이 식별자를 이용할 수 X)
ON JOIN
두 테이블을 연결할 컬럼명이 다른 경우엔 반드시 ON절을 사용.
대부분의 경우 ON절을 사용한다.
EXERCISE 1
각 사원을 관리하는 사수의 이름
SELECT E1.ENO 사번, E1.ENAME 사원명, E1.JOB 업무, E1.MGR 사수_사번, E2.ENAME 사수_이름
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.ENO;EXERCISE 2
각 부서별로 사원 검색 (일반 조인과 외부 조인 결과물 비교)
---- INNER JOIN >> A.DNO와 B.DNO 가 매칭되는 값만 조회됨 (교집합)
SELECT A.DNO 부서번호, A.DNAME 부서명, B.ENAME 사원명
FROM DEPT A, EMP B
WHERE A.DNO = B.DNO
AND D.LOC = '광주';
ORDER BY 1;
---- ANSI QUERY
SELECT*
FROM DEPT A
INNER JOIN EMP B ON A.DNO = B.DNO AND A.LOC = '광주' --필터조건은 INNER JOIN에서는 상관없지만 OUTER JOIN 에서는 반드시 WEHRE절로 걸어야 함.
--WHERE A.LOC = '광주'; --이렇게!(LEFT) OUTER JOIN
: 왼쪽에 있는 테이블이 메인 테이블, 조인되는 테이블이 서브 테이블.
메인 테이블의 데이터는 모두 조회 되고, 서브 테이블은 매칭 되는 값만 조회. 매칭 안되면 NULL.
따라서 메인 테이블이 바뀌면 조회되는 데이터도 달라진다.
SELECT A.DNO 부서번호, A.DNAME 부서명, B.ENAME 사원명
FROM DEPT A, EMP B
WHERE A.DNO = B.DNO(+)
ORDER BY 1;
----ANSI QUERY
SELECT A.DNO 부서번호, A.DNAME 부서명, B.ENAME 사원명
FROM DEPT A
LEFT OUTER JOIN EMP B ON A.DNO = B.DNO
ORDER BY 1;1. 학생중에 동명이인을 검색
SELECT DISTINCT S1.SNO 학번, S1.SNAME 이름
FROM STUDENT S1, STUDENT S2
WHERE S1.SNAME = S2.SNAME
AND S1.SNO != S2.SNO
ORDER BY S1.SNAME, S1.SNO;2. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과순으로 검색
SELECT P.PNO --교수 번호
,P.PNAME --교수 이름
,P.SECTION --전공
,C.CNO --과목 번호
,C.CNAME
,ST.SNO
,ST.SNAME
,SC.RESULT
FROM PROFESSOR P, COURSE C, SCORE SC, STUDENT ST
WHERE P.PNO = C.PNO(+) --INNER JOIN 하면 담당과목 없는 교수는 조회 X. 담당과목 NULL인 교수도 조회하려면 OUTER JOIN
AND C.CNO = SC.CNO(+)
AND SC.SNO = ST.SNO(+) --OUTER JOIN 할 때 메인테이블 외에 나머지 테이블에도 모두 (+) 로 연결해야함.
AND P.SECTION = '물리'
AND P.PNO IN ('1018', '1009')
ORDER BY P.SECTION;
----ANSI QUERY
SELECT P.PNO --교수 번호
,P.PNAME --교수 이름
,P.SECTION --전공
,C.CNO --과목 번호
,C.CNAME
,ST.SNO
,ST.SNAME
,SC.RESULT
FROM PROFESSOR P
LEFT OUTER JOIN COURSE C ON P.PNO = C.PNO
LEFT OUTER JOIN SCORE SC ON C.CNO = SC.CNO
LEFT OUTER JOIN STUDENT ST ON SC.SNO = ST.SNO
WHERE 1=1
AND P.SECTION = '물리'
AND P.PNO IN ('1018', '1009')
ORDER BY P.SECTION;3. 이번학기 등록된 모든 과목과 담당 교수를 학점 순으로 검색
SELECT C.CNO 과목번호
,C.CNAME 과목명
,C.ST_NUM 학점수
,P.PNO 담당교수_번호
,P.PNAME 담당교수
,ST.SNO
,ST.SNAME
FROM COURSE C, PROFESSOR P, SCORE SC, STUDENT ST
WHERE C.PNO = P.PNO(+)
AND C.CNO = SC.CNO(+)
AND SC.SNO = ST.SNO(+)
AND C.CNO IN ('1227', '2369')
ORDER BY C.ST_NUM;
----ANSI QUERY
SELECT C.CNO 과목번호
,C.CNAME 과목명
,C.ST_NUM 학점수
,P.PNO 담당교수_번호
,P.PNAME 담당교수
,ST.SNO
,ST.SNAME
FROM COURSE C
LEFT OUTER JOIN PROFESSOR P ON C.PNO = P.PNO
LEFT OUTER JOIN SCORE SC ON C.CNO = SC.CNO
LEFT OUTER JOIN STUDENT ST ON SC.SNO = ST.SNO
WHERE C.CNO IN ('1227','2369')
ORDER BY C.ST_NUM;4. 직원중에 자신의 관리자보다 급여가 높은 사원의 급여 정보를 관리자 급여 정보와 같이 검색
SELECT A.ENO 사번
, A.ENAME 이름
, A.SAL 급여
, B.ENO 관리자_사번
, B.ENAME 관리자_이름
, B.SAL 관리자_급여
FROM EMP A, EMP B --A:일반 직원, B:관리자
WHERE A.MGR = B.ENO
AND A.SAL > B.SAL
ORDER BY A.SAL;5. 교수 정보와 교수가 담당하는 과목명 검색
SELECT P.*
, C.CNO --과목번호
, C.CNAME --과목명
, C.ST_NUM --학점수
FROM PROFESSOR P, COURSE C
WHERE P.PNO = C.PNO (+);6. 직원과 사수의 명단 검색. 단, 직원명단은 모든 직원 명단 출력
SELECT *
FROM EMP A, EMP B --A:일반직원, B:사수
WHERE A.MGR = B.ENO(+);