[DB] 8. 조인(JOIN)

1. 연관 관계

1. 연관 관계에 따라 테이블을 분리

• 두 테이블 간의 데이터 관계

• 예) 인스타그램 유저 - 피드
• 유저는 피드를 여러 개 작성 가능(N)
• 피드는 한 사람에 의해서 작성됨(1)
• 즉, 유저와 피드는 1 : N 관계(일대다)
• 참조하는 쪽이 외래 키를 가지고 있어야 검색 속도가 빠름(random access)
• 피드 쪽이 유저를 참조하는게 이상적인 상황
• N 관계를 가지는 쪽이 foreign key를 가지고 드라이브를 하는 것(앤포드)
• 피드 테이블에 유저 테이블의 primary key를 참조하는 foreign key를 만들어함
 

• 예) 인스타그램 피드 - 댓글
• 피드에는 여러 개의 댓글이 작성될 수 있음(N)
• 댓글은 하나의 피드에 존재함(1)
• 즉, 피드와 댓글의 관계는 1 : N 관계(일대다)
• N관계인 쪽이 참조키를 가져야 함
• 댓글 테이블에 피드 테이블의 기본키를 참조하는 참조키를 만들어야 함

• 예) 쇼핑몰 유저 - 상품
• 유저는 상품을 여러 개 구매 가능(N)
• 상품은 여러 유저에게 판매 가능(N)
• 즉, 유저와 상품의 관계는 N : N 관계(다대다)
• N : N의 관계에서는 중간에 중계를 해주는 테이블이 필요함
• 개체와 개체 간의 관계이기 때문에 행위의 의미를 지닌 중계 테이블을 생성함(구매)
• 행위의 결과를 구매 테이블에 작성함
• 구매 테이블에 생성된 데이터는 개체의 데이터도 변화 시킴
• 이 모든 과정을 트랜잭션이라 함



❗
유저가 상품을 구매할 때 발생되는 트랜잭션
1. 유저가 상품을 구매 요청함
2. 서버에서 해당 상품의 재고를 확인함 [유효성 검사]
1. 재고가 있다면 구매 테이블에 추가
2. 재고가 없다면 유저에게 에러를 반환
3. 주문 처리
1. 현재 상품의 상태를 확인(재고)
2. 현재 재고 값 확인 50
3. 구매량 확인 3
4. 50 - 3 = 47 값을 임시 저장
5. 상품 테이블의 재고 값을 47로 update
1. 47값을 저장하고 있으면 주문 처리 시 오류가 발생했을 때 이 47값을 다시 찾아서 update를 요청함
2. 만약 47이라는 값이 아니라 update할 때 마다 50 - 3 을 사용한다면, 오류가 발생했을 때 47 - 3 이 될 수도 있음

2. 테이블을 분리 하지 않는다면?

• 유저 테이블, 피드 테이블로 나눠 정보를 담을 때
• 데이터 중복을 제거
• 변화가 발생하면 시간이 짧게 걸림
• 그러나, 단순한 읽기 작업이 많을 경우
• 피드 테이블 → 유저 테이블 처럼 2번에 걸쳐 데이터를 확인 해야 한다
• 시간이 오래 걸릴 수 있다

• 유저 테이블에 피드 정보를 담았을 때
• 피드 정보가 생길 때 마다 유저 테이블의 행이 증가한다
• 중복 값이 발생한다
• 특정 컬럼의 값이 변경이 일어나면 시간이 오래 걸린다
• 그러나, 단순한 읽기 작업이 많을 경우
• 검색 작업을 할 때 유저 테이블만 찾아서 확인하면 되기 때문에 속도가 빨라진다

• 수정 삭제가 많을 것 같다면 테이블을 분리 O

• 읽기가 많을 것 같다면 테이블을 분리 X

3. 테이블을 만들 때…

1. 사용자 시나리오를 파악

2. 시나리오에 어떤 오브젝트가 있을지 상상

3. 각 오브젝트가 어떤 행위를 하는지 파악

