[Oracle] SELECT 활용 - 3 (JOIN)

조인

SELECT 테이블.컬럼,
FROM 테이블, 테이블, ....
WHERE 조인_조건
AND 일반_조건
ORDER BY 컬럼 ....;

• 조인 조건: 테이블 간의 관계 수식
• 조인 문장에서 기술된 모든 테이블은 반드시 조인 조건으로 기술됨.
• 동일한 이름의 컬럼이 여러 테이블에 존재하는 경우 컬럼명 앞에 반드시 테이블명이 필요함
• 테이블 개수가 n개일 때 조인 조건은 항상 (n-1)개 이상
• 등가 조인 : 조인 조건이 등호('=')
• 비등가 조인: 조인 조건이 부등호

등가 조인

외래키와 같은 값으로 조인
ex) emp.dno = dept.dno

//각 사원의 근무 부서와 근무지를 검색한다.
SELECT dept.dno 부서번호, dname 부서, loc 근무처, eno 사번, ename 이름
FROM dept, emp
WHERE dept.dno=emp.dno;

SELECT d.dno 부서번호, dname 부서, loc 근무처, eno 사번, ename 이름
FROM dept d, emp e
WHERE d.dno=e.dno;

비등가 조인

비슷한 값 (범위)
ex) emp.sal BETWEEN salgrade.losal AND salgrade.hisal (emp.sal >= salgrade.losal AND emp.sql <= salgrade.hisal)

//각 사원의 급여 등급을 검색한다.
SELECT eno, ename, sal, (salgrade.)grade
FROM emp e, salgrade s
WHERE sal BETWEEN losal AND hisal;

//총무 부서 사원의 급여를 10% 인상한 경우 사원의 급여등급
SELECT eno 사번, ename 이름, sal*1.1 급여, dname 부서, grade 등급
FROM emp e, dept d, salgrade s
WHERE e.dno = d.dno AND dname = '총무' AND sal*1.1 BETWEEN losal AND hisal;

예제 - 1

1. 송강 교수가 강의하는 과목을 검색한다.
SELECT p.pno, pname, cno, cname
FROM professor p, course c
WHERE p.pno=c.pno
AND pname='송강';

2. 과목명에 화학이 포함된 과목을 강의하는 교수의 명단을 검색한다.
SELECT DISTINCT pname 명단
FROM course c, professor p
WHERE c.pno=p.pno
AND cname LIKE '%화학%';

3. 학점이 2학점인 과목과 이를 강의하는 교수를 검색한다.
SELECT cno, cname, st_num, pname
FROM course c, professor p
WHERE c.pno=p.pno
AND st_num=2;

4. 화학과 교수가 강의하는 과목을 검색한다.
SELECT cname
FROM professor p, course c
WHERE p.pno=c.pno
AND section='화학';

5. 화학과 1학년 학생의 기말고사 성적을 검색한다.
SELECT st.sno, sname, syear, major, cname, result
FROM student st, score c, course o
WHERE st.sno=c.sno AND o.cno=c.cno
AND major='화학' AND syear=1
ORDER BY st.sno;

6. 일반화학 과목의 기말고사 점수를 검색한다.
SELECT c.cno, cname, result 
FROM course c, score s
WHERE c.cno=s.cno
AND cname='일반화학';

7. 화학과 1학년 학생의 일반화학 기말 고사 점수를 검색한다. 
SELECT major,syear, st.sno,sname, cname, result
FROM student st, course c, score s
WHERE st.sno=s.sno AND s.cno=c.cno
AND major='화학' AND cname='일반화학' AND syear=1;

8. 화학과 1학년 학생이 수강하는 과목을 검색한다.
SELECT DISTINCT cname
FROM student st , score s, course c
WHERE st.sno=s.sno AND s.cno=c.cno
AND major='화학' AND syear=1;

9. 일반화학 과목에서 평가 점수가 A인 학생의 명단을 검색한다.
SELECT st.sno, major ,sname, grade
FROM student st, course c, score s, scgrade
WHERE st.sno=s.sno AND s.cno=c.cno 
AND s.result BETWEEN loscore AND hiscore
AND grade='A' AND cname='일반화학'; 

10. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순서로 검색한다.
SELECT st.sno, sname, cname, result
FROM professor p, course c, student st, score s
WHERE p.pno=c.pno AND st.sno=s.sno AND s.cno= c.cno
AND pname='송강'
ORDER BY result DESC;

11. 화학과 1학년 학생의 기말고사 성적을 학점(A,B,C,D,F)으로 검색한다. 
SELECT st.sno, sname, cname, grade
FROM student st, score s, scgrade sg, course c
WHERE st.sno=s.sno AND s.cno=c.cno 
AND result BETWEEN loscore AND hiscore
AND major ='화학' AND syear=1
ORDER BY st.sno, grade;

12. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색한다.
SELECT pname, cname, st.sno, sname, grade
FROM professor p, course c, score s, student st,scgrade sg
WHERE p.pno = c.pno AND c.cno = s.cno AND s.sno = st.sno
AND result BETWEEN loscore AND hiscore
AND pname='송강' AND grade='A';

13. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색한다.
SELECT DISTINCT p.pno, pname
FROM student st, score s,course c, professor p
WHERE st.sno = s.sno AND s.cno = c.cno AND c.pno = p.pno
AND major='화학' AND syear=1;

자기 참조 조인(Self Join)

SELECT  별명1.컬럼1, ... 별명2.컬럼1, ...
FROM  테이블 별명1, 테이블 별명2, ...
WHERE  조인_조건 AND 일반_조건;

• 동일 테이블을 자기 참조에 의해 조인
• 별명1과 별명2를 각각 별도의 테이블처럼 사용

SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno;

외부 조인(Outer Join)

SELECT  컬럼, 컬럼, ...
FROM  테이블1, 테이블2, ...
WHERE  조인_조건(+)
AND 일반_조건

• 조인 조건에 일치하지 않는 데이터를 출력
• 조인 결과물을 해석할 때 오해를 방지하기 위한 방법 ( 결과의 무결성 제공 )
• + 기호는 데이터가 부족 한 쪽에 붙인다.

//결과 값 비교
SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e         
WHERE d.dno = e.dno
ORDER BY 1;
부서 부서명                         사원명
---- ------------------------------ ------------------------------
...
30   ITEA                           양선호
30   ITEA                           이초록
30   ITEA                           정의찬
40   CRM                            윤고은

SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e             
WHERE d.dno = e.dno(+)
ORDER BY 1;

부서 부서명                         사원명
---- ------------------------------ ------------------------------
...
30   ITEA                           양선호
30   ITEA                           이초록
30   ITEA                           정의찬
40   CRM                            윤고은
50   POS

Table dept
DNO  DNAME                          LOC
---- ------------------------------ ------------------
01   총무                           서울
02   회계                           서울
10   ERP                            서울
20   ISP                            부산
30   ITEA                           광주
40   CRM                            대전
50   POS

예제 - 2

1. 학생 중에 동명이인을 검색한다.
SELECT st1.sno, st1.sname, st2.sno, st2.sname
FROM student st1, student st2
WHERE st1.sno!=st2.sno AND st1.sname=st2.sname;

2. 모든 직원의 명단과 이를 관리하는 직원의 명단을 검색한다.
SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno(+);

3. 직원 중에 자신의 관리자 보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색 한다.
SELECT e1.eno, e1.ename, e1.mgr, e1.sal, e2.eno, e2.ename, e2.sal
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno AND e1.sal>e2.sal;

4. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색한다. 
SELECT p.pno, pname, section, cno, cname
FROM professor p, course c	
WHERE p.pno=c.pno(+)
ORDER BY section, p.pno;

5. 모든 과목 목록과 각 과목별 담당 교수를 검색한다.
SELECT p.pno, pname, section, cno, cname
FROM professor p, course c	
WHERE p.pno(+)=c.pno
ORDER BY section, p.pno;