inner join, outer join, count

테이블 생성하기

CREATE TABLE user_tb(
id int primary key auto_increment,
username varchar(20),
password varchar(20),
profile_url varchar(100),
email varchar(100),
createDate TIMESTAMP
);
CREATE TABLE image_tb(
id int primary key auto_increment,
photo_url varchar(100),
content varchar(1000),
user_id int,
createDate TIMESTAMP
);
CREATE TABLE love_tb(
id int primary key auto_increment,
image_id int,
user_id int,
createDate TIMESTAMP
);
CREATE TABLE reply_tb(
id int primary key auto_increment,
content varchar(100),
user_id int,
image_id int,
createDate TIMESTAMP
);

더미 데이터 삽입

user_tb

INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('ssar', '1234' , '/profile/1.jpg', 'ssar@nate.com', now());
INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('cos', '1234' , '/profile/2.jpg', 'cos@nate.com', now());
INSERT INTO user_tb(username, password, profile_url, email, createDate)
VALUES('love', '1234' , '/profile/3.jpg', 'love@nate.com', now());

image_tb

INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/1.jpg', '여행사진', 1, now());
INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/2.jpg', '강아지사진', 1, now());
INSERT INTO image_tb(photo_url, content, user_id, createDate)
values('/image/3.jpg', '친구사진', 2, now());

love_tb

INSERT INTO love_tb(user_id, image_id, createDate)
values(1, 1, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(2, 2, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(2, 3, now());
INSERT INTO love_tb(user_id, image_id, createDate)
values(3, 3, now());

reply_tb

INSERT INTO reply_tb(content, user_id, image_id, createDate)
VALUES('재밌겠다~~!', 1, 1, now());

inner join

select *
from image_tb img
inner join user_tb u on img.user_id = u.id;

select img.*, lo.*
from love_tb lo
inner join image_tb img on lo.image_id = img.id;

select img.*, rp.*
from reply_tb rp
inner join image_tb img on rp.image_id = img.id;

outer join

elect img.*, rp.*
from reply_tb rp
right outer join image_tb img on rp.image_id = img.id;

select img.*, rp.*
from image_tb img
left outer join reply_tb rp on rp.image_id = img.id;

select *
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id;

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image.user_id image_user_id,
reply.id reply_id,
reply.user_id reply_user_id,
reply.content reply_content
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id;

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image.user_id image_user_id,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply.id reply_id,
reply.user_id reply_user_id,
reply.content reply_content
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id;

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image.user_id image_user_id,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply.id reply_id,
reply.user_id reply_user_id,
reply_user.username reply_user_username,
reply.content reply_content
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id;

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image.user_id image_user_id,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply.id reply_id,
reply.user_id reply_user_id,
reply_user.username reply_user_username,
reply.content reply_content,
love.id love_id,
love.user_id love_user_id
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id
left outer join love_tb love on image.id = love.image_id;

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply_user.username reply_user_username,
reply.content reply_content
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id
left outer join love_tb love on image.id = love.image_id;

Group by로 카운트하기

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply_user.username reply_user_username,
reply.content reply_content,
count(love.id) love_count
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id
left outer join love_tb love on image.id = love.image_id
group by image.id, image.content, image.photo_url, image_user.username,
image_user.profile_url, reply_user.username, reply.content;

스칼라 서브쿼리로 카운트하기

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
image_user.username image_user_username,
image_user.profile_url image_user_profile_url,
reply_user.username reply_user_username,
reply.content reply_content,
(select count(*) from love_tb where image_id = image.id) love_count
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id
left outer join user_tb image_user on image.user_id = image_user.id
left outer join user_tb reply_user on reply.user_id = reply_user.id;

한방 쿼리 사용하지 않기

select
image.id image_id,
image.content image_content,
image.photo_url image_photo_url,
user.username image_user_username,
user.profile_url image_user_profile_url,
(select count(*) from love_tb where image_id = image.id) love_count
from image_tb image
inner join user_tb user on image.user_id = user.id;

select
reply.id reply_id,
reply.image_id reply_image_id,
reply.content reply_content,
user.username reply_user_username
from reply_tb reply
inner join user_tb user on reply.user_id = user.id
where image_id in (1,2,3);