03 - 1 JOIN (부모 테이블, 자식 테이블 , Foreign Key)
우리에게 이런 프렌즈 회사 직원 데이터가 있다고 가정해보자.
| ID | hr_name | dept_id | dept_name | dept_number |
| 100 | 모니카 | 1 | 기획부 | 203 |
| 101 | 피비 | 1 | 기획부 | 203 |
| 102 | 챈들러 | 1 | 기획부 | 203 |
소속부서, 부서 이름, 내선 이 데이터는 계속 중복이 된다.
뭔가 따로 보관하는게 좋을 것 같다는 생각이 들지 않나? (안 들어도 그렇게 든다고 Chi자)
혹시나 내선 번호가 바뀐다던가 부서 이름은 바뀐다던가 할 때,
지금 상태에선 모든 열마다 데이터를 바꿔야 하지만 부서 정보를 따로 보관하면 하나만 수정 하면 될 것이다. 바로 요렇게!
| ID | hr_name | dept_id |
| 100 | 모니카 | 1 |
| 101 | 피비 | 1 |
| 102 | 챈들러 | 1 |
| dept_id | dept_name | dept_number |
| 1 | 기획부 | 203 |
| 2 | 인사부 | 204 |
| 3 | 개발부 | 205 |
이렇게 데이터를 인사 정보 테이블과 부서 정보 테이블 두개로 나눠서 저장했다.
그럼 중복 되는 정보를 적을 필요 없이, 소속부서 ID만 넣어주면 된다는 것!
근데 갑자기 우리가 모니카👩 부서 이름이 알고 싶다고 해보자!
그럼 인사 정보 테이블에서 모니카의 부서 이름 꺼내고 , 부서 정보 테이블에서 부서 ID에 맞는 이름 꺼내고 두개의 쿼리를 써야하는 불편함이 있다.
❓근데 그럴 필요 없이 한개의 쿼리문으로 처리할 수 있는 방법이 있다면❓
❓❓ 그것이 바로 Join이다 (그렇다 지금까지 Join을 말하기 위한 빌덥)
오늘은 Join 배워보자쿠-
01 Join 정의
table을 합치는 것! 그래서 연관성 있는 table들을 한개의 쿼리로 select 가능하게 하는 것이 join
👉 table을 합쳐서 SELECT 할 때만 사용할 가상의/임시 table 만드는 것
02 테이블간의 관계
인사 정보 테이블(hr)은 소속 부서 정보는 부서 정보 테이블(dept)에 있다. 즉, hr은 dept 테이블을 참조 하고 있는 관계
참조 하는 테이블을 자식테이블, 참조 당하는(?) 테이블을 부모테이블로 이해하면 편함 (아낌 없이 주는 것이..바로 부모님 🥲)
hr - 자식테이블
| ID | hr_name | dept_id |
| 100 | 모니카 | 1 |
| 101 | 피비 | 1 |
| 102 | 챈들러 | 1 |
dept - 부모테이블
| dept_id | dept_name | dept_number |
| 1 | 기획부 | 203 |
| 2 | 인사부 | 204 |
| 3 | 개발부 | 205 |
03 Foreign Key 제약조건
이렇게 부모 - 자식 간의 참조 관계 테이블을 만들 때에 필요한 제약조건들이 있다.그거슨 바로 Foreign Key 제약 조건
👉 Foreign Key(FK)
부모 테이블의 Primary Key 컬럼의 값들만 가질 수 있다는 것!
따라서 부모 테이블의 Primary Key 컬럼의 타입과 동일하게 설정해야만 함.ex) create table 해서 각 컬럼별 타입 생성할 때 부모 테이블의 PK가 VARCHAR(30) 이었다면 자식테이블의 FK도 VARCHAR(30)로 설정해야 한다는 것
04 Foreign Key 제약 구문
제약 조건 구문 작성하는 방법에 대해서 배워boza!
👉FK 제약조건 기본 구문
CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERENCES 부모테이블(PK컬럼) [ON 설정]이렇게 적어보면 1개도 2해가 안된다.
하나씩 뜯어보자
1. 제약조건이름 : 이름은 맘대로 써도 되지만 조건마다 암묵적 룰이 있는데 아래 처럼 작성해주면 나중에 알아먹기 쉬움!
제약조건타입_자식_부모 ex) 위에 테이블로 대입하면 fk_hr_dept
2. FOREIGN KEY(컬럼명)
참조하는 자식테이블의 컬럼명을 적어준다 ex) FOREIGN KEY(dept_id)
3. 부모테이블(PK컬럼)
부모 테이블 이름과 부모 테이블의 참조되는 Primary Key 컬럼명을 적어준다 ex) dept(dept_id)
4. ON 설정은 05에서 설명 ㄱㄱ
05 ON 설정
기본적으로 자식테이블에서 참조하고 있으면 부모 테이블은 데이터를 삭제 or 수정하지 못함
but, Foreign Key 컬럼 설정할 때 이것을 변경할 수 있는데 이 때 on 설정 사용
정리하자면 부모 테이블 컬럼이 삭제 되었을때 자식테이블 컬럼은 어떻게 할지 정해주는 것이 on 설정
- ON DELETE: 참조하는 부모 테이블의 행이 삭제 되었을 때 어떻게 처리 할 것인지 설정
- ON UPDATE: 참조하는 부모 테이블의 참조 컬럼값이 변경되면 어떻게 처리할 것인지 설정
처리 방식은 CASCADE와 SET NULL이 있다.
- CASCADE: 부모 테이블에서 데이터 삭제/수정하면, 그 행을 참조하는 자식테이블의 행들도 같이 삭제/수정
CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERENCES 부모테이블(PK컬럼) ON DELETE CASCADE- SET NULL: 부모 테이블에서 데이터 삭제/수정하면, 그 행을 참조하는 자식테이블의 foreign key 컬럼의 값을 NULL로 변경
CONSTRAINT 제약조건이름 FOREIGN KEY(컬럼) REFERENCES 부모테이블(PK컬럼) ON DELETE SET NULL
06 외래키 제약조건 제거
부모 테이블은 자식 테이블 지우기 전까지 참조 관계가 있어서 삭제 불가.
그 참조 관계를 잠깐 꺼줘서 삭제 가능하게 만드는 것! (언제 필요할지 모르니 일단 알아듀자)
set foreign_key_checks = 0; -- foreign key 해제 설정
DROP table 삭제할테이블;
set foreign_key_checks = 1; -- foreign key 적용 설정
07 조인 종류
이제 부모 -자식 테이블 관계를 배워보았으니 JOIN을 배워보자!
JOIN은 관계있는 두개의 테이블을 합쳐서 조회(SELECT) 가능하게 하는 것이다.
정보가 흩어져 있어 필요한 정보를 찾을 때 n개의 쿼리를 써야 할 때, 합쳐서 한개의 쿼리문만으로 ssap 가능하게 하는것!
- Inner Join: 양쪽 테이블에서 조인 조건 만족하는 행만 합치기
- Outer Join: 한 쪽 행은 모두 사용, 다른 한 쪽은 조건 만족하는 행만 합치기
- Cross Join: 두 테이블의 곱집합을 반환(가능한 조합 모두 만드는 것, 쓸 일 거의 없음)
Join 알아보기 전 빌드업을 해보고자
부모 테이블 자식 테이블 간의 참조 관계를 배워보아따.
본격적인 Join은 이제부터 시작이라koo!