JOIN
철칙!! : 앞테이블 데이터가 작아야함
문법)
SELECT table1.column, table2.column
FROM table1, table2
WHERE condition;
카티션 곱 (Cartesian Product)
두개 이상의 테이블에 대해 연결 가능한 행을 모두 결합하는 조인방법.
WHERE 절에서 조인 조건절을 생략하거나 조인 조건을 잘못 설정하여 양쪽 테이블을 연결하는 조건을 만족하는 행이 하나도 없는 경우에 발생한다.
예제 : 학생 테이블과 부서 테이블을 카티션 곱을 한 결과를 출력하여라.
SQL> SELECT name, student.deptno, dname, loc
FROM student, department;
위 문장을 실행하면 112개의 행이 나온다. 위 쿼리문에서 WHERE 조건이 없어서 생긴 문제이며 항상 이런 상황이 발생하지 않도록 주의해야 한다.
EQUI JOIN
SQL 문에서 가장 많이 사용되는 조인으로 조인 대상 테이블에서 공통칼럼을
'=' 비교를 통해 같은 값을 갖는 행을 연결하여 결과를 생성하는 조인 방법임.
문법)
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column = table2.column;
예제 : 학생 테이블과 부서 테이블을 EQUI JOIN 하여
학번, 이름, 학과번호, 소속학과 이름, 학과 위치를 출력하여라.
SQL> SELECT s.studno, s.name, s.deptno, d.dname, d.loc
FROM student s, department d
WHERE s.deptno = d.deptno
문제 : student 테이블과 department 테이블을 사용하여 학생명과 학과명을 출력하세요
SQL> select s.name 학생이름, d.dname 학과이름
from student s,department d
where s.deptno=d.deptno;
학생이름 학과이름
-------------------- --------------------------------
전인하 컴퓨터공학과
이동훈 전자공학과
박미경 컴퓨터공학과
김영균 컴퓨터공학과
박동진 전자공학과
김진영 멀티미디어학과
지은경 컴퓨터공학과
오유석 멀티미디어학과
하나리 멀티미디어학과
임유진 컴퓨터공학과
서재진 컴퓨터공학과
윤진욱 멀티미디어학과
이광훈 컴퓨터공학과
김진경 전자공학과
조명훈 전자공학과
류민정 컴퓨터공학과
16 rows selected.
문제 : Student , department , professor 테이블을 사용하여 학생이름,학과이름,지도교수이름을 아래와 같이 출력하세요.
SQL> select s.name 학생이름, d.dname 학과이름, p.name 지도교수명
from student s,department d, professor p
where s.deptno=d.deptno and s.profno=p.profno
학생이름 학과이름 지도교수명
-------------------- -------------------------------- --------------------
김진경 전자공학과 이재우
이광훈 컴퓨터공학과 성연희
전인하 컴퓨터공학과 성연희
윤진욱 멀티미디어학과 권혁일
오유석 멀티미디어학과 권혁일
김진영 멀티미디어학과 권혁일
김영균 컴퓨터공학과 이만식
류민정 컴퓨터공학과 전은지
임유진 컴퓨터공학과 전은지
지은경 컴퓨터공학과 전은지
10 rows selected.
NON-EQUI JOIN
조인 조건에서 '<',BETWEEN a AND b 와 같이 '=' 조건이 아닌 다른 종류의
연산자를 사용하는 조인 방법이다. 그다지 많이 사용되지는 않는다.
예제 : 교수 테이블과 급여 등급 테이블을 NON-EQUI JOIN 하여 교수별로 급여등급을 출력하여라.
SQL> SELECT p.profno, p.name, p.sal, s.grade
FROM professor p,salgrade s
WHERE p.sal BETWEEN s.losal AND s.hisal;
Test13 , test14 테이블을 사용하여
문제1) 고객별로 받을 수 있는 상품을 출력하고
CUST GIFT
-------------- -----------
771201-2233445 COMPUTER
620908-2121232 COMPUTER
500823-1132762 AUDIO
672102-2123452 의류교환권
650207-1765152 굴비세트
680801-1234455 갈비세트
…………………………..
***************************************
SQL> select t14.cust, t13.gift
from test13 t13, test14 t14
where t14.point between t13.fpoint and t13.tpoint;
CUST GIFT
---------------------------- ---------------
771201-2233445 COMPUTER
620908-2121232 COMPUTER
500823-1132762 AUDIO
672102-2123452 의류교환권
650207-1765152 굴비세트
680801-1234455 갈비세트
701212-1143211 PCS무료가입권
710125-1144951 구두상품권
721109-2144952 녹차세트
721101-2144952 도서상품권
10 rows selected.
*****************************************
문제2)각 상품이 몇 개가 필요한지 출력하세요.
GIFT COUNT(A.CUST)
-------------- -------------
COMPUTER 2
의류교환권 1
굴비세트 1
갈비세트 1
PCS무료가입권 1
…………………………..
******************************************************
SQL> select t13.gift, count(t14.cust)
from test13 t13,test14 t14
where t14.point between t13.fpoint and t13.tpoint
group by t13.gift;
GIFT COUNT(T14.CUST)
------------------------------------------------ ---------------
COMPUTER 2
의류교환권 1
굴비세트 1
갈비세트 1
PCS무료가입권 1
구두상품권 1
AUDIO 1
녹차세트 1
도서상품권 1
9 rows selected.
***********************************************************
Test13 , test14 테이블을 참조하여
문제 : 고객의 포인트보다 낮은 모든 모든 등급의 상품을 선택할 수 있다고 할때 갈비세트를 상품으로 받을 수 있는 고객의 고객번호, 포인트 , 선물을 보여주는 쿼리를 작성하시오.
CUST POINT GIFT
-------------- ---------- ----------
650207-1765152 20239650 갈비세트
672102-2123452 40935040 갈비세트
500823-1132762 75636408 갈비세트
771201-2233445 129855120 갈비세트
680801-1234455 10059470 갈비세트
620908-2121232 123674200 갈비세트
6 rows selected.
SQL> select cust,point,gift
from test14 b, test13 a
where a.fpoint <= b.point
and a.gift = '갈비세트';
CUST POINT GIFT
---------------------------- ---------- ------
650207-1765152 20239650 갈비세트
672102-2123452 40935040 갈비세트
500823-1132762 75636408 갈비세트
771201-2233445 129855120 갈비세트
680801-1234455 10059470 갈비세트
620908-2121232 123674200 갈비세트
6 rows selected.
문제 : temp 테이블의 사람 중 emp_level 테이블에서의 부장직급을 받아야 할 나이를 가지고 있는 사람의 사번,성명,생일,현재나이,현재 직급을 출력하세요.
SQL> select a.emp_id "사번", a.emp_name "이름",
to_char(a.birth_date,'YYYY-MM-DD') "생년월일",
trunc(months_between(sysdate,a.birth_date)/12) "현재나이", a.lev "현재직급"
from temp a, emp_level b
where trunc(months_between(sysdate,a.birth_date)/12)
between b.from_age and b.to_age
and b.lev='부장';
사번 이름 생년월일 현재나이 현재직급
---------- -------------------- -------------------- ---------- --------
19970101 김길동 1974-01-25 37 부장
19960101 홍길동 1973-03-22 38 과장
19970201 박문수 1975-04-15 36 과장
19930331 정도령 1976-05-25 35 차장
19950303 이순신 1973-06-15 38 대리
19966102 지문덕 1972-07-05 39 과장
19930402 강감찬 1972-08-15 39 차장
.......
20000102 김설악 1980-03-22 31 수습
20000203 최오대 1980-04-15 31 수습
20000334 박지리 1980-05-25 31 수습
20000305 정북악 1980-06-15 31 수습
20006106 유도봉 1980-07-05 31 수습
20000407 윤주왕 1980-08-15 31 수습
20000308 강월악 1980-09-25 31 수습
20000119 장금강 1980-11-05 31 수습
20000210 나한라 1980-12-15 31 수습
20 rows selected.
*********************
SQL> select a.emp_id "사번",a.emp_name "성명", a.birth_date "생일",
to_char(sysdate,'yyyy')-to_char(a.birth_date,'yyyy') "현재나이", a.lev
from temp a, emp_level b
where to_char(sysdate,'yyyy')-to_char(a.birth_date,'yyyy') >= b.from_age
and b.lev = '부장';
(+) 기호를 사용한 OUTER JOIN
NULL이 출력되는 칼럼에 (+) 기호를 추가하면 된다
OUTER JOIN 연산자 (+)는 NULL이 존재하는 칼럼 쪽에 표시한다.
예제 : 학생 테이블과 교수 테이블을 조인하여 이름, 학년, 지도 교수의 이름, 직급을 출력하여라.
단, 지도 교수가 배정되지 않은 학생이름도 함께 출력하여라.
SQL> SELECT s.name sname, s.grade, p.name pname, p.position
FROM student s, professor p
WHERE s.profno = p.profno(+)
ORDER BY p.profno;
문제 : 학생테이블과 교수테이블을 조인하여 이름, 학년,지도교수 이름,직급을 출력하세요.
단, 지도학생을 배정받지 않은 교수이름도 함께 출력하세요.
SQL> select s.name, s.grade, s.name, p.position
from student s,professor p
where s.profno(+)=p.profno;
NAME GR NAME POSITION
-------------------- -- -------------------- ----------------------------------------
전인하 4 전인하 조교수
김영균 3 김영균 부교수
김진영 2 김진영 교수
지은경 2 지은경 전임강사
오유석 4 오유석 교수
임유진 2 임유진 전임강사
윤진욱 3 윤진욱 교수
이광훈 4 이광훈 조교수
김진경 2 김진경 조교수
류민정 2 류민정 전임강사
교수
전임강사
부교수
13 rows selected.
SQL> select s.name, s.grade, s.name, p.position
from student s,professor p
where s.profno=p.profno(+);
NAME GR NAME POSITION
-------------------- -- -------------------- ----------------------------------------
김진경 2 김진경 조교수
이광훈 4 이광훈 조교수
전인하 4 전인하 조교수
윤진욱 3 윤진욱 교수
오유석 4 오유석 교수
김진영 2 김진영 교수
김영균 3 김영균 부교수
류민정 2 류민정 전임강사
임유진 2 임유진 전임강사
지은경 2 지은경 전임강사
조명훈 1 조명훈
서재진 1 서재진
하나리 1 하나리
박동진 1 박동진
박미경 1 박미경
이동훈 1 이동훈
16 rows selected.
SQL> select s.name " 이름", s.grade "학년", p.name "지도교수 이름", p.position "직급"
from student s, professor p
where s.profno(+) = p.profno;
문제 : Temp 테이블과 emp_level 테이블을 조인하여 각 사원의 사번,이름,직급,현재연봉,해당 직급의 연봉의 상,하한금액을 보고자 한다. 단 연봉의 상,하한이 결정 안 된 수습사원은 사번,이름,직급,현재연봉만 나오면 된다. 쿼리를 만들어보세요.
SQL> select t.emp_id,t.emp_name,t.lev,t.salary,e.from_sal,e.to_sal
from temp t, emp_level e
where t.lev=e.lev(+);
EMP_ID EMP_NAME LEV SALARY FROM_SAL TO_SAL
---------- -------------------- -------- ---------- ---------- ----------
19960303 설까치 사원 35000000 30000000 40000000
19970112 연흥부 대리 45000000 35000000 60000000
19950303 이순신 대리 56000000 35000000 60000000
19960212 배뱅이 과장 39000000 37000000 75000000
19966102 지문덕 과장 45000000 37000000 75000000
19970201 박문수 과장 50000000 37000000 75000000
19960101 홍길동 과장 72000000 37000000 75000000
19930402 강감찬 차장 64000000 40000000 80000000
19930331 정도령 차장 70000000 40000000 80000000
19970101 김길동 부장 100000000 60000000 100000000
20000210 나한라 수습 30000000
20000119 장금강 수습 30000000
20000308 강월악 수습 30000000
20000407 윤주왕 수습 30000000
20006106 유도봉 수습 30000000
20000305 정북악 수습 30000000
20000334 박지리 수습 30000000
20000203 최오대 수습 30000000
20000102 김설악 수습 30000000
20000101 이태백 수습 30000000
20 rows selected.
***************************************************************************
SQL> select a.emp_id "사번", a.emp_name "이름", a.lev "직급", a.salary "현재연봉",
b.from_sal "하한", b.to_sal "상한"
from temp a, emp_level b
where a.lev = b.lev(+);
셀프조인
하나의 테이블에서 두 개의 칼럼을 연결하여 조인
예제 : 부서번호가 201 이상인 부서 이름과 해당 부서가 소속된 상위 부서의 이름을 출력하여라.
SQL> SELECT dept.dname || ' is belong to ' || org.dname
FROM department dept, department org
WHERE dept.college = org.deptno
AND dept.deptno >= 201;
문제 : 학번이 10101 인 학생의 학번, 이름, 학과이름과 학과 위치를 출력하시오.
SQL> select s.studno,s.name,d.dname,d.loc
from student s, department d
whre s.deptno = d.deptno ← join조건
and s.studno = 10101; ← 검색조건
문제 : 몸무게가 80kg 이상인 학생의 학번, 이름, 체중, 학과이름, 학과위치를 출력하여라.
SQL> select s.studno, s.name, s.weight, d.dname, d.loc
from student s,department d
where s.deptno=d.deptno and s.weight>=80;
문제 : Self join, outer join , non-equi join을 이용하여 temp 테이블을 이용하여 아래 조건의 쿼리를 작성하시오. Temp 테이블을 사용해 사번, 성명, 생일, 자신보다 생일이 빠른 사람수를 조회하여 자신보다 생일이 빠른 사람 수 순서대로 오름차순으로 정렬해서 출력하라.
SQL> select a.emp_id "사번", a.emp_name "이름",
a.birth_date "생일", count(b.birth_date) "빠른사람수"
from temp a,temp b
where b.birth_date(+) < a.birth_date
group by a.emp_id, a.emp_name, a.birth_date
order by count(b.birth_date);
SQL> select a.emp_id, a.emp_name, a.birth_date,count(b.birth_date)
from temp a, temp b
where a.birth_date > b.birth_date(+)
group by a.emp_id, a.emp_name, a.birth_date
order by 4;
안시조인
|
비 교 | ||
|
오라클 조인 |
안시조인 | |
|
select ~~~ from a , b where ~~~ |
select ~~~ from a join b on ~~~ | |
|
|
| |
|
아우터 조인 비교 | ||
|
오라클 조인 |
안시조인 | |
|
Null 쪽에 (+) |
LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN | |
|
|
예 제 | |
|
|
SELECT e.last_name, e.department_id, d.department_name FROM employees e LEFT OUTER JOIN departments d ON (e.department_id = d.department_id) ; | |
|
|
SELECT e.last_name, e.department_id, d.department_name FROM employees e RIGHT OUTER JOIN departments d ON (e.department_id = d.department_id) ; | |
|
|
SELECT e.last_name, d.department_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.department_id = d.department_id) ; | |
연습문제
1. EQUI JOIN 을 이용하여 학생 이름과 소속 학과 이름을 학과 이름, 학생 이름순
으로 정렬하여 출력하시오.
SQL> select s.name, d.dname
from student s,department d
where s.deptno=d.deptno
order by 1
2. OUTER JOIN을 이용하여 101번 학과에 소속된 학생들의 이름과 지도 교수
이름을 출력 하세요. 이때 지도교수가 배정되지 않은 학생도 함께 출력하세요.
SQL> select s.name "학생", p.name "지도교수"
from student s,professor p
where s.profno = p.profno(+);
3. 공과대학에 소속된 학부와 학과 이름을 출력하세요.
DNAME DEPTNO DNAME
-------------------------------- ---------- --------------------------------
정보미디어학부 100 멀티미디어학과
정보미디어학부 100 컴퓨터공학과
메카트로닉스학부 200 기계공학과
메카트로닉스학부 200 전자공학과
SQL> select b.dname,b.deptno,c.dname
from department a, department b, department c
where b.deptno = c.college
and a.deptno = b.college
and a.deptno =10;
4. 각 학과별로 학과번호 , 학과 이름, 소속 학생 이름, 학생의 지도교수 이름,
지도교수 직급, 지도교수 소속 학과번호 , 지도교수소속 학과명을 출력하세요
(단 지도교수가 없는 학생과 지도 학생이 없는 교수 모두 출력하세요)
결과)
deptno dname name name position deptno dname
------ ---------------- ------- ------- --------- ------ -----
101 컴퓨터공학과 김영균 이만식 부교수 101 컴퓨터공학과
101 컴퓨터공학과 류민정 전은지 전임강사 101 컴퓨터공학과
101 컴퓨터공학과 박미경
101 컴퓨터공학과 서재진
...
19 row
SQL> 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;
DEPTNO DNAME NAME NAME POSITION DEPTNO DNAME
---------- ----------------- --------- --------- ---------- ------- ---------------
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 전자공학과 조명훈
염일웅 전임강사 102 멀티미디어학과
김도훈 교수 101 컴퓨터공학과
남은혁 부교수 202 기계공학과
19 rows selected.
5. 교수 테이블에서 학과번호 ,교수 이름, 교수 별 지도 학생 수 , 학과별 소계학생수,
총 학생수를 출력하세요.
SQL> select p.deptno, p.name, count(*)
2 from professor p, student s
3 where p.profno = s.profno
4 group by rollup(p.deptno,p.name);
DEPTNO NAME COUNT(*)
---------- -------------------- ----------
101 성연희 2
101 이만식 1
101 전은지 3
101 6
102 권혁일 3
102 3
201 이재우 1
201 1
10
9 rows selected.
'Oracle > Oracle - SQL' 카테고리의 다른 글
| SQL정리 8장 DML (0) | 2012.01.10 |
|---|---|
| SQL정리 7장 서브쿼리 (0) | 2012.01.09 |
| SQL정리 5장 그룹함수 (0) | 2012.01.05 |
| SQL정리 4장 함수 (0) | 2012.01.04 |
| SQL정리 1-3장 (0) | 2012.01.04 |
