<6> 조인 (***)
- 하나의 테이블로는 원하는 컬럼정보를 얻어올 수 없을때
관련된 테이블을 논리적으로 결합하여 컬럼정보를 얻어오는
방법
1) EQUI 조인 (90% 이상 EQUI 조인 사용됨)
- 가장 일반적인 조인. WHERE절에 공통컬럼들이 동등연산자(=)에
의해 비교되어 결합되는 조인
형식)
SELECT 컬럼명1,컬럼명2,..
FROM 테이블명1,테이블명2
WHERE 조인조건;
예1) 사원번호,이름,급여,부서번호,부서명을 조회하시오.
SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME --중복되는 컬럼은 해당 컬럼이 어떤 테이블에 속하는 지 명시
FROM EMP E, DEPT D
WHERE E.DEPTNO=D.DEPTNO;
EMPNO ENAME SAL DEPTNO DNAME
---------- -------------------- ---------- ---------- ----------------------------
7369 SMITH 800 20 RESEARCH
7499 ALLEN 1600 30 SALES
7521 WARD 1250 30 SALES
7566 JONES 2975 20 RESEARCH
7654 MARTIN 1250 30 SALES
7698 BLAKE 2850 30 SALES
7782 CLARK 2450 10 ACCOUNTING
7839 KING 5000 10 ACCOUNTING
7844 TURNER 1500 30 SALES
7900 JAMES 950 30 SALES
7902 FORD 3000 20 RESEARCH
EMPNO ENAME SAL DEPTNO DNAME
---------- -------------------- ---------- ---------- ----------------------------
7934 MILLER 1300 10 ACCOUNTING
예2) 30번 부서 사원들의 사원번호,이름,부서명,직업을 조회하시오.
SELECT EMPNO, ENAME, D.DNAME, JOB
FROM EMP E, DEPT D
WHERE E.DEPTNO=30 AND E.DEPTNO=D.DEPTNO;
EMPNO ENAME DNAME JOB
---------- -------------------- ---------------------------- ------------------
7499 ALLEN SALES SALESMAN
7521 WARD SALES SALESMAN
7654 MARTIN SALES SALESMAN
7698 BLAKE SALES MANAGER
7844 TURNER SALES SALESMAN
7900 JAMES SALES CLERK
Q1) 부서명이 'ACCOUNTING'에 근무하는 사원들의 이름,급여,
입사일,부서명,부서위치를 조회하시오.
SELECT ENAME, SAL, HIREDATE, D.DNAME, D.LOC
FROM EMP E, DEPT D
WHERE D.DNAME='ACCOUNTING' AND E.DEPTNO=D.DEPTNO;
ENAME SAL HIREDATE DNAME LOC
-------------------- ---------- -------- ---------------------------- --------------------------
CLARK 2450 81/06/09 ACCOUNTING NEW YORK
KING 5000 81/11/17 ACCOUNTING NEW YORK
MILLER 1300 82/01/23 ACCOUNTING NEW YORK
Q2) 전체 사원의 평균급여보다 많은 사원의 사원번호,이름,
급여,부서명 조회하기
SELECT EMPNO, ENAME, SAL, D.DNAME
FROM EMP E, DEPT D
WHERE SAL>(SELECT AVG(SAL) FROM EMP) AND E.DEPTNO=D.DEPTNO;
EMPNO ENAME SAL DNAME
---------- -------------------- ---------- -----------
7566 JONES 2975 RESEARCH
7698 BLAKE 2850 SALES
7782 CLARK 2450 ACCOUNTING
7839 KING 5000 ACCOUNTING
7902 FORD 3000 RESEARCH
예3)
# 학생정보테이블[학생번호(PK), 이름, 전화번호] <부모테이블> //**PK는 NULL, 중복 값 허용 X**
CREATE TABLE STUDENT
(
SNUM NUMBER(10) PRIMARY KEY, -- ()안에 숫자는 BYTE수 나타냄
NAME VARCHAR2(20), -- VARCHAR2 ; JAVA의 STRING과 동일
PHONE VARCHAR2(20)
);
# 성적테이블[성적번호(PK),학생번호(FK),과목명,점수] --FK(Foreign Key) ; 외래번호 <자식테이블>
CREATE TABLE GRADE
(
GNUM NUMBER(10) PRIMARY KEY,
SNUM NUMBER(10) REFERENCES STUDENT(SNUM), --SNUM에 없는 KEY값은 넣을 수 없음
SUBJECT VARCHAR2(10),
SCORE NUMBER(3)
);
-STUDENT 테이블에 3명의 학생정보 추가하기 (INSERT 구문)
SQL> INSERT INTO STUDENT(SNUM,NAME,PHONE)
2 VALUES(1,'DASEUL','01048276933');
1 row created.
SQL> INSERT INTO STUDENT(SNUM,NAME,PHONE)
2 VALUES(2,'JONGHYUN','01058201932');
1 row created.
SQL> INSERT INTO STUDENT(SNUM,NAME,PHONE)
2 VALUES(3,'YOUNGJUNE','01028376012');
1 row created.
SQL> SELECT * FROM STUDENT;
SNUM NAME PHONE
---------- ---------------------------------------- ---------------
1 DASEUL 01048276933
2 JONGHYUN 01058201932
3 YOUNGJUNE 01028376012
-GRADE 테이블에 과목점수 추가하기 -- INSERT 생성 시 칼럼명 생략 가능
SQL> INSERT INTO GRADE
2
SQL> INSERT INTO GRADE VALUES(1,1,'JAVA',85);
1 row created.
SQL> INSERT INTO GRADE VALUES(2,2,'JAVA',100);
1 row created.
SQL> INSERT INTO GRADE VALUES(3,3,'JAVA',97);
1 row created.
SQL> SELECT * FROM GRADE;
GNUM SNUM SUBJECT SCORE
---------- ---------- -------------------- -----------------
1 1 JAVA 85
2 2 JAVA 100
3 3 JAVA 97
Q1) 모든 학생번호,이름, 과목명, 점수를 조회해 보세요.
SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME --중복되는 컬럼은 해당 컬럼이 어떤 테이블에 속하는 지 명시
FROM EMP E, DEPT D
WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=G.SNUM;
SNUM NAME SUBJECT SCORE
---------- ---------------------------------------- -------------------- ----------
1 DASEUL JAVA 85
2 JONGHYUN JAVA 100
3 YOUNGJUNE JAVA 97
1 DASEUL JAVA+ 97
Q2) 1번 학생의 이름, 전화번호, 성적번호, 과목명, 점수를 조회해 보세요.
SELECT S.NAME, S.PHONE, G.GNUM, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=1 AND S.SNUM=G.SNUM;
NAME PHONE GNUM SUBJECT SCORE
---------------------------------------- ---------------------------------------- ---------- -------------------- ------
DASEUL 01048276933 1 JAVA 85
DASEUL 01048276933 4 JAVA+ 97
2) NON-EQUI 조인
- 공통컬럼이 존재하지 않는 경우에도 조인이 가능하다.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
GRADE TABLE
SALGRADE TABLE
STUDENT TABLE
SQL> SELECT * FROM SALGRADE;
GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
예1) 사원번호,이름,급여,급여등급(GRADE)를 조회하시오.
SELECT E.EMPNO, E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL>=S.LOSAL AND E.SAL<=S.HISAL;
EMPNO ENAME SAL GRADE
---------- -------------------- ---------- ----------
7369 SMITH 800 1
7900 JAMES 950 1
7521 WARD 1250 2
7654 MARTIN 1250 2
7934 MILLER 1300 2
7499 ALLEN 1600 3
7844 TURNER 1500 3
7566 JONES 2975 4
7698 BLAKE 2850 4
7782 CLARK 2450 4
7902 FORD 3000 4
EMPNO ENAME SAL GRADE
---------- -------------------- ---------- ----------
7839 KING 5000 5
3) CROSS조인 (--자주 사용되지 않음)
- 2개이상의 테이블에서 조인될때 조인조건에 의해 결합이 발생되지 않는 경우.
(실수로 조인결합조건을 안 쓴 경우가 대부분 또는 테스트용데이터를 한번에 --조회되는 검색 속도 측정시 사용
많이 만들고자 할때 사용되는 경우가 있음)
SELECT E.EMPNO, ENAME, D.DNAME
FROM EMP E,DEPT D;
--조인조건 생략된 상태
EMPNO ENAME DNAME
---------- -------------------- ----------------------------
7369 SMITH ACCOUNTING
7499 ALLEN ACCOUNTING
7521 WARD ACCOUNTING
7566 JONES ACCOUNTING
7654 MARTIN ACCOUNTING
7698 BLAKE ACCOUNTING
7782 CLARK ACCOUNTING
7839 KING ACCOUNTING
7844 TURNER ACCOUNTING
7900 JAMES ACCOUNTING
7902 FORD ACCOUNTING
EMPNO ENAME DNAME
---------- -------------------- ----------------------------
7934 MILLER ACCOUNTING
7369 SMITH RESEARCH
7499 ALLEN RESEARCH
7521 WARD RESEARCH
7566 JONES RESEARCH
7654 MARTIN RESEARCH
7698 BLAKE RESEARCH
.
.
.
48 rows selected.
4) SELF 조인
- 참조해야 하는 컬럼이 자신의 테이블의 다른 컬럼인 경우 사용되는 조인
예) 모든 사원의 사원이름,매니져이름을 조회하시오.
SELECT E1.ENAME "사원", E2.ENAME "매니저"
FROM EMP E1, EMP E2 --Alias로 구문
WHERE E1.MGR=E2.EMPNO; --MGR, EMPNO는 동일한 숫자들을 공유.
사원 매니저
-------------------- --------------
FORD JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
11 rows selected.
5) OUTER 조인 (없는데이터를 추출할 때 성능저하 발생하므로 자주 사용되지 않음)
- 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른쪽 테이블에는 데이터가
존재하지 않는 경우 모든 데이터를 추출하는 조인방법
예1) 모든 부서번호와 부서명,근무사원이름을 조회하시오.
SELECT D.DEPTNO, D.DNAME, E.ENAME
FROM DEPT D, EMP E
WHERE D.DEPTNO=E.DEPTNO;
DEPTNO DNAME ENAME
---------- ---------------------------- --------------------
20 RESEARCH SMITH
30 SALES ALLEN
30 SALES WARD
20 RESEARCH JONES
30 SALES MARTIN
30 SALES BLAKE
10 ACCOUNTING CLARK
10 ACCOUNTING KING
30 SALES TURNER
30 SALES JAMES
20 RESEARCH FORD
DEPTNO DNAME ENAME
---------- ---------------------------- --------------------
10 ACCOUNTING MILLER
12 rows selected.
SQL> SELECT D.DEPTNO, D.DNAME, E.ENAME
2 FROM DEPT D, EMP E
3 WHERE D.DEPTNO=E.DEPTNO(+);
DEPTNO DNAME ENAME
---------- ---------------------------- --------------------
20 RESEARCH SMITH
30 SALES ALLEN
30 SALES WARD
20 RESEARCH JONES
30 SALES MARTIN
30 SALES BLAKE
10 ACCOUNTING CLARK
10 ACCOUNTING KING
30 SALES TURNER
30 SALES JAMES
20 RESEARCH FORD
DEPTNO DNAME ENAME
---------- ---------------------------- --------------------
10 ACCOUNTING MILLER
40 OPERATIONS
Q1) 모든 부서번호,부서명,사원이름,급여를 조회하시오.(OUTER조인)
SELECT D.DEPTNO,D.DNAME,E.ENAME,E.SAL
FROM DEPT D,EMP E
WHERE D.DEPTNO=E.DEPTNO(+)
ORDER BY D.DEPTNO;
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
10 ACCOUNTING CLARK 2450
10 ACCOUNTING MILLER 1300
10 ACCOUNTING KING 5000
20 RESEARCH JONES 2975
20 RESEARCH FORD 3000
20 RESEARCH SMITH 800
30 SALES ALLEN 1600
30 SALES TURNER 1500
30 SALES JAMES 950
30 SALES WARD 1250
30 SALES BLAKE 2850
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
30 SALES MARTIN 1250
40 OPERATIONS
Q2) 모든 학생번호, 이름, 과목명, 점수를 출력해 보세요. (OUTER조인)
DELETE FROM GRADE WHERE SNUM=3;
COMMIT;
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S, GRADE G
WHERE S.SNUM=G.SNUM(+);
SNUM NAME SUBJECT SCORE
---------- ---------------------------------------- -------------------- ----------
1 DASEUL JAVA 85
2 JONGHYUN JAVA 100
1 DASEUL ORACLE 0
3 YOUNGJUNE
Q3) EMP테이블에서 사원번호,이름,매니져번호,매니져이름,매니져직업을
조회해 보세요.
SELECT E1.EMPNO "사원번호", E1.ENAME "이름", E2.ENAME "매니저이름", E2.JOB "매니저직업"
FROM EMP E1, EMP E2
WHERE E1.MGR=E2.EMPNO;
사원번호 이름 매니저이름 매니저직업
---------- -------------------- -------------------- ------------------
7902 FORD JONES MANAGER
7900 JAMES BLAKE MANAGER
7844 TURNER BLAKE MANAGER
7654 MARTIN BLAKE MANAGER
7521 WARD BLAKE MANAGER
7499 ALLEN BLAKE MANAGER
7934 MILLER CLARK MANAGER
7782 CLARK KING PRESIDENT
7698 BLAKE KING PRESIDENT
7566 JONES KING PRESIDENT
7369 SMITH FORD ANALYST
<<여기까지는 오라클 표준>>
==================================================================
## ANSI 조인 : SQL의 국제적 표준
1) [INNER] JOIN
형식)
SELECT 컬럼명1,..
FROM 테이블1 [INNER] JOIN 테이블2
ON 조인조건절
[WHERE절]
예) 사원번호,이름,부서명,부서위치를 조회하시오.
SELECT E.EMPNO, E.ENAME, D.DNAME, D.LOC
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
EMPNO ENAME DNAME LOC
---------- -------------------- ---------------------------- -----------------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
EMPNO ENAME DNAME LOC
---------- -------------------- ---------------------------- -----------------------
7934 MILLER ACCOUNTING NEW YORK
2) OUTER JOIN
형식)
SELECT 컬럼명,..
FROM 테이블1 LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN 테이블2
ON 조인조건절
[WHERE]
예1) 모든 부서번호,부서명,근무사원이름,급여조회
SELECT D.DEPTNO, D.DNAME, E.ENAME, E.SAL
FROM DEPT D LEFT OUTER JOIN EMP E
ON D.DEPTNO=E.DEPTNO;
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
20 RESEARCH SMITH 800
30 SALES ALLEN 1600
30 SALES WARD 1250
20 RESEARCH JONES 2975
30 SALES MARTIN 1250
30 SALES BLAKE 2850
10 ACCOUNTING CLARK 2450
10 ACCOUNTING KING 5000
30 SALES TURNER 1500
30 SALES JAMES 950
20 RESEARCH FORD 3000
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
10 ACCOUNTING MILLER 1300
40 OPERATIONS
--아래도 동일함
SELECT D.DEPTNO, D.DNAME, E.ENAME, E.SAL
FROM EMP E RIGHT OUTER JOIN DEPT D
ON D.DEPTNO=E.DEPTNO;
Q1) 부서번호,부서명,이름,급여를 조회해 보세요.
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO;
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
20 RESEARCH SMITH 800
30 SALES ALLEN 1600
30 SALES WARD 1250
20 RESEARCH JONES 2975
30 SALES MARTIN 1250
30 SALES BLAKE 2850
10 ACCOUNTING CLARK 2450
10 ACCOUNTING KING 5000
30 SALES TURNER 1500
30 SALES JAMES 950
20 RESEARCH FORD 3000
DEPTNO DNAME ENAME SAL
---------- ---------------------------- -------------------- ----------
10 ACCOUNTING MILLER 1300
Q2) 사원이름 'ALLEN'인 사원의 사원번호,이름,급여,부서명을 조회하시오.
SELECT E.EMPNO, E.ENAME, E.SAL, D.DNAME
FROM EMP E INNER JOIN DEPT D
ON E.DEPTNO=D.DEPTNO
WHERE ENAME='ALLEN';
EMPNO ENAME SAL DNAME
---------- -------------------- ---------- -------------------
7499 ALLEN 1600 SALES
Q3) 모든 학생의 학생번호, 이름, 과목명, 점수조회 해보세요. (OUTER JOIN)
SELECT S.SNUM, S.NAME, G.SUBJECT, G.SCORE
FROM STUDENT S LEFT OUTER JOIN GRADE G
ON S.SNUM = G.SNUM;
SNUM NAME SUBJECT SCORE
---------- ---------------------------------------- -------------------- ----------
1 DASEUL JAVA 85
2 JONGHYUN JAVA 100
1 DASEUL ORACLE 0
3 YOUNGJUNE
(8) UNION,UNION ALL
- 두 집합의 결과를 더함
- UNION : 중복데이터를 제거(중복값을 제거하기 위해 정렬해서 수행하므로 성능이
느려진다)
- UNION ALL : 중복데이터를 포함
- 규칙 : 두 집합의 SELECT절에 오는 컬럼의 갯수와 타입이 동일해야 하며 컬럼명은
달라도 상관없다.
28일차 오라클(JOIN/EQUI JOIN/NON-EQUI JOIN/CROSS JOIN/SELF JOIN/OUTER JOIN/ANSI JOIN/UNION,UNION ALL)
2020. 3. 10. 18:00
퀴즈1.zip
0.02MB
퀴즈2.zip
0.02MB
퀴즈(1)_20200310.txt
0.00MB
퀴즈(2)_20200310.txt
0.00MB
퀴즈_20200310.txt
0.01MB
메모_20200310.txt
0.00MB
오라클_4.txt
0.02MB