[데이터베이스] 76. Join 결합 연산

1. join

2. 게시판 예시

데이터 생성 및 삽입

drop table if exists user_tb;
drop table if exists feed_tb;
drop table if exists reply_tb;

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://계곡.com', 1);
insert into feed_tb(title, photo_url, user_id) values('바다왔어요', 'http://바다.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);

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

join의 종류 및 활용

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

3. self join

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