MySQL Workbench : 3개 이상의 테이블을 조인하는 방법

< 3개 이상의 테이블을 조인하는 방법 >

# 학습전 3가지의 테이블을 만들고 데이터를 삽입

INSERT INTO series (title, released_year, genre) VALUES
   ('Archer', 2009, 'Animation'),
   ('Arrested Development', 2003, 'Comedy'),
   ("Bob's Burgers", 2011, 'Animation'),
   ('Bojack Horseman', 2014, 'Animation'),
   ("Breaking Bad", 2008, 'Drama'),
   ('Curb Your Enthusiasm', 2000, 'Comedy'),
   ("Fargo", 2014, 'Drama'),
   ('Freaks and Geeks', 1999, 'Comedy'),
   ('General Hospital', 1963, 'Drama'),
   ('Halt and Catch Fire', 2014, 'Drama'),
   ('Malcolm In The Middle', 2000, 'Comedy'),
   ('Pushing Daisies', 2007, 'Comedy'),
   ('Seinfeld', 1989, 'Comedy'),
   ('Stranger Things', 2016, 'Drama');

INSERT INTO reviewers (first_name, last_name) VALUES
   ('Thomas', 'Stoneman'),
   ('Wyatt', 'Skaggs'),
   ('Kimbra', 'Masters'),
   ('Domingo', 'Cortes'),
   ('Colt', 'Steele'),
   ('Pinkie', 'Petit'),
   ('Marlon', 'Crafford');

INSERT INTO reviews(series_id, reviewer_id, rating) VALUES
   (1,1,8.0),(1,2,7.5),(1,3,8.5),(1,4,7.7),(1,5,8.9),
   (2,1,8.1),(2,4,6.0),(2,3,8.0),(2,6,8.4),(2,5,9.9),
   (3,1,7.0),(3,6,7.5),(3,4,8.0),(3,3,7.1),(3,5,8.0),
   (4,1,7.5),(4,3,7.8),(4,4,8.3),(4,2,7.6),(4,5,8.5),
   (5,1,9.5),(5,3,9.0),(5,4,9.1),(5,2,9.3),(5,5,9.9),
   (6,2,6.5),(6,3,7.8),(6,4,8.8),(6,2,8.4),(6,5,9.1),
   (7,2,9.1),(7,5,9.7),
   (8,4,8.5),(8,2,7.8),(8,6,8.8),(8,5,9.3),
   (9,2,5.5),(9,3,6.8),(9,4,5.8),(9,6,4.3),(9,5,4.5),
   (10,5,9.9),
   (13,3,8.0),(13,4,7.2),
   (14,2,8.5),(14,3,8.9),(14,4,8.9);

 

# 실습 문제를 반복하여 여러개의 테이블을 join 하는 SQL 문장도 연습

-- 데이터 정상 진입된것까지 확인!
-- 실습 문제
-- 리뷰의 제목과 별점
select s.title, r.rating
from reviews r
join series s
	on r.series_id = s.id;

 

-- 각 시리즈별 별점 평균
select s.title, avg(r.rating) avg_rating
from reviews r
join series s
	on r.series_id = s.id
group by s.id
order by avg_rating;

-- 강사님 풀이
select s.title , avg(r.rating) as avg_rating
from series s
join reviews r
	on s.id = r.series_id
group by s.id
order by avg_rating;

 

-- 유저의 별점 점수
select rs.first_name, rs.last_name, r.rating
from reviewers rs
join reviews r
	on rs.id = r.reviewer_id;
    
-- 리뷰가 없는 시리즈 제목을 가져와라.
select title as unreviewed_series
from series s
left join reviews r
	on s.id = r.series_id
where r.id is null;

-- 각 장르별 별점 평균
select s.genre, avg(r.rating) as avg_rating
from series s
join reviews r
	on s.id = r.series_id
group by s.genre;

 

# ifnull() 함수 활용 예시

-- 리뷰를 한번도 남기지 않았으면, inactive
select rs.first_name, rs.last_name, 
	count(r.id) as COUNT,
    ifnull(min(r.rating), 0) as MIN,
    ifnull(max(r.rating), 0) as MAX,
    ifnull(avg(r.rating), 0) as AVG,
    if(r.id is null, 'INACTIVE', 'ACTIVE') as STATUS
from reviewers rs
left join reviews r
	on rs.id = r.reviewer_id
group by rs.id;

 

# 3가지 테이블을 연속으로 join (on 작성 주의)

-- 마지막 문제
select title , r.rating ,concat(first_name , ' ' ,last_name) as reviewer
from reviews r
join series s
	on r.series_id = s.id
join reviewers rs
	on  r.reviewer_id = rs.id
order by title;

-- 강사님 풀이
select s.title,
	rv.rating,
    concat(r.first_name, ' ' ,r.last_name) as reviewer
from series s
join reviews rv
	on s.id = rv.series_id
join reviewers r
	on rv.reviewer_id = r.id
order by s.title;

ㄴ 이렇게 세가지 테이블 데이터가 모두 출력되는것 확인해볼수 있다!

 

다음 게시글로 계속~!