[Database] 13. Join

1. Join이란?

2. Join 종류

SELECT *
FROM feed_tb ft
INNER JOIN user_tb ut
ON ft.user_id = ut.id;

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. 관계 설정 예시 (인스타그램 모델링)

4. 데이터 조회 예제

SELECT ft.title AS feed_title, ft.photo_url AS feed_picture, ut.username AS feed_writer,
       rt.content AS reply_content, rut.username AS 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;

5. Join을 사용하는 이유

더미 데이터 세팅

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

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. 이너조인
select *
from feed_tb ft inner join user_tb ut
on ft.user_id = ut.id;

select * from feed_tb;
select * from user_tb;

select * from emp;
select * from dept;

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

-- 2. 이너조인 실패 사례
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;

-- 3. 아우터 조인 (내가 원하는 테이블에 모든 데이터를 다 뽑으면서 조인하기 위해)
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;

-- 4. 화면 데이터 완성하기 (인라인뷰)
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;