inblog logo
|
Essential IT
    Spring

    inner join, outer join, count

    Mar 14, 2024
    inner join, outer join, count
    Contents
    테이블 생성하기더미 데이터 삽입inner joinouter joinGroup by로 카운트하기스칼라 서브쿼리로 카운트하기한방 쿼리 사용하지 않기

    테이블 생성하기

    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());
    notion image
     

    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());
    notion image

    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());
    notion image
     

    reply_tb

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

    inner join

    notion image
    조인되는 조건이 교집합 되는 행을 가지고 온다. 겹치는 컬럼이 있을 때 사용 가능하다.
    select *
from image_tb img
inner join user_tb u on img.user_id = u.id;
    notion image
    inner join 양쪽으로 조인하려는 테이블 명을 작성하고 on 뒤에 겹치는 테이블 컬럼명을 작성한다.
     
     
    select img.*, lo.*
from love_tb lo
inner join image_tb img on lo.image_id = img.id;
    notion image
     
     
    select img.*, rp.*
from reply_tb rp
inner join image_tb img on rp.image_id = img.id;
    notion image
     

    outer join

     
    right outer join
    left outer join
    full outer join
    왼쪽 오른쪽 둘 다의 데이터를 읽어 결과를 생성한다 right outer join + left outer join
    MySQL에서는 지원하지 않기 때문에 left와 right의 결과를 union해서 사용
     
     
    elect img.*, rp.*
from reply_tb rp
right outer join image_tb img on rp.image_id = img.id;
    notion image
     
     
    select img.*, rp.*
from image_tb img
left outer join reply_tb rp on rp.image_id = img.id;
    notion image
     
    join 여러번 하기
    select *
from image_tb image
left outer join reply_tb reply on image.id = reply.image_id;
    notion image
     
    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;
    notion image
     
     
    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;
    notion image
     
     
    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;
    notion image
     
     
    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;
    notion image
     
     
    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;
    notion image
     
     

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

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

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

    한방 쿼리 사용하지 않기

    이미지에 연관되어 있는 user정보를 조인하고 좋아요 카운트를 스칼라 서브 쿼리 한다.
    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;
    notion image
     
    댓글 테이블을 user와 조인하고, 위에서 검색된 image의 id를 where절에 걸어서 조회
    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);
    notion image
    두 개의 결과를 합친다.
    Share article
    Contents
    테이블 생성하기더미 데이터 삽입inner joinouter joinGroup by로 카운트하기스칼라 서브쿼리로 카운트하기한방 쿼리 사용하지 않기

    Essential IT

    RSS·Powered by Inblog