📚 들어가기
Use Database Management System : ORACLE
Use Tool : DBeaver
테이블 생성 및 데이터 추가
먼저 조인을 알아보기 전에 테스트 실행하면서 테스트 할 테이블과 그에 데이터를 입력했다
CREATE TABLE A(
code char(1),
val NUMBER(1)
);
CREATE TABLE B(
code char(1),
unit char(1)
);
INSERT INTO A VALUES ('A', 1);
INSERT INTO A VALUES ('B', 2);
INSERT INTO A VALUES ('C', 3);
INSERT INTO A VALUES ('D', 4);
INSERT INTO B VALUES ('A', '+');
INSERT INTO B VALUES ('B', '-');
INSERT INTO B VALUES ('C', '*');
INSERT INTO B VALUES ('F', '/');
📌 동일조인
테이블은 다른데 컬럼명이 같으면 앞에 테이블명을 붙여서 구분하여 출력한다
SELECT A.code, A.val, B.code, B.unit
FROM A, B
WHERE A.code = B.code;
📌 내부 조인 (INNER JOIN)
현재 사용하고 있는 데이터베이스인 오라클에도 따로 쉽게 지원하는 INNER JOIN이 있지만 SQL 표준인 아래 형태로 공부하는게 훨신 좋다 다른 데이터베이스로 옮긴다 해도 문제 없이 작동되므로 유연성이 생기기 때문이다
SELECT A.CODE, A.VAL, B.CODE, B.UNIT
FROM A INNER JOIN B ON A.CODE = B.CODE;
📌 자연 조인
A 테이블과 B 테이블이 공통된 컬럼을 기반으로 자동으로 조인하는 방식이다
SELECT * FROM A NATURAL JOIN B;
결과
| A | 1 | + |
| B | 2 | - |
| C | 3 | * |
📌 교차 조인
A 테이블과 B 테이블이 교차로 모든 경우의 수가 조인되는 방식이다
SELECT * FROM A CROSS JOIN B;
SELECT * FROM A ,B;
결과
| A | 1 | A | + |
| A | 1 | B | - |
| A | 1 | C | * |
| A | 1 | F | / |
| B | 2 | A | + |
| B | 2 | B | - |
| B | 2 | C | * |
| ... | ... | ... | ... |
| D | 4 | F | / |
📌 외부 조인
두 테이블을 조인시 한쪽 테이블에 일치하는 행이 없어도 데이터를 포함해서 결과를 반환한다
왼쪽 외부 조인 (LEFT OUTER JOIN), 오른쪽 외부 조인 (RIGHT OUTER JOIN)
LEFT OUTER JOIN
SELECT A.*, B.*
FROM A, B
WHERE A.CODE = B.CODE(+);
SELECT A.*, B.*
FROM A LEFT OUTER JOIN B
ON A.CODE = B.CODE;
SELECT A.*, B.*
FROM A RIGHT OUTER JOIN B
ON A.CODE = B.CODE;
👨🏻💻 활용해보기
학생 관리 프로그램
먼저 NEW_STUDENT 테이블을 만들고 샘플 데이터들을 추가했다
CREATE TABLE NEW_STUDENT(
STD_NO CHAR(8) PRIMARY KEY,
STD_NAME VARCHAR2(30) NOT NULL,
STD_MAJOR VARCHAR2(30),
STD_SCORE NUMBER(3,2) DEFAULT 0 NOT NULL,
STD_GENDER CHAR(1)
);
학적 관리 프로그램으로 예를 들어보자
학적관리 프로그램 학생, 교수, 과목이 있음, 학생은 특정 과목을 수강, 해당 과목을 담당하는 교수가 있음, 해당 과목은 수강인원 제한 인원은 과목마다 다르다.
학생은 데이터로 학번, 이름, 학과, 평점, 연락처, 이메일을 가지고 있다
교수는 교수번호, 교수명, 학과, 연락처, 이메일, 과목은 과목번호, 과목명, 담당교수, 수강가능인원 데이터를 가지고 있다
수강 정보는 수강 순번, 과목, 학생, 수강 신청일시를 가지고 있다
학생 : 학생번호, 이름, 학과번호, 평점, 연락처, 이메일
교수 : 교수번호, 이름, 학과번호, 연락처, 이메일
학과번호 : 학과번호, 학과명
과목 : 과목번호, 교수번호, 과목명, 수강제한인원
수강 : 순번, 과목정보, 학생번호, 수강신청날짜
EDR을 만들어서 중복되는 데이터들은 정규형 시켰다
이제 SQL문에 적용시켜서 처음 만들었던 STUDENT 테이블에서 학과 정보를 분리시켰다
SELECT STD_MAJOR ,COUNT(*) FROM NEW_STUDENT GROUP BY STD_MAJOR ; -- 만든 샘플 데이터에 학과가 몇개 있는지 확인
CREATE TABLE MAJOR(
MAJOR_NO CHAR(2) PRIMARY KEY,
MAJOR_NAME VARCHAR2(30)
)
-- 학과번호는 임의로 삽입했다
INSERT INTO MAJOR VALUES ('U7', '신소재 공학');
INSERT INTO MAJOR VALUES ('C2', '의학');
INSERT INTO MAJOR VALUES ('O6', '컴퓨터 공학');
INSERT INTO MAJOR VALUES ('G1', '화학 공학');
INSERT INTO MAJOR VALUES ('M5', '미술학');
INSERT INTO MAJOR VALUES ('M1', '경영학');
INSERT INTO MAJOR VALUES ('R2', '심리학');학생 테이블에 학과번호 컬럼을 추가하고 학과명 컬럼을 삭제했다
학과 번호 컬럼에 데이터들도 추가해줬다
원래는 외래키를 사용해서 종속해줘야 하지만 조인을 공부하니깐 우선 넘어간다
-- 학생 테이블에 학과번호 컬럼을 추가
ALTER TABLE NEW_STUDENT ADD MAJOR_NO CHAR(2);
UPDATE NEW_STUDENT SET MAJOR_NO =
(SELECT MAJOR_NO FROM MAJOR WHERE MAJOR_NAME LIKE STD_MAJOR);
-- 학생 테이블에 학과명 컬럼을 삭제
ALTER TABLE NEW_STUDENT DROP COLUMN STD_MAJOR;
📌 조인 출력
학번, 이름, 학과명, 평점 조회
SELECT ns.STD_NO , ns.STD_NAME, m.MAJOR_NO ,ns.STD_SCORE
FROM NEW_STUDENT ns, MAJOR m
WHERE ns.MAJOR_NO = m.MAJOR_NO;20221102 황지우 U7 3.02
20221166 정다연 U7 4
20221208 강시우 C2 1.3
20221293 박예림 O6 4.43
20221362 황가윤 U7 3.7
20221374 박지후 G1 4.07
20221669 박지은 G1 2.25
20221886 박지유 U7 1.61
20221958 강예은 M5 3.94
20221976 최민서 M1 0.04
...
장학금 테이블 생성
장학금을 받는 학생 테이블 생성해서 테이블 3개로 해보자
마찬가지로 테이블을 생성하고 NEW_STUDENT 테이블에 학번 컬럼에 있는 샘플 데이터에서 랜덤으로 몇명의 데이터를 가져와서 장학금을 받는 학생으로 선정하였다
-- 장학금 테이블
CREATE TABLE STUDENT_SCHOLARSHIP(
SCHOLARSHIP_NO NUMBER,
STD_NO CHAR(8),
MONEY NUMBER
);
장학금을 받는 학생의 학번, 이름, 장학금 금액 조회
SELECT ns.STD_NO ,ns.STD_NAME , ss.MONEY
FROM NEW_STUDENT ns , STUDENT_SCHOLARSHIP ss
WHERE ns.STD_NO = ss.STD_NO ;20221102 황지우 342
20221293 박예림 262
20221374 박지후 352
20221958 강예은 211
20221980 임다온 349
20222100 한시연 211
20222536 이가연 360
20223147 김시현 168
20223151 강서아 329
20223774 이하은 198
...
장학금을 받는 학생의 학번, 이름, 학과명, 장학금 금액 조회
SELECT ns.STD_NO ,ns.STD_NAME , MAJOR_NAME ,MONEY
FROM NEW_STUDENT ns , STUDENT_SCHOLARSHIP ss, MAJOR m
WHERE ns.STD_NO = ss.STD_NO AND ns.MAJOR_NO = m.MAJOR_NO ;황지우 20221102 신소재 공학 342
김시현 20223147 신소재 공학 168
이하은 20223774 신소재 공학 198
조가은 20226123 신소재 공학 378
황하린 20228479 신소재 공학 337
한시연 20222100 의학 211
정가현 20223893 의학 361
이하늘 20224998 의학 370
김예나 20227445 의학 142
박예림 20221293 컴퓨터 공학 262
...
학과 데이터에 2개만 추가 (INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN)
INSERT INTO MAJOR VALUES ('A9', '국어국문학과');
INSERT INTO MAJOR VALUES ('B9', '회계학과');학번, 이름, 학과 번호, 평점, 학과번호, 학과명 모든 컬럼 다 출력
SELECT s.*, m.*
FROM NEW_STUDENT s INNER JOIN MAJOR m
ON s.MAJOR_NO = m.MAJOR_NO ;
NEW_STUDNET 테이블에 해당하는 학생이 없기 떄문에 학생 데이터에서 추가한 국어국문학과와 회계학과는 찾아도 보이지 않는다
SELECT s.*, m.*
FROM NEW_STUDENT s LEFT OUTER JOIN MAJOR m
ON s.MAJOR_NO = m.MAJOR_NO ;
SELECT s.*, m.*
FROM NEW_STUDENT s RIGHT OUTER JOIN MAJOR m
ON s.MAJOR_NO = m.MAJOR_NO ;20247887 이가연 0.65 M R2 R2 심리학
20248469 강하윤 3.96 M U7 U7 신소재 공학
20248640 강다연 0.58 F M1 M1 경영학
20249221 박서영 1.15 F R2 R2 심리학
20249539 이하람 3.97 F U7 U7 신소재 공학
B9 회계학과
A9 국어국문학과
LEFT OUTER JOIN을 입력했을 떄 INNER JOIN과 별 다른것 없이 출력된다
RIGHT OUTER JOIN으로 키워드를 바꿨을 때는 위에서 추가한 국어국문학과와, 회계학과까지 출력된다
LEFT OUTER JOIN은 왼쪽 테이블의 모든 행을 유지하고, 오른쪽 테이블의 해당하는 행이 없는 경우 NULL 값을 채워주는 조인이며, RIGHT OUTER JOIN은 오른쪽 테이블의 모든 행을 유지하고, 왼쪽 테이블의 해당하는 행이 없는 경우 NULL 값을 채워주는 조인이다
학과 테이블에서 학생 테이블에서 사용되지 않은 데이터를 조회
SELECT m.*
FROM NEW_STUDENT s RIGHT OUTER JOIN MAJOR m
ON S.MAJOR_NO =M.MAJOR_NO
WHERE S.STD_NO IS NULL;B9 회계학과
A9 국어국문학과
장학금을 못받은 학생 정보만 조회
SELECT S.STD_NO, S.STD_NAME, M.MAJOR_NAME, S.STD_SCORE, SS.MONEY
FROM NEW_STUDENT S LEFT OUTER JOIN STUDENT_SCHOLARSHIP SS
ON S.STD_NO = SS.STD_NO LEFT OUTER JOIN MAJOR m
ON S.MAJOR_NO = M.MAJOR_NO
WHERE SS.SCHOLARSHIP_NO IS NULL;20221102 황지우 신소재 공학 3.02 342
20223147 김시현 신소재 공학 4.14 168
20223774 이하은 신소재 공학 3.87 198
20226123 조가은 신소재 공학 3.47 378
20228479 황하린 신소재 공학 4.08 337
20222100 한시연 의학 3.35 211
20223893 정가현 의학 3.96 361
20224998 이하늘 의학 3.41 370
20227445 김예나 의학 2.61 142
20221293 박예림 컴퓨터 공학 4.43 262
20221980 임다온 컴퓨터 공학 3.2 349
20223151 강서아 컴퓨터 공학 3.88 329
20221374 박지후 화학 공학 4.07 352
20222536 이가연 화학 공학 4.37 360
20224159 임예진 화학 공학 3.66 171
20221958 강예은 미술학 3.94 211
20225708 강서윤 미술학 3.72 275
20226450 이다경 미술학 3.39 105
20226688 조서아 경영학 3.72 197
20227025 이서지 경영학 2.93 314
20228570 최하림 경영학 4.33 295
20224615 김서하 심리학 4.37 174
20225459 임가현 심리학 3.87 120
학과별로 장학금을 받은 학생들의 (학과별, 성별), 인원수, 최대평점, 최저평점 조회
SELECT M.MAJOR_NAME, S.STD_GENDER, COUNT(*) AS SCHOLARSHIP_COUNT, MAX(S.STD_SCORE), MIN(S.STD_SCORE)
FROM NEW_STUDENT S LEFT OUTER JOIN STUDENT_SCHOLARSHIP SS
ON S.STD_NO = SS.STD_NO
INNER JOIN MAJOR m
ON S.MAJOR_NO = M.MAJOR_NO
WHERE SS.SCHOLARSHIP_NO IS NOT NULL
GROUP BY M.MAJOR_NAME, S.STD_GENDER;신소재 공학 F 1 3.02 3.02
신소재 공학 M 4 4.14 3.47
의학 F 2 3.35 2.61
의학 M 2 3.96 3.41
컴퓨터 공학 M 2 4.43 3.88
컴퓨터 공학 F 1 3.2 3.2
화학 공학 M 2 4.07 3.66
화학 공학 F 1 4.37 4.37
미술학 F 3 3.94 3.39
경영학 F 3 4.33 2.93
심리학 F 1 4.37 4.37
심리학 M 1 3.87 3.87
'SQL' 카테고리의 다른 글
| [SQL] 서브 쿼리 (SubQuery) (0) | 2024.04.12 |
|---|