조인 : 자기 참조 조인(Self join)과 외부조인(Outer join)
조인 문을 생성하는 과정
step 1. 지문에서 검색 대상과 조건을 구분한다.
- 이를 통해 일단 SELECT 절, WHERE 절, ORDER BY 절을 구성한다.
- SELECT 절은 step 5에서 작성자의 의도에 따라 추가될 수 있다.
step 2. SELECT 절과 WHERE 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
step 3. 테이블간의 관계를 확인한다.
- 관계를 확인하는 단계에서 테이블이 추가될 수 있다.
step 4. 조인 조건을 기술한다.
step 5. 전체 문장을 다듬는다.
예제 1. 위에 제시한 단계별로 지문으로부터 SQL문을 만들어 보자.
- 화학과 학생의 일반화학 기말고사 점수를 검색한다.
step 1. 지문에서 검색 대상과 조건을 찾는다.
step 2. SELECT 절과 WHERE 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
step 3. 테이블간의 관계를 확인한다.
step 4. 조인 조건을 기술한다.
step 5. 전체 문장을 다듬는다.
- 4학년 학생이 수강하는 과목을 강의하는 교수의 명단을 검색한다.
step 1. 지문에서 검색 대상과 조건을 찾는다.
step 2. SELECT 절과 WHERE 절의 내용에 따라 정보를 검색할 테이블을 찾는다.
step 3. 테이블간의 관계를 확인한다.
step 4. 조인 조건을 기술한다.
step 5. 전체 문장을 다듬는다.
자기 참조 조인 (Self Join)
: 같은 TABLE 조인 (TABLE 하나 더 생성하고 조인)
별명을 이용한 조인
SELECT 별명1.컬럼1, ... 별명2.컬럼1, ...
FROM 테이블 별명1, 테이블 별명2, ...
WHERE 조인_조건
AND 일반_조건
예제 2. 각 사원을 관리하는 사수의 이름을 검색한다. (mgr : 사수 사번)
SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno;
ENO ENAME MGR ENO ENAME
------- ---------- -------- ------- ----------
0201 안영숙 0001 0001 안영희
0202 손하늘 0001 0001 안영희
0301 이승철 0001 0001 안영희
2007 이초록 0001 0001 안영희
2008 윤고은 0001 0001 안영희
1001 문시현 0201 0201 안영숙
1002 김주란 0201 0201 안영숙
외부 조인(Outer join)
SELECT 테이블1.컬럼, ..테이블2.컬럼, ...
FROM 테이블1, 테이블2, ...
WHERE 조인_조건(+)
AND 일반_조건데이터 없는 쪽에 (+) 붙이기
예제 3. 각 부서별로 사원를 검색한다. (외부 조인과 일반 조인 결과물의 비교)
- 일반 조인
SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e
WHERE d.dno = e.dno
ORDER BY 1;
부서번호 부서명 사원명
--------- ------- ----------
01 총무 안영숙
01 총무 안영희
01 총무 손하늘
02 회계 박선경
02 회계 이승철
02 회계 천유정
02 회계 김민지SQL문은 맞으나 데이터가 틀림.
- 외부 조인
SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e
WHERE d.dno = e.dno(+)
ORDER BY 1;
부서번호 부서명 사원명
--------- ------- ----------
01 총무 안영희
01 총무 안영숙
01 총무 손하늘
02 회계 이승철
02 회계 박선경
02 회계 천유정
02 회계 김선유
...
부서번호 부서명 사원명
--------- ------- ----------
50 POS데이터 없는 emp 테이블에 (+) 기호를 붙인다.
실습
1. 학생 중에 동명이인을 검색한다.
SELECT DISTINCT s1.sname 이름, s1.sno 학번
FROM student s1, student s2
WHERE s1.sname = s2.sname
AND s1.sno != s2.sno
ORDER BY s1.sname;
이름 학번
---------- -------
유태지 905301
유태지 943905
유태지 948209
황진혜 913902
황진혜 913908
2. 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색한다.
SELECT p.pno 교수번호, pname 교수이름, section 학과, cno 과목번호, cname 과목이름
FROM professor p, course c
WHERE p.pno = c.pno(+)
ORDER BY section;
교수번호 교수이름 학과 과목번호 과목이름
--------- ---------- ------- --------- ------------
1018 김응전 물리 1290 열역학
1016 호연작 물리 1287 전산물리
1013 하영진 물리 1245 실험물리학
1012 이영준 물리 1233 역학
1013 하영진 물리 1223 자기학
1009 이준영 물리
1016 호연작 물리 1226 양자물리학
...
3. 이번 학기 등록된 모든 과목과 담당 교수를 학점 순으로 검색한다.
SELECT st_num 학점, cno 과목번호, cname 과목명, c.pno 교수번호, pname 교수이름
FROM course c, professor p
WHERE c.pno = p.pno(+)
ORDER BY st_num;
학점 과목번호 과목명 교수번호 교수이름
---------- --------- ------------ --------- ----------
2 1228 위상수학 1018 김응전
2 2365 전자기학 1029 주동평
2 2112 영양학실험 1034 최해연
2 2313 분류학실험 1027 임충원
2 1211 일반화학실험 1001 송강
2 1218 생화학 1008 문규식
2 2366 물리실험 1030 김동평
학점 과목번호 과목명 교수번호 교수이름
---------- --------- ------------ --------- ----------
2 1214 무기화학 1004 시진영
2 1245 실험물리학 1013 하영진
2 1216 환경화학 1006 장청아
2 1227 이학수학 1017 최무송
2 2369 무기화학실험
2 1711 유전학실험 1036 성현수
3 2312 생리학 1022 이준
...
4. 직원 중에 자신의 관리자 보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색한다.
SELECT e1.eno 사번, e1.ename 이름, e1.sal 급여, e1.mgr 관리자번호, e2.eno 사번, e2.ename 이름, e2.sal 급여
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno
AND e1.sal > e2.sal;
사번 이름 급여 관리자번호 사번 이름 급여
------- ---------- ---------- ------------ ------- ---------- ----------
1001 문시현 4500 0201 0201 안영숙 3900
1002 김주란 4100 0201 0201 안영숙 3900
1003 양선호 4300 0201 0201 안영숙 3900
2001 남궁연호 3950 0202 0202 손하늘 3510
2003 정의찬 4350 0202 0202 손하늘 3510
5. 교수의 정보와 교수가 담당하는 과목명을 검색한다.
SELECT p.pno 교수번호, pname 교수이름, cno 과목번호, cname 과목이름
FROM professor p, course c
WHERE p.pno = c.pno(+);
교수번호 교수이름 과목번호 과목이름
--------- ---------- --------- ------------
1001 송강 1211 일반화학실험
1004 시진영 1212 일반화학
1006 장청아 1213 유기화학
1004 시진영 1214 무기화학
1006 장청아 1216 환경화학
1007 이초아 1217 고분자화학
1008 문규식 1218 생화학
...
교수번호 교수이름 과목번호 과목이름
--------- ---------- --------- ------------
1021 왕사진 2322 생물정보학
1027 임충원 2313 분류학실험
1029 주동평 2329 발생생물학
1033 박삭광
1009 이준영
6. 직원과 사수의 명단을 검색한다. 단 직원 명단은 모든 직원 명단이 출력되어야 한다.
SELECT e1.eno 사번, e1.ename 이름, e1.mgr 관리자번호, e2.eno 사번, e2.ename 이름
FROM emp e1, emp e2
WHERE e1.mgr = e2.eno(+);
사번 이름 관리자번호 사번 이름
------- ---------- ------------ ------- ----------
0201 안영숙 0001 0001 안영희
0202 손하늘 0001 0001 안영희
0301 이승철 0001 0001 안영희
2007 이초록 0001 0001 안영희
2008 윤고은 0001 0001 안영희
1001 문시현 0201 0201 안영숙
1002 김주란 0201 0201 안영숙
...
사번 이름 관리자번호 사번 이름
------- ---------- ------------ ------- ----------
0309 김선유 0302 0302 박선경
3001 김선유 1001 1001 문시현
0401 김진성 1001 1001 문시현
0801 천유정 1001 1001 문시현
3002 권아현 1002 1002 김주란
0120 김경현 1002 1002 김주란
0001 안영희
7. 화학과 학생 중에 학점이 동일한 학생을 검색한다.
SELECT s1.sno 학번, s1.sname 이름, s1.major 학과, s1.avr 평점
FROM student s1, student s2
WHERE s1.sno != s2.sno
AND s1.avr = s2.avr
AND s1.major = s2.major
AND s1.major = '화학';
학번 이름 학과 평점
------- ---------- ------- ----------
915303 정욱주 화학 .95
915301 정동상 화학 .95
'SQL 공부 기록' 카테고리의 다른 글
| SQL 11 - 서브 쿼리 : 다중 행, 다중 열 서브 쿼리 (0) | 2021.11.09 |
|---|---|
| SQL 10 - 서브 쿼리 : 단일 행 서브 쿼리 (0) | 2021.11.05 |
| SQL 8 - 조인 : 등가 조인(Equi join)과 비등가 조인(Non Equi-join) (0) | 2021.11.01 |
| SQL 7 - BETWEEN과 IN 연산자 (0) | 2021.10.28 |
| SQL 6 - 관계 연산자와 LIKE 연산자 (0) | 2021.10.27 |