4. 오브젝트와 행위의 연관 관계를 설정

4. 유저 & 피드 & 댓글 관계도

• N의 관계를 가지고 있는 테이블에 FK를 만든다.

2. 조인

0. data setting

-- Join
-- 1. 테이블 생성
CREATE TABLE user_tb(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
email varchar(100)
);

CREATE TABLE feed_tb(
id int primary key auto_increment,
title varchar(1000),
photo_url varchar(100),
user_id int
);

CREATE TABLE reply_tb(
id int primary key auto_increment,
content varchar(100),
user_id int,
feed_id int
);

-- 2. 더미데이터 삽입
insert into user_tb(username, password, email) values('ssar', '1234', 'ssar@nate.com');
insert into user_tb(username, password, email) values('cos', '1234', 'cos@nate.com');
insert into user_tb(username, password, email) values('love', '1234', 'love@nate.com');

insert into feed_tb(title, photo_url, user_id) values('계곡왔어요', 'http://xn--989a5b.com', 1);
insert into feed_tb(title, photo_url, user_id) values('바다왔어요', 'http://xn--2j1b67o.com', 2);

insert into reply_tb(content, user_id, feed_id) values('굿', 2, 1);
insert into reply_tb(content, user_id, feed_id) values('별로', 3, 1);

1. inner join

select * from feed_tb;
select * from user_tb;

• 양 쪽 테이블에서 매칭 되는 부분만 합쳐진다.

• user_id = FK 에 매칭 되는 데이터를 가져와 붙여준다.

select *
from 
feed_tb ft 
inner join 
user_tb ut
on ft.user_id = ut.id;

1. inner join 실패, 메인 테이블 데이터가 안보임

select * from feed_tb;
select * from user_tb;
select * from reply_tb;

select *
from 
feed_tb ft 
inner join 
user_tb ut 
on ft.user_id = ut.id
inner join 
reply_tb rt 
on ft.id = rt.feed_id;

• ‘바다왔어요’가 안보임.

• feed_id = FK 에 매칭 되는 데이터만 보여준다.

• feed_id에 1만 존재하기 때문에 2를 가지고 있는 ‘바다왔어요’ 데이터는 보이지 않는다.

2. outer join

select *
from 
feed_tb ft 
inner join 
user_tb ut 
on ft.user_id = ut.id
left outer join 
reply_tb rt 
on ft.id = rt.feed_id;

• 아우터 조인을 하면 드라이빙을 하는 테이블을 기준으로 붙여서 보여준다.

1. 화면에 필요한 정보만 보여주기(인라인뷰)

• from 인라인뷰를 사용해 출력하는 방법

select post.feed_title, post.feed_picture, post.feed_writer, post.reply_content, rut.username reply_writer
from
(
	select ft.title feed_title, ft.photo_url feed_picture, ut.username feed_writer, rt.content reply_content, rt.user_id reply_writer_id
	from feed_tb ft 
	inner join 
	user_tb ut 
	on ft.user_id = ut.id
	
	left outer join 
	reply_tb rt 
	on ft.id = rt.feed_id
) post 
left outer join 
user_tb rut 
on post.reply_writer_id = rut.id;

2. 화면에 필요한 정보만 보여주기(join)

• left outer join을 사용해 출력하는 방법

select ft.title feed_title, ft.photo_url feed_picture, ut.username feed_writer, rt.content reply_content, rut.username reply_writer
from 
feed_tb ft 
inner join 
user_tb ut 
on ft.user_id = ut.id
left outer join 
reply_tb rt 
on ft.id = rt.feed_id
left outer join 
user_tb rut 
on rt.user_id = rut.id;

3. self join

• 자신의 테이블을 붙여서 필요한 정보를 표기함

• 직원의 상사를 표시하라 MGR이 상사의 번호

select *
from emp;

select e1.empno '사원번호', e1.ENAME '사원명', e2.ENAME '상사명'
from 
emp e1
left outer join 
emp e2
on e1.mgr = e2.empno;