[DB] 8. Join

0. Join이란?

• Join : 두 테이블을 각각 조회하는 것이 아닌(두 번의 select), 두 테이블을 합친 것처럼 한 번의 조회로 두 곳의 데이터를 한 번에 찾는 방법
• 두 테이블 간의 연관 관계를 잘 파악하는 것이 제일 중요
• 일대일 관계, 일대다 관계, 다대다 관계가 존재

연관 관계

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

• ex 1) 인스타그램 (Instagram) : 유저 - 피드
• 유저는 피드를 여러 개 작성 가능 (N)
• 피드는 한 사람에 의해서만 작성될 수 있음 (1)
• 즉, 유저와 피드의 관계는 1 : N (일대다) 관계
• 참조하는 쪽이 외래 키를 가지고 있어야 검색 속도가 빠름 (Random Access)
• 즉, 피드 측에서 유저 테이블을 참조 : 피드 테이블에 외래 키 필요
• 이때, 외래 키 = 유저 테이블의 기본 키

• 인스타그램 : 피드 - 댓글
• 피드에는 여러 개의 댓글이 달릴 수 있음 (N)
• 댓글은 작성되면 하나의 피드에만 작성됨 (1)
• 즉, 피드와 댓글의 관계는 1 : N (일대다) 관계
• 댓글 측에서 피드 테이블을 참조 : 댓글 테이블에 외래 키 필요
• 이때, 외래 키 = 피드 테이블의 기본 키

• 인스타그램 : 유저 - 댓글
• 유저는 여러 개의 댓글을 작성할 수 있음 (N)
• 하나의 댓글은 한 명의 유저에게만 작성됨 (1)
• 즉, 유저와 댓글의 관계는 1 : N (일대다) 관계
• 댓글 측에서 유저 테이블을 참조 : 댓글 테이블에 외래 키 필요
• 이때, 외래 키 = 유저 테이블의 기본 키

• ex 2) 쇼핑몰
• 유저는 상품을 여러 종류 구매 가능 (N)
• 상품은 여러 유저들에게 구매될 수 있음 (N)
• 이 경우, 유저와 상품은 N : M (다대다) 관계
• 두 테이블이 명사일 경우, 동사 테이블이 무조건 만들어짐
• 위의 경우, ‘유저’가 ‘상품’을 ‘구매’하므로 ‘구매’ 테이블 생성
• ‘구매’의 행위에는 트랜잭션의 파악이 중요

• 인스타그램으로 보는 연관 관계
• 유저가 피드를 작성하면, 다른 유저들이 피드에 댓글 작성 가능
• 유저는 여러 피드를 올릴 수 있고, 피드에는 여러 댓글이 달릴 수 있음
• 피드에는 댓글이 없을 수도 (null) 있음 : Outer Join
• 피드는 다 불러오되, 댓글은 없으면 null로 처리
• 피드에는 사진, 제목 외에 작성자의 정보가 필요
• 작성자 id (외래 키), 작성자 이름 (username)
• 댓글에는 댓글 내용, 댓글 작성자 정보, 피드 정보가 필요
• 피드 id (외래 키), 댓글 작성자 id (외래 키), 댓글 작성자 이름 (username)
• 한 번의 Select로 여러 테이블의 정보를 한 번에 찾으려면?
• 피드 작성자와 유저, 댓글 작성자와 유저의 정보가 모두 동일한 데이터만 불러옴
• = Inner Join

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);

• user_tb

• feed_tb

• reply_tb

1. Inner Join

• 참조하는 값이 같은 데이터만 출력

-- 3. Join
-- 1) Inner Join
select *
from feed_tb ft inner join user_tb ut
on ft.user_id = ut.id;

-- 2) Inner Join 2
select * from emp;
select * from dept;

select *
from emp e inner join dept d
on e.deptno = d.deptno;

-- 3) Inner Join (Failure)
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;

2. Outer Join

• 한 쪽의 데이터를 전부 출력하고, 참조하는 데이터가 없으면 null로 표시

• Left Outer Join : 데이터를 전부 출력할 테이블이 왼쪽에 있음

• Right Outer Join : 데이터를 전부 출력할 테이블이 오른쪽에 있음

-- 4) 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;

3. 데이터 완성

-- 5) 데이터 완성 (Inline View)
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;

-- 5) 데이터 완성
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

-- 정렬 추가
order by ft.id, rt.id;

Self Join

• 계층 구조를 표현할 때에 주로 사용

• 가져오는 테이블들이 동일하기 때문에 별칭이 필수 (column 구별)

-- 6) Self Join
select e1.empno '사원번호', e1.ename '사원명', e2.ename '상사'
from emp e1 left outer join emp e2
on e1.mgr = e2.empno;